Postgres Pro
Downloads
Home   >   mailing lists

Thread: Mixing quoted mixed case column names and non quoted constraintsdefinitions in CREATE TABLE causes an error

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

From
Rusty Conover
Date:
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

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

From
Sergei Kornilov
Date:
Hello

This is expected and documented behavior:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

> Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For
example,the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different
fromthese three and each other.
 

regards, Sergei