I have a table with money type column in my PostgreSQL database. I want to connect the data to Google Data Studio, but it doesn't support money type data.
is there a way to convert money type to double or bigint in a query like this or any other equivalent query?
SELECT todouble(maintenance.cost) FROM maintenance
Thanks!
The most natural choice is numeric:
select 123.45::money::numeric;
numeric
---------
123.45
(1 row)
You can also use integer or bigint but you have to take care of handling the fractional part then. Do not try real or double precision, per the documentation::
Floating point numbers should not be used to handle money due to the potential for rounding errors.
you can use ::numeric::float8 this to convert it. so it become
SELECT maintenance.cost::numeric::float8 FROM maintenance
you can see it in the documentation below: https://www.postgresql.org/docs/current/datatype-money.html