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