Thread: Bug with "CHECK" when part of ALTER TABLE ... ADD COLUMN IF NOTEXISTS ... CHECK ...

Hi,

I ran into what seems to be a bug with this command.

Postgres version: PostgreSQL 11.5 (Debian 11.5-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

Expected behavior:

If the column exists nothing is altered.

Actual behavior:

The check is always added regardless if the column exists or not.

Reproduction steps:

CREATE TABLE "element_instances" (
  "instance_id" UUID NOT NULL DEFAULT uuid_generate_v4(),
  "generation" INTEGER NOT NULL DEFAULT 1,
  "element" CHARACTER VARYING(1024) NOT NULL CHECK(element <> ''),
  "kind" CHARACTER VARYING(64) NOT NULL CHECK(kind <> ''),
  "observed_start_time" TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  "observed_end_time" TIMESTAMP WITHOUT TIME ZONE,
  "estimated_time" TSRANGE NOT NULL,
  "storage_location" CHARACTER VARYING(1024),
  "assets" CHARACTER VARYING(1024)[] NOT NULL,
  "s2cells" BIGINT[] NOT NULL,
  EXCLUDE USING GIST (element WITH =, estimated_time WITH &&),
  PRIMARY KEY ("instance_id")
);

ALTER TABLE IF EXISTS "element_instances"
  ALTER COLUMN "storage_location" DROP NOT NULL,
  ALTER COLUMN "generation" SET NOT NULL,
  DROP COLUMN IF EXISTS "instance_name" CASCADE,
  ADD COLUMN IF NOT EXISTS "kind" CHARACTER VARYING(64) NOT NULL CHECK(kind <> '');

The second command should not change anything; however, then if you run:
SELECT con.conname, con.consrc 
  FROM pg_catalog.pg_constraint con 
  INNER JOIN pg_catalog.pg_class rel 
  ON rel.oid = con.conrelid 
  WHERE rel.relname = "element_instances" 
  ORDER BY con.conname;

you will see:

...

element_instances_kind_check, "((kind)::text <> ''::text)"
element_instances_kind_check1, "((kind)::text <> ''::text)"

...

A duplicate constraint has been added!