CODEWITHSUNDEEP
sqlpostgresqltypesdefault
don't train ai on me
don't train ai on me

Reputation: 1112

What is the type of 100.0 in PostgreSQL?

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.

Upvotes: 1

Views: 466

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

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.

Upvotes: 1

Related Questions