JIYIK CN >

This article discusses how to escape single quotes in PostgreSQL queries.

Escaping single quotes in PostgreSQL

Consider a comment table that records user comments. The table has 5 fields: id, userid, postid, comments, commentdate, as shown below:

|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 tables in the above example. Here is CREATEthe 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 values ​​in the first row of the above example. Here is INSERTthe 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 values ​​into the second row. Here is INSERTthe 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 try to execute the above statement, it will throw a syntax error as shown below:

ERROR:  syntax error at or near "ve"
LINE 1: ... postid, comments, commentdate) VALUES (2, 1, 'We've found t...

PostgreSQL cannot understand Wethe word after , because it assumes that Wethe single quote after indicates the end of the string. Lines 3 and 5 will give similar errors because they both commentshave single quotes in the field.

Here is a 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 when only the second row is inserted.

One way to fix this is to escape the single quotes, which can be done with:

1. Another single quote
2. Backslash
3. Dollar quotation marks

Escape a single quote with another single quote in PostgreSQL

A single quote can be specified in escaped form by writing a single quote followed by the 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 following statement escapes all single quotes in the above statement:

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

Using backslash to escape single quotes in PostgreSQL

To escape a single quote with a backslash, you have to put the ' symbol before the string E, which is the comment in our example, and put a backslash before the single quote you want to escape, like this:

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

Escaping single quotes via dollar quotes in PostgreSQL

If you want a more readable solution, especially when multiple single quotes are present, you can use dollar quotes.

Dollar quoting can make the solution readable if there are more single quotes in the string. Dollar quoting uses a dollar sign, an optional marker, the string (in this case, a comment), then another dollar sign, the optional marker, and a closing dollar sign.

Single quotes can be used within a dollar-quoted string without being escaped. You can insert a line using dollar quoting as follows:

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 is the official documentation to learn more about PostgreSQL string constants and their escape.