CODEWITHSUNDEEP
databasepostgresqlsequenceplpgsql
JDoe
JDoe

Reputation: 25

bulk update postgresql sequences

I have existing data that I want to import in a new system. I want to set sequences accordingly to the length of existing tables. I try this, but I get number == 1.

DO
$do$
   DECLARE
   _tbl text;
   number int;
    BEGIN
        FOR _tbl  IN
           SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' and c.relname ilike '%y_id_seq'
        LOOP
        -- EXECUTE
           SELECT count(*) FROM regexp_replace(_tbl, '(\w)y_.*', '\1ies')  INTO number;
           RAISE NOTICE '%', number;
           EXECUTE format('SELECT setval(''"%s"'', ''%s'' )', _tbl, number);
        END LOOP;
    END
$do$;

What should I do to get the right count?

Upvotes: 1

Views: 98

Answers (1)

Adam
Adam

Reputation: 5589

COUNT(*) is not the best choice for a new sequence value. Just imagine that you have holes in your numbering, for example 1, 2, 15. Count is 3 but next value should be 16 to avoid duplicates in the future.

Assuming you use sequence for id column I would suggest:

SELECT max(id) FROM _table_name_ INTO number;

Or even simpler:

SELECT setval(_sequence_name_, max(id)) FROM _table_name_;

Upvotes: 2

Related Questions