postgresqlpostgresql-9.4information-schema

Why does atttypmod differ from character_maximum_length?


I'm converting some information_schema queries to system catalog queries and I'm getting different results for character maximum length.

SELECT column_name, 
    data_type , 
    character_maximum_length AS "maxlen"
FROM information_schema.columns 
WHERE table_name = 'x'

returns the results I expect, e.g.:

city    character varying   255
company character varying   1000

The equivalent catalog query

SELECT attname,
       atttypid::regtype  AS datatype,
       NULLIF(atttypmod, -1) AS maxlen
FROM   pg_attribute
WHERE  CAST(attrelid::regclass AS varchar) = 'x'
AND    attnum > 0
AND    NOT attisdropped

Seems to return every length + 4:

city    character varying   259
company character varying   1004

Why the difference? Is it safe to always simply subtract 4 from the result?


Solution

  • You could say it's safe to substract 4 from the result for types char and varchar. What information_schema.columns view does under the hood is it calls a function information_schema._pg_char_max_length (this is your difference, since you don't), which body is:

    CREATE OR REPLACE FUNCTION information_schema._pg_char_max_length(typid oid, typmod integer)
     RETURNS integer
     LANGUAGE sql
     IMMUTABLE PARALLEL SAFE STRICT
    AS $function$SELECT
      CASE WHEN $2 = -1 /* default typmod */
           THEN null
           WHEN $1 IN (1042, 1043) /* char, varchar */
           THEN $2 - 4
           WHEN $1 IN (1560, 1562) /* bit, varbit */
           THEN $2
           ELSE null
      END$function$
    

    That said, for chars and varchars it always substracts 4. This makes your query not equivalent to the extent that it would actually need a join to pg_type in order to establish the typid of the column and wrap the value in a function to have it return proper values. This is due to the fact, that there are more things coming into play than just that. If you wish to simplify, you can do it without a join (it won't be bulletproof though):

    SELECT attname,
           atttypid::regtype  AS datatype,
           NULLIF(information_schema._pg_char_max_length(atttypid, atttypmod), -1) AS maxlen
    FROM   pg_attribute
    WHERE  CAST(attrelid::regclass AS varchar) = 'x'
    AND    attnum > 0
    AND    NOT attisdropped
    

    This should do it for you. Should you wish to investigate the matter further, refer to the view definition of information_schema.columns.