Обсуждение: Resetting identity columns
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
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
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
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
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
"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
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
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
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