I would like to select all sequences in the database, get the schema of sequence, dependent table, the schema of a table, dependent column.
I've tried the following query:
SELECT
ns.nspname AS sequence_schema_name,
s.relname AS sequence_name,
t_ns.nspname AS table_schema_name,
t.relname AS table_name,
a.attname AS column_name,
s.oid,
s.relnamespace,
d.*,
a.*
FROM pg_class s
JOIN pg_namespace ns
ON ns.oid = s.relnamespace
left JOIN pg_depend d --
ON d.objid = s.oid --TO FIX???
AND d.classid = 'pg_class'::regclass --TO FIX???
AND d.refclassid = 'pg_class'::regclass --TO FIX???
left JOIN pg_class t
ON t.oid = d.refobjid --TO FIX???
left JOIN pg_attribute a
ON a.attrelid = d.refobjid
AND a.attnum = d.refobjsubid
left JOIN pg_namespace t_ns
ON t.relnamespace = t_ns.oid
WHERE s.relkind = 'S'
;
Unfortunately, this query does not work at 100%. The query filter some sequences.
I need it for further processing (after data restore on different ENV, I need to find max column-value and set sequence to MAX+1).
Could anyone help me?
The following query should to work:
create table foo(id serial, v integer);
create table boo(id_boo serial, v integer);
create sequence omega;
create table bubu(id integer default nextval('omega'), v integer);
select sn.nspname as seq_schema,
s.relname as seqname,
st.nspname as tableschema,
t.relname as tablename,
at.attname as columname
from pg_class s
join pg_namespace sn on sn.oid = s.relnamespace
join pg_depend d on d.refobjid = s.oid
join pg_attrdef a on d.objid = a.oid
join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
join pg_class t on t.oid = a.adrelid
join pg_namespace st on st.oid = t.relnamespace
where s.relkind = 'S'
and d.classid = 'pg_attrdef'::regclass
and d.refclassid = 'pg_class'::regclass;
┌────────────┬────────────────┬─────────────┬───────────┬───────────┐
│ seq_schema │ seqname │ tableschema │ tablename │ columname │
╞════════════╪════════════════╪═════════════╪═══════════╪═══════════╡
│ public │ foo_id_seq │ public │ foo │ id │
│ public │ boo_id_boo_seq │ public │ boo │ id_boo │
│ public │ omega │ public │ bubu │ id │
└────────────┴────────────────┴─────────────┴───────────┴───────────┘
(3 rows)
For calling sequence related functions you can use s.oid column. For this case, it is sequence unique oid identifier. You need cast it to regclass.
A script for you request can looks like:
do $$
declare
r record;
max_val bigint;
begin
for r in
select s.oid as seqoid,
at.attname as colname,
a.adrelid as reloid
from pg_class s
join pg_namespace sn on sn.oid = s.relnamespace
join pg_depend d on d.refobjid = s.oid
join pg_attrdef a on d.objid = a.oid
join pg_attribute at on at.attrelid = a.adrelid and at.attnum = a.adnum
where s.relkind = 'S'
and d.classid = 'pg_attrdef'::regclass
and d.refclassid = 'pg_class'::regclass
loop
-- probably lock here can be safer, in safe (single user) maintainance mode
-- it is not necessary
execute format('lock table %s in exclusive mode', r.reloid::regclass);
-- expect usual one sequnce per table
execute format('select COALESCE(max(%I),0) from %s', r.colname, r.reloid::regclass)
into max_val;
-- set sequence
perform setval(r.seqoid, max_val + 1);
end loop;
end;
$$
Note: Using %s for table name or sequence name in format function is safe, because the cast from Oid type to regclass type generate safe string (schema is used when it is necessary every time, escaping is used when it is needed every time).