checking existence of a table before updating its SERIAL

On Monday, June 8, 2020, Matthias Apitz <guru@unixarea.de> wrote:

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?

https://www.postgresql.org/docs/12/catalogs-overview.html

David J.

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