Using postgresql 11.1.
I think there is a bug when quoting a mixed case column name in the column definition of a CREATE TABLE then creating a constraint where the field name is not quoted but specified in mixed case.
CREATE TABLE "question" ("fooBar" boolean NOT NULL DEFAULT false, CONSTRAINT f CHECK (fooBar = false));
Error:
2019-02-03 13:55:30.519 EST [19696] ERROR: column "foobar" does not exist
2019-02-03 13:55:30.519 EST [19696] HINT: Perhaps you meant to reference the column "question.fooBar".
2019-02-03 13:55:30.519 EST [19696] STATEMENT: CREATE TABLE "question" ("fooBar" boolean NOT NULL DEFAULT false, CONSTRAINT f CHECK (fooBar = false));
ERROR: 42703: column "foobar" does not exist
HINT: Perhaps you meant to reference the column "question.fooBar".LOCATION: errorMissingColumn, parse_relation.c:3294
It is very strange that the column name is converted to lowercase in the error message even though it was specified as mixed case.
This query does succeed surprisingly:
CREATE TABLE "question" ("foobar" boolean NOT NULL DEFAULT false, CONSTRAINT foobar CHECK (foobar = false));
Also if I quote the column name in the constraint expression it does succeed.
CREATE TABLE "question" ("fooBar" boolean NOT NULL DEFAULT false, CONSTRAINT f CHECK ("fooBar" = false));
I think all of these queries should succeed regardless if the column is defined as having a mixed case name.
Thank you,
Rusty