How to Bulk Update Sequence Id Postgresql for All Tables

How to bulk update sequence ID postgreSQL for all tables

Assuming that all used sequences are owned by the respective columns, e.g. through a serial or identity attribute, you can use this, to reset all (owned) sequences in the current database.

with sequences as (
  select *
  from (
    select table_schema,
           table_name,
           column_name,
           pg_get_serial_sequence(format('%I.%I', table_schema, table_name), column_name) as col_sequence
    from information_schema.columns
    where table_schema not in ('pg_catalog', 'information_schema')
  ) t
  where col_sequence is not null
), maxvals as (
  select table_schema, table_name, column_name, col_sequence,
          (xpath('/row/max/text()',
             query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, ''))
          )[1]::text::bigint as max_val
  from sequences
) 
select table_schema, 
       table_name, 
       column_name, 
       col_sequence,
       coalesce(max_val, 0) as max_val,
       setval(col_sequence, coalesce(max_val, 1)) --<< this will change the sequence
from maxvals;

The first part selects all sequences owned by a column. The second part then uses query_to_xml() to get the max value for the column associated with that sequence. And the final SELECT then applies that max value to each sequence using setval().

You might want to run that without the setval() call first to see if everything is as you need.

Postgres: Update primary key sequence for all tables

here is plpgsql to reset all sequences (run in pgadmin or psql or any other client):

do 
$$
declare
 _r record;
 _i bigint;
 _m bigint;
begin
  for _r in (
    SELECT relname,nspname,d.refobjid::regclass, a.attname, refobjid
    FROM   pg_depend    d
    JOIN   pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
    JOIN pg_class r on r.oid = objid
    JOIN pg_namespace n on n.oid = relnamespace
    WHERE  d.refobjsubid > 0 and  relkind = 'S'
   ) loop
    execute format('select last_value from %I.%I',_r.nspname,_r.relname) into _i;
    execute format('select max(%I) from %s',_r.attname,_r.refobjid) into _m;
    if coalesce(_m,0) > _i then
      raise info '%',concat('changed: ',_r.nspname,'.',_r.relname,' from:',_i,' to:',_m); 
      execute format('alter sequence %I.%I restart with %s',_r.nspname,_r.relname,_m+1);
    end if;
  end loop;

end;
$$
;

or use any other solution proposed at How to reset postgres' primary key sequence when it falls out of sync?

Most efficient way to do a bulk UPDATE with pairs of input

Normally you want to batch-update from a table with sufficient index to make the merge easy:

CREATE TEMP TABLE updates_table
        ( id integer not null primary key
        , val varchar
        );
INSERT into updates_table(id, val) VALUES
 ( 1, 'foo' ) ,( 2, 'bar' ) ,( 3, 'baz' )
        ;

UPDATE target_table t
SET value = u.val
FROM updates_table u
WHERE t.id = u.id
        ;

So you should probably populate your update_table by something like:

---

INSERT into updates_table(id, val)
SELECT
        split_part(x,',',1)::INT AS id,
        split_part(x,',',2)::VARCHAR AS value
    FROM (
        SELECT UNNEST(ARRAY['1,foo','2,bar','3,baz']) 
         ) AS x
     ;

---

Remember: an index (or the primary key) on the id field in the updates_table is important. (but for small sets like this one, a hashjoin will probably by chosen by the optimiser)

---

In addition: for updates, it is important to avoid updates with the same value, these cause extra rowversions to be created + plus the resulting VACUUM activity after the update was committed:

UPDATE target_table t
    SET value = u.val
    FROM updates_table u
    WHERE t.id = u.id
    AND (t.value IS NULL OR t.value <> u.value)
            ;

Postgresql - SQL query to list all sequences in database

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).

Bulk/batch update/upsert in PostgreSQL

I've used 3 strategies for batch transactional work:

1. Generate SQL statements on the fly, concatenate them with semicolons, and then submit the statements in one shot. I've done up to 100 inserts in this way, and it was quite efficient (done against Postgres).
2. JDBC has batching capabilities built in, if configured. If you generate transactions, you can flush your JDBC statements so that they transact in one shot. This tactic requires fewer database calls, as the statements are all executed in one batch.
3. Hibernate also supports JDBC batching along the lines of the previous example, but in this case you execute a flush() method against the Hibernate Session, not the underlying JDBC connection. It accomplishes the same thing as JDBC batching.

Incidentally, Hibernate also supports a batching strategy in collection fetching. If you annotate a collection with @BatchSize, when fetching associations, Hibernate will use IN instead of =, leading to fewer SELECT statements to load up the collections.

---

Related Topics