Re: Resetting identity columns

Поиск
Список
Период
Сортировка
От Thomas Kellerer
Тема Re: Resetting identity columns
Дата
Msg-id f0ea89b2-815a-0d8d-da44-6e0d84ea255e@gmx.net
обсуждение исходный текст
Ответ на Resetting identity columns  (Ray O'Donnell <ray@rodonnell.ie>)
Список pgsql-general
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




В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: Resetting identity columns
Следующее
От: Jeremy Finzel
Дата:
Сообщение: Why does log_error_verbosity not apply to server logs?