create missing sequence for database tables containing column called id

Question

i'm trying to fix database issue related to creating missing sequence of the table after moving database from lower to higher version but i face 2 issues

first here is what i tried so far:

DO $$
DECLARE
i TEXT;
BEGIN
    FOR i IN (select table_name from information_schema.tables where table_catalog='cst_sh' and table_schema='public') LOOP
    IF EXISTS (SELECT count(*) FROM information_schema.columns WHERE table_name=table_name and column_name='id') THEN
        EXECUTE 'CREATE SEQUENCE IF NOT EXISTS '''||i||'_id_seq''';
        EXECUTE 'Select setval('''||i||'_id_seq'', (SELECT max(id) as a FROM ' || i ||')+1,true);';
        end if;
    END LOOP;
END$$;

1st problem is that the condition doesn't seem to work. First i check if the table has column called id then i start to create the sequence if exist and then set the value for it but some tables doesn't have id column so the second query fail.

2nd problem is with the 1st query that i use to create the sequence if it doesn't exist, it fails every time and i dont know why

the error is :

QUERY:  CREATE SEQUENCE IF NOT EXISTS 'xxxx'
CONTEXT:  PL/pgSQL function inline_code_block line 7 at EXECUTE

i backed up the db from v9.6 and restored on v12.4 and some of the sequence are not created for some reason — Mostafa Mohamed
– Mostafa Mohamed, Commented Sep 1, 2020 at 12:16
Find that reason and fix the problem. Sequences should definitely be restored. What were the error messages? — Laurenz Albe
– Laurenz Albe, Commented Sep 1, 2020 at 12:34

user330315 · Accepted Answer · 2020-09-01 12:20:45Z

2

You should only loop over tables that actually have such a column. This can be achieved by using an EXISTS condition.

select t.table_name, t.table_schema
from information_schema.tables t
where t.table_schema = 'public'
and exists (select *
            from information_schema.columns c
            where t.table_name = c.table_name 
              and t.table_schema = c.table_schema
              and c.column_name = 'id')

To create a safe SELECT statement you need to include the schema name of the table when getting the max() value.

Dynamic SQL is a lot easier to write if you use format() instead of string concatenation. You create a statement create sequence 'foo_id_seq' putting the sequence name in single quotes - but that's invalid for identifiers.

When you loop over a SELECT statement the loop variable should be a record, not a text value.

So putting that all together your code should look something like this:


DO $$
DECLARE
  l_rec record;
  l_seq_name text;
BEGIN
  FOR l_rec IN  select t.table_name, t.table_schema
                from information_schema.tables t
                where t.table_schema = 'stuff'
                  and exists (select *
                              from information_schema.columns c
                              where t.table_name = c.table_name 
                                and t.table_schema = c.table_schema
                                and c.column_name = 'id')
  LOOP
    l_seq_name := l_rec.table_name||'_id_seq';
    EXECUTE format('CREATE SEQUENCE IF NOT EXISTS %I', l_seq_name);
    EXECUTE format('select setval(%L, max(id)) FROM %I.%I', l_seq_name, l_rec.table_schema, l_rec.table_name);
  END LOOP;
END
$$;

You probably also want to make the sequence owned by the column, so you should add:

EXECUTE format('alter sequence %I owned by %I.%I.id', l_seq_name, l_rec.table_schema, l_rec.table_name);

answered Sep 1, 2020 at 12:20

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Mostafa Mohamed Over a year ago

I liked your solution but i need to know why can't i fit a condition inside the loop. I mean select all then check if it include the ID column ? what if i need to change the owenership of the sequence to the table_catalog owner 'db owner'? Also using format i much better ofc thnx

user330315 Over a year ago

Of course you can have a condition inside the loop, however if exists (select count(*) ... is an expensive way of writing if true because the count(*) sub-select **always returns one row and thus the if exists is always true.

Collectives™ on Stack Overflow

create missing sequence for database tables containing column called id

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related