[Bug] Wrong detection on column that is primary key and also have another constraints

[mfvanek]

create table if not exists another_table
(
    id bigserial primary key, -- should NOT be found
    constraint not_reserved_id check (id > 1000),
    constraint less_than_million check (id < 1000000)
);

create table if not exists one_more_table
(
    id bigserial, -- should be found. Why not PK?
    constraint unique_id unique (id),
    constraint not_reserved_id check (id > 1000),
    constraint less_than_million check (id < 1000000)
);

create table if not exists test_table
(
    id bigserial, -- should be found
    num bigserial, -- should be found
    constraint test_table_pkey_id primary key (id),
    constraint test_table_fkey_other_id foreign key (id) references another_table (id),
    constraint test_table_fkey_one_more_id foreign key (id) references one_more_table (id)
);

[mfvanek]

A partial fix

left join pg_constraint c on c.conrelid = col.attrelid and c.conkey[1] = col.attnum and c.contype in ('p', 'f')

[mfvanek]

Final fix

select
    col.attrelid::regclass::text as table_name,
    col.attname::text as column_name,
    col.attnotnull as column_not_null,
    case col.atttypid
        when 'int'::regtype then 'serial'
        when 'int8'::regtype then 'bigserial'
        when 'int2'::regtype then 'smallserial' end as column_type,
    pg_get_serial_sequence(col.attrelid::regclass::text, col.attname) as sequence_name
from
    pg_catalog.pg_class t
        join pg_catalog.pg_namespace nsp on nsp.oid = t.relnamespace
        join pg_catalog.pg_attribute col on col.attrelid = t.oid
        join pg_attrdef ad on ad.adrelid = col.attrelid and ad.adnum = col.attnum
       left join lateral (select
                              sum(case when c.contype = 'p' then +1 else -1 end) as res
                          from pg_constraint c
                          where
                              c.conrelid = col.attrelid and
                              c.conkey[1] = col.attnum and
                              c.contype in ('p', 'f') and /* primary or foreign key */
                              array_length(c.conkey, 1) = 1 /* single column */
                          group by c.conrelid, c.conkey[1]) c on true
where
        t.relkind = 'r' and
        col.attnum > 0 and /* to filter out system columns such as oid, ctid, xmin, xmax, etc. */
    not col.attisdropped and
        col.atttypid = any('{int,int8,int2}'::regtype[]) and
    (c.res is null or c.res <= 0) and
    /* column default value = nextval from owned sequence */
        pg_get_expr(ad.adbin, ad.adrelid) = 'nextval(''' || (pg_get_serial_sequence(col.attrelid::regclass::text, col.attname))::regclass || '''::regclass)' and
        nsp.nspname = 'public'::text
order by t.oid::regclass::text, col.attname::text;