Executing a PostgreSQL query with an EXCEPTION results in two different ERROR messages

Question

I have an PostgreSQL query that includes a transaction and an exception if a column is duplicated:

BEGIN;
ALTER TABLE "public"."cars" 
  ADD COLUMN "top_speed" text;
EXCEPTION WHEN duplicate_column THEN NOTHING;
ROLLBACK;

In this query I am trying to add a column that already exists (playing a little bit with exceptions) and if it does then the query shall just ignore it. At the moment I am not really sure if the exception-code I am using is the right (couldn't find a site where they are described; only found this)

My Problem is if I execute this query I get the error-message:

ERROR: column "top_speed" of relation "cars" already exists

And if I execute it a second time the error-message changes to:

ERROR: current transaction is aborted, commands ignored until end of transaction block

You are mixing up SQL and PL/pgSQL. You can use a DO statement, but then you cannot have a semicolon after BEGIN (it starts a block, not a transaction). — Laurenz Albe
– Laurenz Albe, Commented Jan 13, 2020 at 8:13

Jim Jones · Accepted Answer · 2020-01-13 13:44:56Z

1

Try an anonymous code block. As Laurenz mentioned, you were mixing PL/pgSQL and SQL commands.

Sample table

CREATE TABLE t (f1 TEXT);

Anonymous code block

DO $$
BEGIN
  IF (SELECT count(column_name) FROM information_schema.columns 
      WHERE table_schema = 'public' AND
            table_name = 't' AND
            column_name = 'f2') = 0 THEN 
      ALTER TABLE public.t ADD COLUMN "f2" text;    
  END IF;
END$$;

After execution you have your new column. If the column already exists, it will do nothing.

SELECT * FROM t;
 f1 | f2 
----+----
0 Zeilen

In PostgreSQL 9.6+ you can use IF NOT EXISTS to check if a given column already exists in the table before creating it:

ALTER TABLE t ADD COLUMN IF NOT EXISTS f2 TEXT;

Code at db<>fiddle

edited Jan 13, 2020 at 13:44

answered Jan 13, 2020 at 8:32

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Executing a PostgreSQL query with an EXCEPTION results in two different ERROR messages

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related