Escaping keyword-like column names in Postgres
When column names double as SQL keywords, encase them within double quotes to prevent errors in PostgreSQL.
This ensures PostgreSQL interprets "user" and "order" as column names, not keywords. The operation is case-sensitive.
For reserved keywords like "year", the usage of double quotes avoids errors. Without them, PostgreSQL might interpret "year" as a function or a date fragment.
A column name with double quotes? They need to be doubled within the already double quotes.
Guard your column: Quote it!
In case a column name has double quotes, double them inside:
Here, the column name, a"b"c
, needs the extra pairs of quotes.
Surviving the case (sensitivity) and schema nightmares
Identifiers without quotes are automatically lowercased in Postgres. So, "User"
and "user"
are different:
To evade the hair-pulling double quote dance, you can specify the schema name. This helps avoid clashing with keywords:
But if the schema and table names are reserved words, you're back to square one.
Avoid the bear traps: Common issues and solutions
Keyword collisions
- Wrap keyword-like columns in double quotes for both DDL and DML operations.
Case sensitivity
- Stick to the case used during the creation, or go all lowercase with unquoted identifiers.
Forgetting the quotes
- Learn your SQL standards and refer to the Postgres documentation when in doubt.
Dress-up the columns: Using aliases
For a cleaner and self-explanatory query, use aliases for quoted columns:
Now, user_account
and customer_order
are more convenient substitutes for their respective quoted identifiers.
Was this article helpful?