Re: checking existence of a table before updating its SERIAL

Matthias Apitz schrieb am 08.06.2020 um 09:53:
> We're updating the SERIAL of a bunch of tables with a SQL script which
> does for any table:
>
> /* table: idm_tasktab */
> DO $$
> DECLARE
> max_id int;
> BEGIN
> SELECT INTO max_id GREATEST(COALESCE(max(taskid), 0),0) + 1 FROM idm_tasktab;
> RAISE NOTICE '% % %', 'idm_tasktab', 'taskid', max_id ;
> EXECUTE 'ALTER SEQUENCE idm_tasktab_taskid_seq RESTART ' || max_id::text;
> END $$ LANGUAGE plpgsql;
>
> Can some kind soul help me with doing a test for the existence of the
> table to avoid the error message about non existing relation?

I think the easiest way is to use to_regclass():

DO $$
DECLARE
max_id int;
BEGIN
if to_regclass('idm_tasktab') is not null then
SELECT INTO max_id GREATEST(COALESCE(max(taskid), 0),0) + 1 FROM idm_tasktab;
RAISE NOTICE '% % %', 'idm_tasktab', 'taskid', max_id ;
EXECUTE 'ALTER SEQUENCE idm_tasktab_taskid_seq RESTART ' || max_id::text;
end if;
END $$ LANGUAGE plpgsql;

Note that you don't really need dynamic SQL for this, you can simplify this to:

select setval('idm_tasktab_taskid_seq', GREATEST(COALESCE(max(taskid), 0),0))
from idm_tasktab;

I also don't think greatest() is necessary.

Thomas