postgresqlquoted-identifier

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

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?

Solution

  • 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.