1

I am trying to understand whether I would prefer to cast to NUMERIC or to DECIMAL in PostgreSQL. I have read that they are basically functionally the same. One factor which might affect my decision is the default type when a decimal number is manually written, e.g.

SELECT percentage, 100.0
FROM grades

So the question is:

What type is 100.0 in the query above?

I was unable to find the answer in previous questions. I only know how to find the type of a column in a database, not an individual number. Thank you for the help.

doublefelix
  • 952
  • 1
  • 9
  • 22

1 Answers1

1

The type of the literal value is numeric (or decimal which is the same thing). This is explained in the documentation. However, the documentation is a little convoluted. The decimal point makes this numeric. Without a decimal point, it could be either int or bigint.

Postgres supports numeric without scale and precision, so these are actually NULL.

To answer this sort of question, I often do a simple test. The following idea works in any database:

create table t as
    select 100.0 as x;

select * 
from information_schema.columns
where column_name = 'x';

Or for a simpler Postgres-specific approach:

select pg_typeof(100.0)

As in this db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for the help. After your answer I also tried your suggestion of `select pg_typeof(100.0)` and the output was `numeric`. I will accept your answer as soon as the 5-minute timer is complete. – doublefelix Jun 26 '21 at 12:19