checking existence of a table before updating its SERIAL

From: Matthias Apitz <guru(at)unixarea(dot)de>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: checking existence of a table before updating its SERIAL
Date: 2020-06-08 07:53:58
Message-ID: 20200608075358.GA17471@sh4-5.1blu.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hello,

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?

Thanks in advance.

matthias

--
Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-06-08 08:05:59 Re: checking existence of a table before updating its SERIAL
Previous Message Laurenz Albe 2020-06-08 06:50:27 Re: Multitenent architecture