CODEWITHSUNDEEP
postgresqlquoted-identifier
Orysza
Orysza

Reputation: 714

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?

Upvotes: 27

Views: 28512

Answers (2)

Kjetil S.
Kjetil S.

Reputation: 3777

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.

Upvotes: 51

Orysza
Orysza

Reputation: 714

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.

Upvotes: 11

Related Questions