Inserting Strings with Single Quotes in PostgreSQL

PostgreSQL, like many SQL databases, uses single quotes to delimit string literals. This creates a challenge when the string you want to insert itself contains single quotes. This tutorial explains how to correctly insert such strings into your PostgreSQL tables.

The Problem: Single Quotes Within Strings

Consider a table named test with columns id and name. You might want to insert values like "user’s log", "’my user’", or "customer’s" into the name column. Directly using these strings in an INSERT statement will lead to syntax errors because the database interprets the internal single quotes as the end of the string literal.

The Solution: Escaping Single Quotes

The standard way to include a single quote within a PostgreSQL string literal is to escape it by doubling it. This means replacing each single quote (') with two single quotes ('').

Here’s how it works:

"user’s log" becomes ‘user”s log’
"’my user’" becomes ”’my user”’
"customer’s" becomes ‘customer”s’

Here are example INSERT statements:

INSERT INTO test VALUES (1, 'user''s log');
INSERT INTO test VALUES (2, '''my user''');
INSERT INTO test VALUES (3, 'customer''s');

In these examples, the doubled single quotes tell PostgreSQL to treat them as literal characters within the string, rather than the end of the string delimiter.

Alternative Escaping Methods (Less Common)

While doubling single quotes is the preferred and most readable method, there are other options:

1. Backslash Escaping (with standard_conforming_strings enabled):

If the PostgreSQL configuration parameter standard_conforming_strings is set to on (which is the default), you can use a backslash (\) to escape single quotes. However, this method is less portable and not always recommended.

INSERT INTO test VALUES (1, 'user\'s log');

Important: Be aware that the behavior of backslash escaping depends on the value of standard_conforming_strings. It’s best practice to rely on doubling the single quotes for consistency.

2. chr() Function:

You can use the chr() function to insert a single quote by providing its ASCII code (39). This is less readable and generally unnecessary:

INSERT INTO test VALUES (1, 'user' || chr(39) || 's log');

Dollar-Quoted Strings

PostgreSQL also supports dollar-quoted strings, which provide a more flexible way to define strings, especially those containing many single quotes or other special characters. Dollar quotes allow you to define a string enclosed within a delimiter of your choosing.

INSERT INTO test VALUES (1, $token$user's log$token$);

Here, $token$ is the delimiter. You can use any unique string as the delimiter. Dollar-quoted strings can be nested, providing even greater flexibility.

Best Practices & Security Considerations

Always escape single quotes: Regardless of the method you choose, always ensure that any string literal containing single quotes is properly escaped before being used in a SQL query.
Prepared Statements: The most secure and recommended approach is to use parameterized queries (prepared statements) with your database driver. Prepared statements separate the SQL code from the data, preventing SQL injection vulnerabilities. This method is beyond the scope of this tutorial but is a crucial security practice.
Avoid dynamic SQL when possible: Constructing SQL queries by concatenating strings can be error-prone and vulnerable to injection attacks.

By following these guidelines, you can safely and effectively insert strings containing single quotes into your PostgreSQL database.