Are PostgreSQL column names case-sensitive?

Question

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?

Answer 1

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

"first_Name"                 -- upper-case "N" preserved
"1st_Name"                   -- leading digit preserved
"AND"                        -- reserved word preserved

But (without double-quotes):

first_Name   → first_name    -- upper-case "N" folded to lower-case "n"
1st_Name     → Syntax error! -- leading digit
AND          → Syntax error! -- reserved word

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';

The manual on identifiers.

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

System catalogs like pg_class store names in case-sensitive fashion - as provided when double-quoted (without enclosing quotes, obviously), or lower-cased if not.