Bug with "CHECK" when part of ALTER TABLE ... ADD COLUMN IF NOT EXISTS ... CHECK ...

From: "Alfred R(dot) Fuller" <alfred(dot)fuller(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Bug with "CHECK" when part of ALTER TABLE ... ADD COLUMN IF NOT EXISTS ... CHECK ...
Date: 2019-09-24 18:37:39
Message-ID: CAMESm9+SEO6ZNUNMh7+dkko_TiksOQC2_0hFeM0gBE8Fk3k7kA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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!

Thanks,

Alfred

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Fabrízio de Royes Mello 2019-09-24 20:27:19 Re: Bug with "CHECK" when part of ALTER TABLE ... ADD COLUMN IF NOT EXISTS ... CHECK ...
Previous Message Tom Lane 2019-09-24 14:17:05 Re: BUG #16019: error pg_restore from pg_dump(windows8.1-pgadmin 4.12)