Search code examples
sqlpostgresqlcasting

Convert column type from FLOAT to MONEY in PostgreSQL

I need to change a column type from FLOAT to MONEY, but I get the error:

Link to the example:

ERROR: operator does not exist: money >= double precision HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Current table:

CREATE TABLE user_settings
(
    ...
    price FLOAT DEFAULT 0 CHECK (price >= 0)
);

Here is the migration part:

ALTER TABLE user_settings
ALTER COLUMN price TYPE money USING price::text::money,
ALTER COLUMN price SET DEFAULT 0.0::money;
ALTER TABLE user_settings ADD CHECK (price >= 0.0::money);
Solution

  • First to numeric then to money:

     alter table user_settings
 alter column price type numeric using price::numeric::money;

    DEMO

    Values of the numeric, int, and bigint data types can be cast to money. Conversion from the real and double precision data types can be done by casting to numeric first, for example:

    SELECT '12.34'::float8::numeric::money;