Which datatype should be used for currency?

by

Your source is in no way official. It dates to 2011 and I don’t even recognize the authors. If the money type was officially “discouraged” PostgreSQL would say so in the manual – which it doesn’t.

For a more official source, read this thread in pgsql-general (from just this week!), with statements from core developers including D’Arcy J.M. Cain (original author of the money type) and Tom Lane:

Related answer (and comments!) about improvements in recent releases:

Basically, money has its (very limited) uses. The Postgres Wiki suggests to largely avoid it, except for those narrowly defined cases. The advantage over numeric is performance.

decimal is just an alias for numeric in Postgres, and widely used for monetary data, being an “arbitrary precision” type. The manual:

The type numeric can store numbers with a very large number of digits.
It is especially recommended for storing monetary amounts and other
quantities where exactness is required.

Personally, I like to store currency as integer representing Cents if fractional Cents never occur (basically where money makes sense). That’s more efficient than any other of the mentioned options.

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. Create unique constraint with null columns
  3. Custom SERIAL / autoincrement per group of values
  4. Safely rename tables using serial primary key columns
  5. Serial numbers per group of rows for compound key
  6. PostgreSQL: Which Datatype should be used for Currency?
  7. Postgres constraint for unique datetime range
  8. Select (retrieve) all records from multiple schemas using Postgres
  9. Use email address as primary key?
  10. PostgreSQL Index on JSON
  11. Temporal database design, with a twist (live vs draft rows)
  12. Aggregate columns with additional (distinct) filters
  13. How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
  14. Insert data in 3 tables at a time using Postgres
  15. How to use ANY instead of IN in a WHERE clause?
  16. Is there a shortcut for SELECT * FROM?
  17. Database Structure for Tree Data Structure [closed]
  18. Concatenate multiple result rows of one column into one, group by another column [duplicate]
  19. sql ORDER BY multiple values in specific order?
  20. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  21. NOT NULL constraint over a set of columns
  22. How to create a multi-tenant database with shared table structures?
  23. Name database design notation you prefer and why?
  24. postgresql – sql – count of `true` values
  25. Optimize groupwise maximum query
  26. How to use multiple CTEs in a single SQL query?
  27. How to get a list column names and datatypes of a table in PostgreSQL?
  28. What does ON [PRIMARY] mean?
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment