By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3799168 fiddles created (41763 in the last week).
CREATE TABLE tbl (
test1 integer
, tbl_id bigserial PRIMARY KEY -- PK with index
, col1 timestamp(2)
, col2 numeric(9,3)
, test2 integer
, some_serial serial -- no index!
, some_other serial UNIQUE NOT NULL -- index, but not PK
);
✓
hidden batch(es)
-- Just detect a 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
attrelid
attname
serial_type
tbl
tbl_id
bigserial
…
hidden batch(es)
-- Show ALL columns, serial 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;