When do Postgres column or table names need quotes and when don't they?

Question

Let's consider the following postgres query:

SELECT * 
FROM "MY_TABLE"
WHERE "bool_var"=FALSE 
 AND "str_var"='something';

The query fails to respond properly when I remove quotes around "str_var" but not when I do the same around "bool_var". Why? What is the proper way to write the query in that case, no quotes around the boolean column and quotes around the text column? Something else?

Answer 1

PostgreSQL converts all names (table name, column names etc) into lowercase if you don't prevent it by double quoting them in create table "My_Table_ABC" ( "My_Very_Upper_and_Lowercasy_Column" numeric,...). If you have names like this, you must always double quote those names in selects and other references.

I would recommend not creating tables like this and also not using chars outside a-z, 0-9 and _. You can not guarantee that every piece of software, library etc ever to be used against your database will support case-sensitivity. It's also tedious to remember and doing this double quoting.