Are PostgreSQL column names case-sensitive?

sql postgresql identifier case-sensitive case-insensitive
5122014009 picture 5122014009 · Jan 2, 2014 · Viewed 114.5k times · Source

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

Erwin Brandstetter picture Erwin Brandstetter · Jan 2, 2014

All 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")

So, yes, PostgreSQL column names are case-sensitive:

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

Also fix the incorrect double-quotes around 'xyz'. Values (string literals) are enclosed in single quotes.

Read the manual here.

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

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More