Explain Codes LogoExplain Codes Logo

Escaping keyword-like column names in Postgres

sql
database-design
sql-best-practices
postgres
Nikita BarsukovbyNikita Barsukov·Dec 26, 2024
TLDR

When column names double as SQL keywords, encase them within double quotes to prevent errors in PostgreSQL.

SELECT "user", "order" FROM "my_table";

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.

SELECT "year" FROM "financial_data";

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:

CREATE TABLE "my_table" ("a""b""c" VARCHAR(10)); -- "Whoever designed this column name must love abstraction," - Reddit user

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:

CREATE TABLE "Users" ( "Id" SERIAL, "userName" VARCHAR(100) ); -- Postgres laughing: “Got ya! Thought case sensitivity isn’t a thing in SQL?”

To evade the hair-pulling double quote dance, you can specify the schema name. This helps avoid clashing with keywords:

SELECT schema_name.table_name.year FROM schema_name.table_name; -- SQL equivalent of 'Speak your name, soldier!'

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:

SELECT "user" AS user_account, "order" AS customer_order FROM "my_table"; -- And my parents said playing dress-up wouldn’t get me anywhere. Look at this query!

Now, user_account and customer_order are more convenient substitutes for their respective quoted identifiers.