On PostgreSQL, a column whose name contains an uppercase letter does not behave like the others. Without precaution, the query fails with a non-intuitive error. This article explains why PostgreSQL treats uppercase differently, how to write the query correctly, and the best practice to avoid the problem at the root.
The default PostgreSQL behavior with uppercase
PostgreSQL automatically converts every unquoted identifier to lowercase. This includes table names, column names, and aliases. A column declared as queueName is therefore referenced as queuename if you do not add quotes.
This behavior is part of the SQL standard. It aims to make queries case-insensitive for unprotected identifiers. It is consistent at scale, but it surprises as soon as a column name contains an uppercase letter.
Forcing case with double quotes
To preserve the uppercase, you must wrap the column name with double quotes. Without them, the query fails with an error message:
-- Fails with: column "queuename" does not exist
SELECT * FROM _queue_items WHERE queueName = 'crawl-queue';
-- Works
SELECT * FROM _queue_items WHERE "queueName" = 'crawl-queue';Double quotes tell PostgreSQL to take the identifier as is, without case conversion. This is mandatory as soon as an uppercase letter is present, even a single one.
In read and in write
The rule applies everywhere the identifier appears. All these forms require the double quotes around queueName:
SELECT "queueName" FROM _queue_items;
UPDATE _queue_items SET "queueName" = 'foo';
INSERT INTO _queue_items ("queueName") VALUES ('foo');
SELECT * FROM _queue_items ORDER BY "queueName";Forgetting the quotes on a single one of these commands triggers the same column does not exist error. Note that double quotes are never used for values, which remain wrapped in single quotes.
Difference with MySQL and SQL Server
MySQL uses backticks to protect identifiers. Its case sensitivity depends on the file system: sensitive on Linux, insensitive on macOS and Windows by default. SQL Server accepts brackets [queueName] and its behavior depends on the collation.
A migration from one of these RDBMSs to PostgreSQL is therefore often the moment when the trap is discovered, sometimes in production.
Best practice: lowercase names
To stop worrying about double quotes, the cleanest convention is to name all columns in lowercase with underscores: queue_name rather than queueName. This is the style recommended by the PostgreSQL community, and most modern ORMs (Doctrine, SQLAlchemy, ActiveRecord) follow it by default.
If you inherit a camelCase schema, the pragmatic option is to always quote, everywhere, as soon as an uppercase letter is present in an identifier name.