Re: checking existence of a table before updating its SERIAL - Mailing list pgsql-general

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