Postgresql escape single quote

In this tutorial, we will learn about “Postgresql escape single quote” and cover the following topics.

• Postgresql escape single quote in where clause
• Insert single quote in postgresql
• Postgres replace singlequote with two single quotes
• Postgres single quote in string
• Postgres remove single quotes from string
• Replace singlequote in postgresql
• Postgresql single quote vs double quote
• Postgresql regex single quote
• Postgresql search single quote
• Postgresql copy single quote

Before beginning, let’ create an empty table and we will use this table throughout this tutorial.

Create a table named single_quote.

CREATE TABLE single_quote(name varchar,job varchar);

Insert single quote in postgresql

In Postgresql, we can insert a single quote using the double single quote (”) or (E’\’) to declare Posix escape string syntax.

Let’s understand through an example by inserting data into an empty table that we created above.

INSERT INTO single_quote
VALUES 
(E'O\'Brien','SDE')
,(E'Jhon\'s','Dot Net Developer')
,('Dan''s', 'Database Developer'),
('Christopher''s', 'Data Scientist');

From the above output, Four names have been inserted into table single_quote using the double single quote (”) and (E’\’). This is how we insert data with a single quote in Postgresql.

Read: PostgreSQL Date Difference

Postgresql escape single quote in where clause

In Postgresql, a single quote can be used WHERE clause.

Let’s find the job role of the person Dan’s using the where clause.

SELECT job FROM single_quote WHERE name = 'Dan''s';

In the above code, we are finding the job of a person name Dan’s using the WHERE clause.

The single quote in name Dan’s represented using the double single quote (”) like name = ‘Dan”s’ after the where clause.

The output of the above code is given below.

From the above output, Dan’s do the job of Database developer.

Read: Create a stored procedure in PostgreSQL

Postgresql search single quote

In Postgresql, a single quote can be used with the WHERE clause for searching, actually we can search the name of the person with a single quote using the where clause.

Use the code below to search names with a single quote.

SELECT * FROM single_quote WHERE name LIKE E'%\'s%;

From the above output, we have searched the name of the person who has a single quote in his name, using the WHERE clause with LIKE that helps in matching the pattern against the text value.

For pattern, we have used the E’%\’s%; that will match with any with a single quote.

Read: PostgreSQL list databases

Postgres replace singlequote with two single quotes

In Postgresql, REPLACE function can be used to replace the single quote with two single quotes in the string.

Syntax:

REPLACE(source, old_data, new_data );

Where the source is string or data where we want to replace.

old_data: It is the text that we want to search and replace.

new_data: It is the text that will replace the old_data.

Let’s replace the name Dan’s with two single quotes in the above table named single_quote.

UPDATE 
   single_quote
SET 
   name = REPLACE(name,'Dan''s',E'DAN\'''s')
WHERE 
   name ='Dan''s';

From the above code, we have used the E’\”’ within REPLACE function to represent the quotes with the name Dan’s as new_data. After the backslash (\) in the replace function three single quotes are used to represent the two single quotes like E’DAN\”’s’.

The output of the above code is given below.

In the above output, we have updated Dan’s name from a single quote to two single quotes Dan”s.

Read: PostgreSQL Data Types

Postgres remove single quotes from string

In Postgresql, to remove the single quote, we will use the above sub-section code with little change to old and new data of REPLACE function.

Let’ remove the single quote of the name Dan’s in the table single_quote.

UPDATE 
   single_quote
SET 
   name = REPLACE(name, '''','')
WHERE 
   name ='Dan''s';

Let’s remove the single quote from a string named United Kindom’s, Note in Postgresql single quote is represented in the string Using the double single quote like ‘United Kindom”s’.

SELECT REPLACE('United Kingdom''s','''','');

The code result shows the removed single quote from a string United Kindom’s as United Kindom.

Read: PostgreSQL Date Add

Postgres single quote in string

In Postgresql, a single quote can be used in the string.

Let’s use the double single quote (”) to escape a string with a single quote.

SELECT 'Jhon''s' as string_with_single_quote;

From the above code, we are escaping a single quote using the double single quote (”) between the string Jhon and s like Jhon”s.

The output of the above code is given below.

Read: Postgresql date between two dates

Replace singlequote in postgresql

In Postgresql, to replace the single quote, we will use the above sub-section code with little change to old and new data of REPLACE function.

Let’ replace the single quote of the name O’Brien in the table single_quote with & symbol.

UPDATE 
   single_quote
SET 
   name = REPLACE(name, '''','&')
WHERE 
   name ='O''Brien';
   
SELECT * FROM single_quote;

In the above code, we replace the single quote with the (&), wherein REPLACE function four single quotes (””) represent the single quote.

From the above output, O’Brien changed to O&Brien using the REPLACE function.

Read: How to create a view in PostgreSQL

Postgresql regex single quote

In Postgresql, the regex_replace function can be used for providing a single quote to string, REGEXP_REPLACE() function replaces substrings that match a POSIX regular expression by a new substring.

Syntax:

REGEXP_REPLACE(source, pattern, replacement_data)

Where,

• source: It is a string on which replacement will happen.
• pattern: It is a POSIX regular expression for matching substrings that need to replace.
• replacement_data: It is a string that replaces the substrings which match the regular expression pattern.

Let’s insert a new record in the table single_quote without a single quote.

INSERT INTO single_quote(name,job)VALUES('Charles','Data Eneineer');
SELECT * FROM single_quote;

From the above output, we have inserted a new record of Charles and whose name does not contain any single quote.

Let’s add a single quote Charles using the regex_replace function.

UPDATE 
   single_quote
SET 
   name = REGEXP_REPLACE('Charles','es','e''s')
WHERE 
   name ='Charles';
   
SELECT * FROM single_quote;

In the above code, we are replacing the last two characters (es from Charles) with a single quote using the regexp_repalce function. We have proved source as Charles, pattern as es, and replacement_string as e”s.

In the above output, the name of the person Charles changed to Charle’s using the regexp_replace function.

Read: Postgresql create user with password

Postgresql single quote vs double quote

In Postgresql, the single quote and the double quote are used for different purposes.

Single Quote	Double Quote
Single quotes are used to represent that a token is a string.	Double quotes are always used to represent the delimited identifiers
It looks like Alan’s (‘).	Double quotes look like “I am red” (“”).
The single quote is for string constants.	Double quotes are the names of tables or fields.
In Postgresql single quote can be inserted or used anywhere using the double single quote (”) and E’\’

Postgresql copy single quote

In Postgresql, we can also copy a string with a single quote using the COPY command.

Open the text editor like Notepad on Windows or nano on Linux and write the following line.

Save the file as employee_data.csv

'Matthe''w',Content Writer

Let’ insert the data using the CSV file that contains a name with a single quote.

COPY single_quote FROM 'D:\employee_data.csv' DELIMITERS ',' CSV QUOTE '''';

SELECT * FROM single_quote;

In the above code, we are copying data from the employee_data.csv file that exists at location ‘D:\employee_data.csv’ in our computer, Where DELIMITERS tells that the data is separated using comma (,) and QUOTE tells the data contains quote using the double single quote in the employee_data.csv file.

The output of the above code is given below.

In the above output, we have copied the data from the CSV file that contains a single quote in the name Matthe’w.

Also, take a look at some more tutorials on PostgreSQL.

• How to create a table in PostgreSQL
• How to connect to PostgreSQL database
• How to create database in PostgreSQL
• Postgresql replace + Examples
• Postgresql Average + Examples
• Postgresql unique constraint
• How to Uninstall PostgreSQL

So, in this tutorial, we have learned about “Postgresql escape single quote” and covered the following topics.

• Postgresql escape single quote in where clause
• Insert single quote in postgresql
• Postgres replace singlequote with two single quotes
• Postgres single quote in string
• Postgres remove single quotes from string
• Replace singlequote in postgresql
• Postgresql single quote vs double quote
• Postgresql regex single quote
• Postgresql search single quote
• Postgresql copy single quote