Strange case conversion in PostgreSQL 9.5.4

Question

SELECT AVG(MY_AE_Actual) FROM MY_Data_Details

results in an error:

ERROR: column "my_ae_actual" does not exist LINE 1: SELECT AVG(MY_AE_Actual) FROM MY_Data_Details ^ HINT: Perhaps you meant to reference the column "my_data_details.my_ae_actual111". ********** Fehler **********

ERROR: column "my_ae_actual" does not exist SQL Status:42703 Hinweis:Perhaps you meant to reference the column "my_data_details.my_ae_actual111". Zeichen:12

Update

It is quite strange. I tested now in PostgreSQL 10 with following table:

CREATE TABLE public.testable
(
    id integer NOT NULL DEFAULT nextval('testable_id_seq'::regclass),
    string_data1 character varying(255) COLLATE pg_catalog."default",
    "String_Data2" character varying(255) COLLATE pg_catalog."default",
    "string_Data3" character varying(255) COLLATE pg_catalog."default",
    "String_data4" character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT testable_pkey PRIMARY KEY (id)
)

select string_data1 from testable - success
select String_data1 from testable - success
select string_Data1 from testable - success
select String_Data1 from testable - success
select "string_data1" from testable - success
select "String_data1" from testable - failure
select "string_Data1" from testable - failure
select "String_Data1" from testable - failure
select string_data2 from testable - failure
select String_data2 from testable - failure
select string_Data2 from testable - failure
select String_Data2 from testable - failure
select "string_data2" from testable - failure
select "String_data2" from testable - failure
select "string_Data2" from testable - failure
select "String_Data2" from testable - success

It turns out that without quotes PostgreSQL is not "case insensitive", but "lower casing" which makes no sense at all.

Could it be that the column is indeed called `my_ae_actual111`? — Jim Jones, Jun 19 '18 at 15:31
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS — , Jun 19 '18 at 15:59
Postgres **is** case insensitive when an unquoted identifier is used (`foo`, `FOO`, `Foo`, `fOo` are all the same identifier. And `"String_Data2"` is something different than `String_Data2` This is well documented [in the manual](https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) — , Jun 19 '18 at 19:25

score 1 · Accepted Answer · edited Jun 19 '18 at 19:03

Postgresql forces lower case unless you use double quotes so

ThisFieldName == thisfieldname

But:

"ThisFieldName"  <> ThisFieldName
"ThisFieldName"  <> thisfieldname

If you create your field like "ThisFieldName" you need reference it the same.

In your case you try to use MY_AE_Actual but postgresql tell you my_ae_actual name doesn't exist.

Sames go for table names.

My suggestion don't use upper case letters in Postgresql. I use all lower case with underscore _ as separator. But that is just a personal preference.

Strange case conversion in PostgreSQL 9.5.4

1 Answers1