Postgres Pro
Downloads
Home   >   mailing lists

Thread: Resetting identity columns

Resetting identity columns

From
Ray O'Donnell
Date:
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?

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.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: Resetting identity columns

From
Adrian Klaver
Date:
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.klaver@aklaver.com

Re: Resetting identity columns

From
Adrian Klaver
Date:
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

Re: Resetting identity columns

From
Adrian Klaver
Date:
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

Re: Resetting identity columns

From
Ray O'Donnell
Date:
On 22/04/2019 17:02, Adrian Klaver wrote:

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

Thanks a million Adrian - EXECUTE did the job, and I finished up 
wrapping it in a function as I used it in a number of places in the 
larger migration script:

create function reset_identity(
     p_table text,
     p_column text
)
returns text
as
$$
declare
     m_max_id bigint;
begin
     execute 'select max(' || quote_ident(p_column) || ') + 1 from '
        || quote_ident(p_table) into m_max_id;
     execute 'alter table ' || quote_ident(p_table)
        || ' alter column ' || quote_ident(p_column)
        || ' restart with ' || m_max_id;

     return 'New identity value for ' || p_table || '.' || p_column
        || ': ' || m_max_id;
end;
$$
language plpgsql;


In general, then, is it not possible to use an expression thus? -

     [...] ALTER COLUMN [...] RESTART WITH <expression here>

Thanks,

Ray.


-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

Re: Resetting identity columns

From
Tom Lane
Date:
"Ray O'Donnell" <ray@rodonnell.ie> writes:
> In general, then, is it not possible to use an expression thus? -

>      [...] ALTER COLUMN [...] RESTART WITH <expression here>

No.  In general, PG's utility commands (everything except SELECT/
INSERT/UPDATE/DELETE) don't do expression evaluation.  Partly this
is laziness or lack of round tuits, but in some cases there are
also interesting semantic problems.

            regards, tom lane

Re: Resetting identity columns

From
Adrian Klaver
Date:
On 4/22/19 10:08 AM, Ray O'Donnell wrote:
> On 22/04/2019 17:02, Adrian Klaver wrote:
> 
>> 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;
>> $$;
> 
> Thanks a million Adrian - EXECUTE did the job, and I finished up 
> wrapping it in a function as I used it in a number of places in the 
> larger migration script:
> 
> create function reset_identity(
>      p_table text,
>      p_column text
> )
> returns text
> as
> $$
> declare
>      m_max_id bigint;
> begin
>      execute 'select max(' || quote_ident(p_column) || ') + 1 from '
>         || quote_ident(p_table) into m_max_id;
>      execute 'alter table ' || quote_ident(p_table)
>         || ' alter column ' || quote_ident(p_column)
>         || ' restart with ' || m_max_id;
> 
>      return 'New identity value for ' || p_table || '.' || p_column
>         || ': ' || m_max_id;
> end;
> $$
> language plpgsql;
> 
> 
> In general, then, is it not possible to use an expression thus? -
> 
>      [...] ALTER COLUMN [...] RESTART WITH <expression here>

No. It took some digging when I first ran into this to find out why. To 
follow up on Tom's post and show where it is called out:


https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

"Another restriction on parameter symbols is that they only work in 
SELECT, INSERT, UPDATE, and DELETE commands. In other statement types 
(generically called utility statements), you must insert values 
textually even if they are just data values."

> 
> Thanks,
> 
> Ray.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Resetting identity columns

From
Thomas Kellerer
Date:
Ray O'Donnell schrieb am 22.04.2019 um 17:30:
> 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...
>
>
> 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.

As you noticed, an identity column is backed by a sequence, just like a serial column, so you can use setval() to sync
thesequence. 

To get the name of the sequence you can also use pg_get_serial_sequence() (despite its name):


    select setval(pg_get_serial_sequence('orders', 'order_id'), (select max(order_id) from x));

Thomas

Re: Resetting identity columns

From
Ray O'Donnell
Date:
On 22/04/2019 18:49, Tom Lane wrote:
> "Ray O'Donnell" <ray@rodonnell.ie> writes:
>> In general, then, is it not possible to use an expression thus? -
> 
>>       [...] ALTER COLUMN [...] RESTART WITH <expression here>
> 
> No.  In general, PG's utility commands (everything except SELECT/
> INSERT/UPDATE/DELETE) don't do expression evaluation.  Partly this
> is laziness or lack of round tuits, but in some cases there are
> also interesting semantic problems.

Fair enough. Thanks Tom, Adrian & Thomas for the help!

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie