How to Insert money datatype value in postgres

Question

Hi I am new to postgres, while inserting the data into a money type field I am getting the error as.. Here my sample table is as

CREATE TABLE x_table
(
  t_name character varying(40) NOT NULL,
  currency money
)

INSERT INTO x_table(t_name, currency)
VALUES ('TEST', 1000);

and getting the error message as ERROR: column "currency" is of type money but expression is of type integer SQL state: 42804.

So can any one say how to resolve this.. Thanks in advance

As always: your version of Postgres? In modern versions, this just works. — Erwin Brandstetter, Jun 02 '14 at 19:02
Store currency in cents using big integer. Not an answer but may work equally well. — Audrius Meškauskas, Jun 02 '14 at 19:09

score 5 · Accepted Answer · edited May 23 '17 at 12:16

5

In modern versions (tested with 9.1 and 9.3) the above code example works as is.

In older versions there was no implicit cast defined from integer to money.
Quoting the the release notes for Postgres 9.1:

Add support for casting from int4 and int8 to money (Joey Adams)

Also, be sure to consider alternatives to store currency information:
PostgreSQL: Which Datatype should be used for Currency?

edited May 23 '17 at 12:16

Community

answered Jun 02 '14 at 19:04

Thanks Erwin,Worked with 9.1 and may I know to use that money to int casting while inserting from the command prompt... if we are using in the older versions – RamBen Jun 03 '14 at 04:31

1 Answers1