Re: Resetting identity columns

On 4/22/19 8:30 AM, Ray O'Donnell wrote:
> Hi all,
> 
> I'm probably doing something silly.... I'm migrating data from one 
> database table to another, where the old table used a SERIAL primary key 
> and the new one uses GENERATED BY DEFAULT AS IDENTITY. Having loaded the 
> data into the new table, I need to reset the underlying sequence so that 
> it picks up from the highest existing value.
> 
> I'm using PostgreSQL 11.2 on Debian 9.
> 
> I've tried:
> 
> =# alter table orders alter column order_id restart with (
> select max(order_id) + 1 from orders);
> 
> ERROR:  syntax error at or near "("
> LINE 1: ...r table orders alter column order_id restart with (select ma...
> 
> 
> I also tried it with a DO block:
> 
> =# do language plpgsql $$
> $# declare m_max_id bigint;
> $# begin
> $# select max(order_id) + 1 from orders into m_max_id;
> $# alter table orders alter column order_id restart with m_max_id;
> $# end;
> $# $$;
> 
> ERROR:  syntax error at or near "m_max_id"
> LINE 5: ...er table orders alter column order_id restart with m_max_id;
> 
> 
> What am I missing?

Attempt #2:

test_(postgres)# \d identity_test
                         Table "public.identity_test"
  Column |  Type   | Collation | Nullable |             Default 

--------+---------+-----------+----------+----------------------------------
  id     | integer |           | not null | generated by default as identity

select * from identity_test;
  id
----
   2
   3

do language plpgsql $$
declare m_max_id bigint;
begin
select max(id) + 1 from identity_test into m_max_id;
EXECUTE 'alter table identity_test alter column id restart with ' || 
m_max_id;
end;
$$;

select pg_get_serial_sequence('identity_test', 'id');
    pg_get_serial_sequence
-----------------------------
  public.identity_test_id_seq

select * from identity_test_id_seq
test-# ;
  last_value | log_cnt | is_called
------------+---------+-----------
           4 |       0 | f
(1 row)

> 
> I should add that this is part of a larger migration script; otherwise I 
> could just do it by hand the command line.
> 
> Thanks in advance,
> 
> Ray.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com