Re: Resetting identity columns

[Ray O'Donnell]

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