Re: Resetting identity columns

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(at)rodonnell(dot)ie