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@xxxxxxxxxxx
