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

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Rusty Conover-3
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
Reply | Threaded
Open this post in threaded view
|

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

Sergei Kornilov
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 from these three and each other.

regards, Sergei