I come from a MySQL background, where single quotes and double quotes are generally interchangeable. But in a recent switch to PostgreSQL, I kept running into errors like this:

ERROR: column "XXX" does not exist

And they were driving me mad!

To put an end to this madness once and for all, I compiled this PostgreSQL reference so that I never use the wrong kind of quotes ever again!

Why are MySQL and PostgreSQL different?

There are many SQL vendors, so over the decades, many SQL variants emerged. In response, the American National Standards Institute (ANSI) created a set of SQL standards to promote consistency and best practices across all these variants.

Adhering to ANSI SQL is not mandatory. Vendors are free to follow the ANSI standard, another established standard (e.g. ODBC), or to create one of their own. They may also create vendor-specific plugins and extensions. This is why MySQL and PostgreSQL differ. MySQL doesn't attempt to achieve ANSI-compliance as closely as PostgreSQL does.

The PostgreSQL stance on quotes

PostgreSQL may seek to be ANSI-compliant, but the best place to learn about its stance on quotes is to visit its official documentation. The relevant excerpts are republished below.

Single quotes are used around string constants:

A string constant in SQL is an arbitrary sequence of characters bounded by single quotes ('), for example 'This is a string'. To include a single-quote character within a string constant, write two adjacent single quotes, e.g., 'Dianne''s horse'. Note that this is not the same as a double-quote character (").

Double quotes are used around identifiers:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named “select”, whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

Some of the terminology used in the double-quote excerpt is important to understand, so here's a quick refresher:

  • A key word is a word that has a fixed meaning in the SQL language. Examples include SELECT, UPDATE, and VALUES.
  • An identifier identifies a table, column, or other database object. Examples might be customers or transactions or employee_id.

Let's stop there. The documentation seems clear enough, but it's probably easier to grok with a few examples. How are quotes used in practice?

Examples of single quotes

The trivial case

This query uses single quotes to denote that the 'hello' token is a string constant:

SELECT 'hello' FROM customers LIMIT 5;

Double quotes will not work. If attempted, an error will be thrown that column "hello" does not exist.

String constants in functions

Avid readers of my blog might remember this query from an earlier post on how to calculate week offsets:

  SELECT DATE_TRUNC('WEEK', created_at) AS week,
         COUNT(*)
    FROM customers
GROUP BY 1
ORDER BY 1 DESC;

At first glance, it may seem like 'WEEK' is an identifier because it's used in a PostgreSQL function, but don't be fooled! The first parameter of DATE_TRUNC accepts a string, and 'WEEK' needs single quotes because it is a string constant.

Apostrophes in string constants

String constants containing apostrophes need an additional ' to be escaped:

INSERT INTO some_table(column1, column2)
     VALUES ('i''m an escaped string constant', 'another string');

This is explicitly explained in the documentation, but my initial attempt had been MySQL's \' instead. Now I know better.

Examples of double quotes

The trivial case

SELECT * FROM "customers";

Here, "customers" is a delimited identifier for a SQL table. Because the token can't be misinterpreted, the double quotes are often dropped:

SELECT * FROM customers;

Similarly, double quotes can be used (but aren't always necessary) around tokens that are column or alias identifiers:

SELECT "created_at" AS "joined_time" FROM customers;

SQL key words

The VALUES token is a SQL key word, but double quotes will force PostgreSQL to interpret VALUES as an identifier instead. The following query successfully retrieves data from a table named VALUES instead of running the VALUES command:

SELECT * FROM "VALUES";

There's some nuance regarding reserved vs. non-reserved SQL key words. For non-reserved key words, the PostgreSQL parser is smart enough to correctly interpret a token as an identifier even when double quotes aren't used. With reserved key words, however, the double quotes are always required. As best practice, any SQL key word being rebranded as an identifier should have double quotes.

Case sensitivity

Regular identifiers are case-insensitive, so a table named customers can be queried with any of the following:

SELECT * FROM customers;
SELECT * FROM Customers;
SELECT * FROM CUSTOMERS;
SELECT * FROM CuStOmErS;

However, the delimited identifier is case-sensitive! For database objects that use uppercase letters in their names, double quotes are required:

SELECT * FROM "SomeCaseSensitiveTableName";

Personally, I stick to lowercase identifiers. The simplicity is worth it.

Special characters

SELECT * FROM "terrible table";
SELECT * FROM "terr!ble t@able";

I hope that you never have to deal with a database where special characters (like spaces) are used in the table or column names. Nevertheless, it is possible to do. Always use double quotes.

Examples of single and double quotes

Very often, single quotes and double quotes will be used together in the same query:

CREATE ROLE "user" WITH LOGIN PASSWORD 'password';

The above query has double quotes around the "user" token because it is an identifier for a PostgreSQL role. The password, on the other hand, is a string constant that doesn't identify a database object. Hence, single quotes.

From personal experience, I know it can be easy to confuse where each kind of quote is used. Hopefully, this reference can help you. That said, SQL also has a backtick character too...