Are PostgreSQL column names case-sensitive?

176

I have a db table say, persons in Postgres handed down by another team that has a column name say, "first_Name". Now am trying to use PG commander to query this table on this column-name.

select * from persons where first_Name="xyz";

And it just returns

ERROR: column "first_Name" does not exist

Not sure if I am doing something silly or is there a workaround to this problem that I am missing?

This question is tagged with sql postgresql identifier case-sensitive case-insensitive

~ Asked on 2014-01-02 08:21:15

The Best Answer is


323

Identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL. Column names that were created with double-quotes and thereby retained upper-case letters (and/or other syntax violations) have to be double-quoted for the rest of their life:

"first_Name"

Values (string literals / constants) are enclosed in single quotes:

'xyz'

So, yes, PostgreSQL column names are case-sensitive (when double-quoted):

SELECT * FROM persons WHERE "first_Name" = 'xyz';

Read the manual on identifiers here.

My standing advice is to use legal, lower-case names exclusively so double-quoting is not needed.

~ Answered on 2014-01-02 09:53:47


20

To quote the documentation:

Key words and unquoted identifiers are case insensitive. Therefore:

UPDATE MY_TABLE SET A = 5;

can equivalently be written as:

uPDaTE my_TabLE SeT a = 5;

You could also write it using quoted identifiers:

UPDATE "my_table" SET "a" = 5;

Quoting an identifier makes it case-sensitive, whereas unquoted names are always folded to lower case (unlike the SQL standard where unquoted names are folded to upper 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.

If you want to write portable applications you are advised to always quote a particular name or never quote it.

~ Answered on 2015-04-27 15:37:38


Most Viewed Questions: