PostgreSQL SELECT primary key as "serial" or "bigserial"
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, that 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 &gt; 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.

Addressing comment by @jpmc26

A simplified 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 is also set to draw numbers from the sequence. The documentation:


  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 &gt; 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.

db&lt;>fiddle here