Search code examples
sqlpostgresqlquoted-identifier

Strange case conversion in PostgreSQL 9.5.4

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.

Solution

  • 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.