How to Insert Text with Single Quotes in PostgreSQL

Inserting Text with Single Quotes in PostgreSQL

When inserting text that contains single quotes in PostgreSQL, special handling is required because single quotes are used to delimit strings. If you want to include single quotes within the text, you can escape them by doubling each single quote within the string. This approach is straightforward and prevents syntax errors.

Syntax for Inserting Text with Single Quotes

The syntax involves using two consecutive single quotes ('') to represent a single quote within a string.

INSERT INTO table_name (column_name)
VALUES ('It''s a beautiful day');

Explanation:

• '': Represents a single quote within a text string by using two single quotes in sequence.

Example Code:

Suppose you have a table called quotes with a quote_text column, and you want to insert the sentence "It's a beautiful day".

-- Insert text with single quotes into the quotes table
INSERT INTO quotes (quote_text)          -- Specify target table and column
VALUES ('It''s a beautiful day');         -- Insert text, escape single quote with double single quotes

Explanation:

• INSERT INTO quotes (quote_text): Specifies the quotes table and quote_text column for the insert.
• VALUES ('It''s a beautiful day');: Inserts the text It's a beautiful day into the column, with '' used to escape the single quote in It's.

Alternative: Using the E'' Notation for Escape Sequences:

You can also use the E'' escape string syntax if you prefer explicit escape sequences. In this format, a backslash (\) can be used to escape characters.

INSERT INTO quotes (quote_text)
VALUES (E'It\'s a beautiful day');

In this example:

E'It\'s a beautiful day': The E before the string enables backslash escape sequences, and \' represents a single quote.

Important Notes:

• Error Prevention: Escaping single quotes properly prevents syntax errors and ensures the text is inserted accurately.
• Consistency: Use the double single-quote ('') method for readability or the E'' method if you prefer escape sequences.
• Double Quotes: For column or table names with spaces or special characters, use double quotes ("column name"). However, single quotes are required for string literals in values.

All PostgreSQL Questions, Answers, and Code Snippets Collection.