How to Escape Single Quote in PostgreSQL

In this PostgreSQL tutorial, I will show you how to escape a single quote in PostgreSQL. You will also understand the three different methods, such as double quotes, dollar quoted string, and backslash, to escape the single quote.

How to Escape Single Quote in PostgreSQL

Sometimes, you must insert data into the database containing a single quote within the string (Jame’s). In general, if you haven’t followed the proper syntax for inserting a string with a single quote, PostgreSQL will raise an error.

So, to escape the single quote in PostgreSQL while inserting, you need to know about some of the methods that allow you to insert the value with a single quote.

There are different ways to escape the single quote, which are double single quotes (”), backslash (E\), and the dollar quoted string ($$).

To Escape Single quotes in PostgreSQL, follow the approaches below.

Approach-1: Use Double Single Quote

You can use the double single quote (”) to represent the single quote within a string and insert it into the database without any error. In other words, this double single quote (”) is equivalent to a single (‘) in PostgreSQL.

Let’s say a table called ’employee’ with columns ‘id’, ‘name’, and ‘country’.

You want to insert the data of the new employee ‘Jame’s’, who belongs to the USA. However, remember that the name contains a single quote before the letter s. So, use the below command to insert the data.

INSERT INTO employees (name, country) VALUES 
('Jame's', 'USA');

After executing the above query, I got the expected output as shown in the screenshot below.

When you executed the above command to insert the data, it showed an error (ERROR: syntax error at or near “s” LINE 2: (‘Jame’s’, ‘USA’);). This means it is not the correct way to insert the record in the database, and this error arises due to a single quote before the letter s in the name ‘Jame’s’.

To insert the above data with a single quote, use the double single quote instead of the single quote with a name like ‘Jame”s’.

INSERT INTO employees (name, country) VALUES 
('Jame''s', 'USA');

SELECT * FROM employees;

After executing the above query, I got the expected output as shown in the screenshot below.

Look at the above output. The new record of employee ‘Jame’s’ is inserted successfully and contains a single quote in the employee’s name.

Read Also: How To Check Oracle Database Read-Write Mode

Approach-2: Use Dollar Quoted String

Another way to use a single quote is to wrap the value containing it using the double dollar character. To put it another way, use the double dollar character at the beginning and end of the string value containing the single quote.

Let’s take an example and insert the record of another employee into the table employees.

INSERT INTO employees (name, country) VALUES 
($$Jonathan's$$, 'Canada');

SELECT * FROM employees;

If you look at the query part ($$Jonathan’s$$, ‘Canada’), the employee’s name is wrapped using the double dollar character to keep the single quote within the name in the table. Using the double dollar characters, you can use more than one single quote within a string.

Approach-3: Using E\

The backslash (E\) in PostgreSQL allows you to escape the single quote within the string value while inserting the data into the table.

So, how to use this (E\) to escape a single quote, all you have to do is to specify the E at the beginning of the string, then within the string use backslash (\) before the place where you want to put the single quote.

Suppose you have the word ‘PostgreSQL’ and you want to put the single quote before the letter L, then you write like this (E’PostgreSQ\’L’).

Let’s check with an example: You will insert the new employee record into the database again using the command below.

INSERT INTO employees (name, country) VALUES 
(E'Chris\'s', 'Australia');

SELECT * FROM employees;

After executing the above query, I got the expected output as shown in the screenshot below.

The above command successfully inserted the employee data named ‘Chris’, containing the single quote using the (E\). This is how to escape the single quote using the backslash (E\) method.

Conclusion

In this PostgreSQL tutorial, you learned how to escape single quote in PostgreSQL using the dollar-quoted string, double single quotes, and backslash (E|) methods.

You may also read:

• How to Update a Query In PostgreSQL
• How to Find PostgreSQL DateDiff
• How to use PostgreSQL group_concat