Mixing quoted mixed case column names and non quoted constraints definitions in CREATE TABLE causes an error

From: Rusty Conover <rusty(at)conover(dot)me>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Mixing quoted mixed case column names and non quoted constraints definitions in CREATE TABLE causes an error
Date: 2019-02-03 19:03:55
Message-ID: CAPFu4vw=aUVMRgnm9vURSEuL3=_6DeR=Vjb44vgcSaZsQCTtag@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sergei Kornilov 2019-02-03 20:02:12 Re: Mixing quoted mixed case column names and non quoted constraints definitions in CREATE TABLE causes an error
Previous Message Evgeniy Efimkin 2019-02-02 17:41:29 Re: BUG #15617: walsender hang if sync replica disconnected from network