Are PostgreSQL column names case sensitive?

Question

I have a db table, say persons in Postgres passed by another command that has a column name, "first_Name" . Now I'm trying to use the PG command to query this table for this column name.

 select * from persons where first_Name="xyz"; 

And he just returns

ERROR: column "first_Name" does not exist

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

Answer 1

All identifiers (including column names) that are not double quotation marks are added to lowercase in PostgreSQL. Column names that were created using double quotes and thereby retained uppercase letters (and / or other syntax violations) should be doubled for the remainder of their lives. So yes, PostgreSQL column names are case sensitive:

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

Also fix invalid double quotes around 'xyz' . Values ​​(string literals) are enclosed in single quotes.

Read the manual here.

My constant advice is to use legal names with the smallest names, so double quoting is not required.

Answer 2

To quote the documentation :

Keywords and unquoted identifiers are case insensitive. Therefore:

 UPDATE MY_TABLE SET A = 5; 

can be equivalently written as:

 UPDATE MY_TABLE SET A = 5; 

Quoting an identifier makes it case sensitive, while unnamed names always add lowercase:

 UPDATE "my_table" SET "a" = 5; // equivalent to the above examples 

Answer 3

Column names that are mixed or uppercase must be double enclosed in the sql address of postgres. Thus, the best deal would be to follow the entire small case with an underline.