21

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?

Orysza
  • 574
  • 1
  • 5
  • 10
  • [I can run your entire query with no double quotes anywhere](https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=809e7aaf6cc0cd19c7ef46ba84e8c705). You would typically only need those double quotes if a table name or column happens to coincide with a Postgres reserved keyword. – Tim Biegeleisen Mar 22 '19 at 10:44
  • Those aren't "variables", those are column names. –  Mar 22 '19 at 11:14
  • See the explanation [in the manual](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) –  Mar 22 '19 at 11:15
  • 2
    @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. – 404 Mar 22 '19 at 12:10
  • @404 Good intuition +1. Yes, case sensitivity would be another reason for using double quotes, didn't think of that. – Tim Biegeleisen Mar 22 '19 at 12:32

2 Answers2

40

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.

Kjetil S.
  • 3,468
  • 20
  • 22
  • 1
    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"](https://stackoverflow.com/questions/71660945/how-to-specify-names-for-implicit-many-to-many-relation-table-columns-in-prisma). So `where A = 1` is an invalid column name, but `where "A" = 1` is fine. >_ – Coderer Jan 16 '23 at 20:08
7

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.

Orysza
  • 574
  • 1
  • 5
  • 10
  • 2
    The general advice indeed is to never use double quotes around identifiers in sQL –  Mar 22 '19 at 12:45
  • 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. https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive – gsteiner Mar 23 '19 at 16:05