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

On Tue, Sep 24, 2019 at 5:15 PM Alfred R. Fuller <alfred(dot)fuller(at)gmail(dot)com>
wrote:

> 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!
>
>
I'll take a look on it?

--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento