Where are NUMERIC precision and scale for a field found in the pg_catalog tables?

Question

In PostgreSQL, column data for the structure of a table is stored in pg_attribute, with a few fields in pg_class, and a couple in pg_attrdef .

But I do not see the precision or scale for a NUMERIC field type stored in there anywhere.

It can be found in the INFORMATION_SCHEMA tables, but I am trying to avoid them, as they do not use oids for easy joining to the pg_catalog tables.

So the question is: Where is column precision and scale stored in the postgreSQL system tables?

Answer 1

It is stored in pg_attribute, in the column atttypmod. All information is available in the view information_schema.columns. This view uses some queries to calculate the values, these are the bare basics:

SELECT
  CASE atttypid
         WHEN 21 /*int2*/ THEN 16
         WHEN 23 /*int4*/ THEN 32
         WHEN 20 /*int8*/ THEN 64
         WHEN 1700 /*numeric*/ THEN
              CASE WHEN atttypmod = -1
                   THEN null
                   ELSE ((atttypmod - 4) >> 16) & 65535     -- calculate the precision
                   END
         WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
         WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
         ELSE null
  END   AS numeric_precision,
  CASE 
    WHEN atttypid IN (21, 23, 20) THEN 0
    WHEN atttypid IN (1700) THEN            
        CASE 
            WHEN atttypmod = -1 THEN null       
            ELSE (atttypmod - 4) & 65535            -- calculate the scale  
        END
       ELSE null
  END AS numeric_scale,
  *
FROM 
    pg_attribute ;