Search code examples
postgresqlsqldatatypes

Is it OK to use the DEC data type in PostgreSQL

The DEC type seems to work just fine in PostgreSQL 15:

SELECT 12 :: DEC(5,3);

producing:

 numeric 
---------
  12.000
(1 row)

Clearly DEC is an alias for the NUMERIC type, just like DECIMAL. However, when I look at the data types table in PostgreSQL manual, it doesn't include the DEC type. Only NUMERIC and DECIMAL.

It states above the table:

Table 8.1 shows all the built-in general-purpose data types. Most of the alternative names listed in the “Aliases” column are the names used internally by PostgreSQL for historical reasons. In addition, some internally used or deprecated types are available, but are not listed here.

This makes me wonder whether DEC type might be deprecated. I tried to search for deprecated types in PostgreSQL, but didn't manage to find such information.

Is it simply an omission from the documentation? If so, are there perhaps more data type aliases which are supported but not documented?

Or is there something more going on with the DEC type?

Solution

  • dec is short for decimal. Both are aliases for numeric.

    test=> SELECT 'dec'::regtype, 'decimal'::regtype, 'numeric'::regtype;
 regtype | regtype | regtype 
---------+---------+---------
 numeric | numeric | numeric
(1 row)

    The manual:

    The types decimal and numeric are equivalent. Both types are part of the SQL standard.

    I am not sticking out my neck much to claim neither is going to get deprecated. Still, just use the canonical type name numeric. No good reason not to.