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

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.

>
> 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(dot)klaver(at)aklaver(dot)com