Re: Resetting identity columns

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