PostgreSQL: IF EXISTS COMMENT ON TABLE

Question

I am little bit stucked with task IF EXIST.

I have several lines query with the COMMENTS ON TABLE function

COMMENT ON TABLE my_table1 IS 'Comment for table1';
COMMENT ON TABLE my_table2 IS 'Comment for table2';
COMMENT ON TABLE my_table3 IS 'Comment for table3';
COMMENT ON TABLE my_table3 IS 'Comment for table4';

I want to do, when I will execute this query, if one of the table doesn't exist query will pass that and continue.

I tried to do that task with IF EXIST action, but it doesn't works like UPDATE or other actions. Where am I wrong?

IF EXISTS (SELECT relname FROM pg_class where relname='my_table1')
then
COMMENT ON TABLE my_table1 IS 'Comment for table1';
IF EXISTS (SELECT relname FROM pg_class where relname='my_table2')
then
COMMENT ON TABLE my_table1 IS 'Comment for table2';
IF EXISTS (SELECT relname FROM pg_class where relname='my_table3')
then
COMMENT ON TABLE my_table1 IS 'Comment for table3';
IF EXISTS (SELECT relname FROM pg_class where relname='my_table4')
then
COMMENT ON TABLE my_table1 IS 'Comment for table4';

score 3 · Accepted Answer · answered Jan 07 '18 at 13:46

3

You could use:

DO
$do$
BEGIN
  IF EXISTS (SELECT relname FROM pg_class where relname='my_table1') THEN
    COMMENT ON TABLE my_table1 IS 'Comment for table1';
  END IF;

  IF EXISTS (SELECT relname FROM pg_class where relname='my_table2') THEN
    COMMENT ON TABLE my_table1 IS 'Comment for table2';
  END IF;
END
$do$

And check:

SELECT relname, obj_description(oid) 
FROM pg_class 
WHERE relname LIKE 'my_table%';

DBFiddle Demo

answered Jan 07 '18 at 13:46

Lukasz Szozda

162,964
23
234
275

1

Magnificent! That's what I need. Thank you very much. – mulrus Jan 07 '18 at 14:44

score 0 · Answer 2 · answered Jan 07 '18 at 14:50

There is currently (Postgres 10) no IF EXISTS for the COMMENT command. You'll have to check for existence one or the other way to avoid exceptions - which which lead to ROLLBACK for your complete transaction.

So one option would be to run separate transactions an just ignore any errors:

BEGIN;
COMMENT ON TABLE my_table1 IS 'Comment for table1';
COMMIT;

BEGIN;
COMMENT ON TABLE my_table2 IS 'Comment for table2';
COMMIT;

-- etc.

Or you test with IF like @lad2025 provided.

Or, you might shorten the code with to_regclass(), which returns NULL for non-existent tables. See:

How to check if a table exists in a given schema

DO
$do$
DECLARE
   _tbls text[] :=
      '{my_table1
      , my_table2
      , my_table3
      , my_table4}';
   _comments text[] :=
      '{"Comment for table1"
      , "Comment for table2"
      , NULL
      , "Comment for table4"}';
BEGIN
EXECUTE 
(SELECT string_agg('COMMENT ON TABLE ' || to_regclass(t) || ' IS ' || quote_nullable(c), '; ')
 FROM   unnest(_tbls, _comments) t(t, c));
END
$do$;

You can also delete any comment this way (reset it to NULL). Also note the use of quote_nullable().

Check:

SELECT t1 AS table, obj_description(t1) AS table_comment
FROM  (SELECT to_regclass(t) AS t1
       FROM unnest('{my_table1, my_table2, my_table3, my_table4}'::text[]) t) sub;

dbfiddle here

Unnest multiple arrays in parallel

Hello. Also thank you for your advice for that actions with COMMENTs, also but I am still use PostgreSQL 9.4. But I will be keep in mind your info about PostgreSQL 10, thanks. — mulrus, Jan 07 '18 at 15:01
@mulrus: Oh, everything of the above works with pg 9.4 (which should be declared in the questions to begin with!). Just stating that there is still no `IF EXISTS` for `COMMENT` in pg 10. — Erwin Brandstetter, Jan 07 '18 at 15:08
also mentioned on PostgreSQL 8.4 that DO function doesn't work, but I added $$ with the create or declare function action and everything fine. — mulrus, Jan 08 '18 at 19:47

PostgreSQL: IF EXISTS COMMENT ON TABLE

2 Answers2