CODEWITHSUNDEEP
sqlpostgresql
leon52
leon52

Reputation: 99

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

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

Upvotes: 2

Views: 1012

Answers (1)

Jim Jones
Jim Jones

Reputation: 19623

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

Upvotes: 1

Related Questions