How to Escape a Single Quote in PostgreSQL
- Escape a Single Quote in PostgreSQL
- Escape a Single Quote Using Another Single Quote in PostgreSQL
- Escape a Single Quote Using a Backslash in PostgreSQL
- Escape a Single Quote by Dollar Quoting in PostgreSQL
This tutorial discusses how to escape a single quote in a PostgreSQL query.
Escape a Single Quote in PostgreSQL
Consider a comments table that keeps track of the users’ comments. The table has 5 fields: id, userid, postid, comments, commentdate, as shown here:
|id | userid | postid | comments | commentdate
|---|-------- |---------|--------------------------------|---------------------
|1 | 1 | 1 | The post is great | 07-02-2022 11:03:05
|2 | 2 | 1 | We've found the right post | 07-02-2022 01:17:02
|3 | 3 | 3 | I'm working on a related post | 08-02-2022 09:12:17
|4 | 4 | 3 | Excellent post | 08-02-2022 12:04:01
|5 | 5 | 4 | The post's title is impressive | 09-02-2022 16:23:09
We will create the table in the example above. Here’s the CREATE statement for the comments table:
CREATE TABLE comments
(
id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
userid INT NOT NULL,
postid INT NOT NULL,
comments TEXT NOT NULL,
commentdate TIMESTAMP NOT NULL,
CONSTRAINT comment_pkey PRIMARY KEY (id)
)
After creating the table, we will insert the values in the first row in the example above. Below is the INSERT statement for the first row:
INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (1, 1, 'The post is great', '07-02-2022 11:03:05');
This query inserts successfully.
Next, let’s insert the values in the second row. Below is the INSERT statement:
INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (2, 1, 'We've found the right post', '07-02-2022 01:17:02');
When we attempt to execute the statement above, a syntax error is thrown, as shown here:
ERROR: syntax error at or near "ve"
LINE 1: ... postid, comments, commentdate) VALUES (2, 1, 'We've found t...
PostgreSQL cannot make sense of the words after We as it assumes the single quote after We indicates the end of the string. Rows 3 and 5 will give a similar error as they all have single quotes in the comments field.
Below is the statement to insert all the rows in the example:
INSERT INTO comments (userid, postid, comments, commentdate)
VALUES
(1, 1, 'The post is great', '07-02-2022 11:03:05'),
(2, 1, 'We've found the right post', '07-02-2022 01:17:02'),
(3, 3, 'I'm working on a related post', '08-02-2022 09:12:17'),
(4, 3, 'Excellent post', '08-02-2022 12:04:01'),
(5, 4, 'The post's title is impressive', '09-02-2022 16:23:09');
The above statement will give the same error as the error while inserting only the second row.
A way to resolve this is to escape the single quote, and this can be accomplished with:
- another single quote
- a backslash
- dollar quoting
Escape a Single Quote Using Another Single Quote in PostgreSQL
A single quote can be specified in escaped form by writing a single quote followed by a single quote to be escaped. This solution is shown here:
INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (2, 1, 'We''ve found the right post', '07-02-2022 01:17:02');
The statement to escape all single quotes in the statement above is shown here:
INSERT INTO comments (userid, postid, comments, commentdate)
VALUES
(1, 1, 'The post is great', '07-02-2022 11:03:05'),
(2, 1, 'We''ve found the right post', '07-02-2022 01:17:02'),
(3, 3, 'I''m working on a related post', '08-02-2022 09:12:17'),
(4, 3, 'Excellent post', '08-02-2022 12:04:01'),
(5, 4, 'The post''s title is impressive', '09-02-2022 16:23:09');
Output:
|id | userid | postid | comments | commentdate
|---|-------- |---------|--------------------------------|---------------------
|1 | 1 | 1 | The post is great | 07-02-2022 11:03:05
|2 | 2 | 1 | We've found the right post | 07-02-2022 01:17:02
|3 | 3 | 3 | I'm working on a related post | 08-02-2022 09:12:17
|4 | 4 | 3 | Excellent post | 08-02-2022 12:04:01
|5 | 5 | 4 | The post's title is impressive | 09-02-2022 16:23:09
Escape a Single Quote Using a Backslash in PostgreSQL
To escape a single quote using a backslash, you have to place the E symbol before the string, which is a comment in our example, and place a backslash just before the single quote to be escaped, as shown here:
INSERT INTO comments (userid, postid, comments, commentdate)
VALUES
(1, 1, 'The post is great', '07-02-2022 11:03:05'),
(2, 1, E'We\'ve found the right post', '07-02-2022 01:17:02'),
(3, 3, E'I\'m working on a related post', '08-02-2022 09:12:17'),
(4, 3, 'Excellent post', '08-02-2022 12:04:01'),
(5, 4, E'The post\'s title is impressive', '09-02-2022 16:23:09');
Output:
|id | userid | postid | comments | commentdate
|---|-------- |---------|--------------------------------|---------------------
|1 | 1 | 1 | The post is great | 07-02-2022 11:03:05
|2 | 2 | 1 | We've found the right post | 07-02-2022 01:17:02
|3 | 3 | 3 | I'm working on a related post | 08-02-2022 09:12:17
|4 | 4 | 3 | Excellent post | 08-02-2022 12:04:01
|5 | 5 | 4 | The post's title is impressive | 09-02-2022 16:23:09
Escape a Single Quote by Dollar Quoting in PostgreSQL
If you want a more readable solution, especially when multiple single quotes are there, dollar-quoting can be used.
Dollar-quoting makes the solution readable if more single quotes are in the string. Dollar quoting uses a dollar sign, an optional tag, the string, in this case, the comment, followed by another dollar sign, the optional tag, and a closing dollar sign.
A single quote can be used in a dollar-quoted string without it being escaped. A row can be inserted using dollar-quoting like this:
INSERT INTO comments (userid, postid, comments, commentdate)
VALUES (6, 5, $$'I've shared the post. It's quite impressive'$$, '09-02-2022 16:34:17')
Here’s the official documentation to know more about PostgreSQL string constants and their escapes.