SQL stands for structured query language and is used to query databases for analytical needs. While using arithmetic queries, some results require strings to help them understand better. Strings can be formed by enclosing text in quotes. However in a case when quotes are themselves required in a string, then we need to escape them. In this article, we are going to see how we can escape a single quote in PostgreSQL.

Single Quote in PostgreSQL

Single quote (‘) is a special character used to denote the beginning and end of a string literal. It is used to represent text values in SQL queries and allows developers to work with alphanumeric data. The single quote serves as a delimiter for allowsvalues. When a sequence of characters is enclosed within single quotes, PostgreSQL interprets it as a string literal. When a string contains special characters or reserved keywords, using single quotes helps PostgreSQL understand that the content between the quotes is a literal string and not a part of the SQL syntax.

Setting Up Environment

Let us start by creating a table and insert some values in it. Suppose we have the following customer table. The following query creates the customer table and inserts a few records in it.

-- Create the customer table
CREATE TABLE customer (
    id INT,
    name VARCHAR(20)
);

-- Insert sample values into the customer table
INSERT INTO customer VALUES
    ('1', 'John Doe'),
    ('2', 'Jane Smith'),
    ('3', 'Bob Johnson');

The following is the initial data in the table:

Initial data

Using Quotes in PostgreSQL

In PostgreSQL, quotes are used to denote strings. For instance, the following query returns a string:

Query:

--Example: Using quotes to create a string
SELECT ‘String value’

Output:

String value output

Explanation: When using quotes, it’s important to note that column names should not be enclosed in quotes, as it results in a constant value in each row:

Quotes select the text in the form of a string. We cannot enclose column names in quotes as it will result in a string with that value instead. See the following example.

Query:

-- Display the contents of the customer table with a constant string
SELECT Name, 'Name' FROM customer;

Output:

query output

Explanation: As we can see the column name when enclosed just returns a constant value in each row.

How to Escape Single Quotes in PostgreSQL

Sometimes we might need to put quotes inside the text. We can do it in the following two ways.

Method 1: Use CHR() Function

We can make use of CHR() function to escape single quote. Single quote is represented by CHR(39) in PostgreSQL.

Query:

SELECT chr(39) || 'Name' || chr(39) FROM customer;

Output:

Using chr() Function

Explanation: Thus making use of chr() function helps to escape single quotes.

Method 2: Write Them Twice Consecutively

In some cases, when you cannot use double quotes, you can escape single quotes inside single quotes by writing them along with each other. Every single quote escapes the next one (except the border ones).

Query:

SELECT '''Name' FROM customer;

Output:

query output

Explanation: As you can see, we enclosed single quotes inside single quotes by writing them consecutively.

Inserting Text with Single Quote in PostgreSQL

Let us start by creating a table which contains information of the employees. The following query creates the table:

CREATE TABLE description (
    info VARCHAR(50)
);

At present the table is empty. Let us now insert some records in the table. We will deliberately insert records in the table which has single quotes. We are going to make use of both the methods that we went through previously in this article to escape the single quote while inserting the data. The following query inserts three records in the table:

INSERT INTO description VALUES
    ('John Doe''s id is 1'),
    ('Jane Smith' || chr(39) || 's id is 2'),
    ('Bob Johnson''s id is 3');

As you can see we used the second method while inserting the first and the third records while used the first method to insert the second record in the table. The following is the data in the table after executing the above query.

Table data

Conclusion

After reading whole article now you have good understanding of how to escape single quote and insert them in PostgreSQL. We saw two methods to escape single quotes, the first using CHR() function and then later writing them twice consecutively with their examples. Now you can easily insert data with single quotes to tables in PostgreSQL.