PostgreSQL – How to Extract All Possible Couples of a Primary Key in a Table

Rows in postgresql have no fixed order. Not only are records placed where ever there is free space records can also move around. This is because when a row is updated a new version of the row is created in a new location while the old version continues to live in it's old location until it's removed by vacuum.

A CLUSTER operations sorts all rows but does indeed not affect how postgresql add's rows. So the data won't stay sorted. However postgresql keeps statistics among which is the correlation for each column between the order of the rows in the tables and the sorted order of that column. So the planner can still optimize it's plan based on the statistics that tell it the table is still mostly sorted even if some rows have been added after a cluster operation (or have been moved by updates).

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