Why does atttypmod differ from character_maximum_length?

user_163024 0
Viewed 1269

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?

postgresql-9.4 information-schema postgresql
1 Answers

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 informatoin_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 view definition of information_schema.columns.

user_20955340
Related