Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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


attrelid
attname
serial_type


tbl
tbl_id
bigserial


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


attrelid
attname
data_type


tbl
test1
integer

tbl
tbl_id
bigserial

tbl
col1
timestamp(2) without time zone

tbl
col2
numeric(9,3)

tbl
test2
integer

tbl
some_serial
serial

tbl
some_other
serial
attrelid	attname	data_type
tbl	test1	integer
tbl	tbl_id	bigserial
tbl	col1	timestamp(2) without time zone
tbl	col2	numeric(9,3)
tbl	test2	integer
tbl	some_serial	serial
tbl	some_other	serial