PostgreSQL SELECT primary key as "serial" or "bigserial"

Question

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page.

If there is something wrong with my query I need to actually know what in a given scenario would throw it off on the explicit context of having a query or queries to run on a purely test database/table to modify that database/table so run this query on in order to test for any false-positives.

SELECT pg_attribute.attname, 
format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
CASE 
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial'
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial'
END AS type
FROM pg_index, pg_class, pg_attribute 
WHERE pg_class.oid = 'delete2'::regclass 
AND indrelid = pg_class.oid 
AND pg_attribute.attrelid = pg_class.oid 
AND pg_attribute.attnum = any(pg_index.indkey) 
AND indisprimary;

Here is a table with a primary key that does not return the primary key with this query:

CREATE TABLE delete_key_bigserial (
 test1 integer,
 id bigserial NOT NULL,
 col1 text,
 col2 text,
 test2 integer
);

Answer 1

Your query would fail, because the standard name of an integer is "integer", not "int". You can avoid this kind of error by comparing the internal regtype OID instead of a text representation. Many basic data types have several alias names, they all resolve to the same internal registered type.
That aside, you can largely simplify and improve:

SELECT a.attname
 , CASE a.atttypid
 WHEN 'bigint'::regtype THEN 'bigserial'
 WHEN 'int'::regtype THEN 'serial'
 ELSE format_type(a.atttypid, a.atttypmod)
 END AS type
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid
WHERE i.indrelid = 'tbl'::regclass 
AND i.indisprimary
AND a.attnum = ANY(i.indkey);

While this improves the query, it's still not doing what you hope it would.

Just because an integer column is (part of) the primary key, doesn't make it a serial column, yet. Here is a detailed assessment of what a serial is:

• Safely and cleanly rename tables that use serial primary key columns in Postgres?

You don't find anything for the presented table, because you are basing your query on pg_index, which is completely unrelated to serial types. A serial does not have to be indexed, only primary keys happen to be indexed.

Safe solutions

Just detect the serial type of the PRIMARY KEY:

SELECT a.attrelid::regclass::text, a.attname
 , CASE a.atttypid
 WHEN 'int'::regtype THEN 'serial'
 WHEN 'int8'::regtype THEN 'bigserial'
 WHEN 'int2'::regtype THEN 'smallserial'
 END AS serial_type
FROM pg_attribute a
JOIN pg_constraint c ON c.conrelid = a.attrelid
 AND c.conkey[1] = a.attnum 
JOIN pg_attrdef ad ON ad.adrelid = a.attrelid
 AND ad.adnum = a.attnum
WHERE a.attrelid = 'tbl'::regclass -- table name, optionally schema-qualified
AND a.attnum > 0
AND NOT a.attisdropped
AND a.atttypid = ANY('{int,int8,int2}'::regtype[]) -- integer type
AND c.contype = 'p' -- PK
AND array_length(c.conkey, 1) = 1 -- single column
AND pg_get_expr(ad.adbin, ad.adrelid)
 = 'nextval('''
 || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
 || '''::regclass)'; -- col default = nextval from owned seq

Returns nothing if the PK isn't a serial type.

A simpler check for just:

pg_get_serial_sequence(attr.attrelid::regclass::text, attr.attname) IS NOT NULL

... would only check if there is a sequence "owned" by the column, but not whether the column default actually draws numbers from the sequence. The manual:

The function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it's typically used with serial or bigserial columns.

---

To show ALL columns with proper data type - replaced with the appropriate serial type where applicable:

SELECT a.attrelid::regclass::text, a.attname
 , CASE WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[])
 AND EXISTS (
 SELECT FROM pg_attrdef ad
 WHERE ad.adrelid = a.attrelid
 AND ad.adnum = a.attnum
 AND pg_get_expr(ad.adbin, ad.adrelid)
 = 'nextval('''
 || (pg_get_serial_sequence (a.attrelid::regclass::text
 , a.attname))::regclass
 || '''::regclass)'
 )
 THEN CASE a.atttypid
 WHEN 'int'::regtype THEN 'serial'
 WHEN 'int8'::regtype THEN 'bigserial'
 WHEN 'int2'::regtype THEN 'smallserial'
 END
 ELSE format_type(a.atttypid, a.atttypmod)
 END AS data_type
FROM pg_attribute a
WHERE a.attrelid = 'tbl'::regclass -- table name, optionally schema-qualified
AND a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum;

The check on the column default might break with exotic settings for search_path. Didn't test all combinations.

fiddle

• Paul White: The secondary discussion on this answer has been moved to chat.