How to Escape Single Quotes in PostgreSQL ?

Escaping Single Quotes in PostgreSQL

Single quotes are commonly used in PostgreSQL to delimit string literals. However, if a single quote needs to appear within a string, it must be properly escaped to avoid syntax errors. PostgreSQL offers several ways to handle this scenario, ensuring seamless query execution.

Syntax:

There are multiple approaches to escape single quotes in PostgreSQL:

1. Escape with Two Single Quotes ('')

SELECT 'It''s a beautiful day';

2. Use Dollar-Quoted Strings ($$)

SELECT $$It's a beautiful day$$;

3. Escape Using Backslash (\) (Requires standard_conforming_strings to be OFF)

SELECT 'It\'s a beautiful day';

Examples

1. Escaping with Two Single Quotes

Code:

-- Use two single quotes to include one within the string
SELECT 'This is Afina''s book' AS escaped_string;

Output:

escaped_string
This is Afina's book

2. Using Dollar-Quoted Strings

Code:

-- Use dollar quotes to handle special characters like single quotes
SELECT $$This is Afina's car$$ AS escaped_string;

Output:

escaped_string
This is Afina's car

3. Using Backslash (Legacy Method)

Code:

-- Escape the single quote with a backslash (legacy method)
-- Note: Requires standard_conforming_strings = OFF
SET standard_conforming_strings = OFF;
SELECT 'This is Afina\'s house' AS escaped_string;

Output:

escaped_string
This is Afina's house

Explanation:

1. Two Single Quotes: The most reliable method in PostgreSQL. When two consecutive single quotes are placed, PostgreSQL interprets them as a single quote within the string.

2. Dollar-Quoted Strings: A flexible option, especially useful for strings with multiple single quotes or special characters.

3. Backslash Method: A legacy option, but it requires configuration changes, making it less commonly used in modern PostgreSQL versions.

Precautions

• SQL Injection: Always use parameterized queries or prepared statements to prevent SQL injection attacks when handling dynamic strings with single quotes.
• Standard Configuration: Avoid relying on the backslash method as it depends on specific server configurations (standard_conforming_strings).

Use Cases

• Handling user inputs with single quotes in text.
• Writing queries for text containing contractions (e.g., "it's", "don't").
• Processing dynamic strings in SQL queries.

Additional Information

• JSON and Escaping: When dealing with JSON in PostgreSQL, escaping rules differ. Ensure to handle JSON strings appropriately by using json_build_object or ::jsonb casting.
• String Functions: PostgreSQL string functions like concat() or || (concatenation operator) can also be used for dynamic string construction.

All PostgreSQL Questions, Answers, and Code Snippets Collection.