[PostgreSQL]tables sans oid en big serial

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;

select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
       and typ.typname = 'int4'
order by sch.nspname,
       tab.relname,
       col.attnum;


with seqs as
       (select oid,
             relname as sequencename
             from pg_class
             where relkind = 'S' )
select sch.nspname as schemaname,
       tab.relname as tablename,
       col.attname as columnname,
       col.attnum as columnnumber,
       col.atttypid,
       typ.typname as columntype,
       seq.sequencename
from pg_attribute col
join pg_type typ on col.atttypid = typ.oid
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid
and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid
and deps.deptype = 'N'
left join seqs seq on deps.refobjid = seq.oid
where sch.nspname != 'information_schema'
       and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
       and col.attnum > 0
       and typ.typcategory = 'N'
       and col.attnotnull = true
order by sch.nspname,
       tab.relname,
       col.attnum;