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@rodonnell.ie