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?

Errolerroll

I can run your entire query with no double quotes anywhere. You would typically only need those double quotes if a table name or column happens to coincide with a Postgres reserved keyword. – Clipping

Those aren't "variables", those are column names. – Cordle

See the explanation in the manual – Cordle

@TimBiegeleisen it only works in your example because you created the table without double quotes. Since OP's query works with the double quotes, his table name is in uppercase, so his query would fail without double quoting the table name. – Dispenser

@Dispenser Good intuition +1. Yes, case sensitivity would be another reason for using double quotes, didn't think of that. – Clipping

Answer 1

Thanks to @TimBiegeleisen's comment, I was able to pinpoint the problem; I used a reserved keyword ("user") as a column name.

Link to reserved keywords in the doc: https://www.postgresql.org/docs/current/sql-keywords-appendix.html.

Now I know not to use quotes to query column names, but rather to avoid reserved keywords as column names.

Errolerroll

The general advice indeed is to never use double quotes around identifiers in sQL – Cordle

There is also convention when creating a table that can cause it to be case sensitive, thus requiring quotes as well. Just an FYI so you can avoid encountering this scenario unintentionally as well. #20879432 – Sherrell

Answer 2

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.

Promotion

This just bit me -- I inherited an application that uses Prisma in front of Postgres, and it generates relation tables with columns named "A" and "B". So where A = 1 is an invalid column name, but where "A" = 1 is fine. >_< – Nanceenancey