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

postgresql quoted-identifier
Orysza picture Orysza · Mar 22, 2019 · Viewed 7.8k times · Source

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

Kjetil S. picture Kjetil S. · Mar 22, 2019

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.

Related questions

Cannot simply use PostgreSQL table name ("relation does not exist")

I'm trying to run the following PHP script to do a simple database query: $db_host = "localhost"; $db_name = "showfinder"; $username = "user"; $password = "password"; $dbconn = pg_connect("host=$db_host dbname=$db_name user=$username password=$password") or die('Could …

Read More
PostgreSQL column 'foo' does not exist

I have a table that has 20 integer columns and 1 text column named 'foo' If I run query: SELECT * from table_name where foo is NULL I get error: ERROR: column "foo" does not exist I have checked myself that his …

Read More
PostgreSQL "Column does not exist" but it actually does

I'm writing a Java application to automatically build and run SQL queries. For many tables my code works fine but on a certain table it gets stuck by throwing the following exception: Exception in thread "main" org.postgresql.util.PSQLException: …

Read More