How to Escape Single Quote in PostgreSQL

In this PostgreSQL tutorial, I will show how to escape a single quote in PostgreSQL.

Also, you will 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 need to insert the data into the database which contains the single quote within the string (Jame’s). In general, while inserting the data which contains a single quote PostgreSQL raises an error, if you haven’t followed the proper syntax of inserting a string with a single quote.

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 ($$).

Let’s take some examples and understand how to escape a single quote in PostgreSQL.

Use Double Single Quote to Escape Single Quote in PostgreSQL

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’ and ‘name’ and ‘country’.

You want to insert the data of new employee ‘Jame’s’ who belongs to country USA but 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');

When you executed the above command to insert the data, it shows 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 in places of the single quote with a name like ‘Jame”s’.

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

SELECT * FROM employees;

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

Read Also: How To Check Oracle Database Read Write Mode

Use Dollar Quoted String to Escape Single Quote in PostgreSQL

Another way to use a single quote is to wrap the value containing a single quote 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 name of the employee is wrapped using the double dollar character to keep the single quote within the name in the table. you can use more than one single quote within a string using the double dollar characters.

Escape Single Quote in PostgreSQL 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 where again you will insert the new employee record into the database using the below command.

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

SELECT * FROM employees;

The above command successfully inserted the employee data with the name ‘Chris’s’ 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 Query In PostgreSQL
• How to Find PostgreSQL DateDiff
• How to use Postgresql group_concat