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 );

-- 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

-- 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;