Re: Resetting identity columns

On 4/22/19 8:45 AM, Adrian Klaver wrote:
> 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);
> 
> https://www.postgresql.org/docs/11/sql-altertable.html
> 
> "ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | 
> SET sequence_option | RESTART [ [ WITH ] restart ] } [...]"
> 
> See if the above form will work in your Do block below.

Aargh, time to clean the glasses. You where using the above. Sorry for 
the noise.

> 
>>
>> 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?
>>
>> 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