How to Escape a Single Quote in PostgreSQL

Joy Idialu Feb 02, 2024
  1. Escape a Single Quote in PostgreSQL
  2. Escape a Single Quote Using Another Single Quote in PostgreSQL
  3. Escape a Single Quote Using a Backslash in PostgreSQL
  4. Escape a Single Quote by Dollar Quoting in PostgreSQL
How to Escape a Single Quote 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:

  1. another single quote
  2. a backslash
  3. 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.

Related Article - PostgreSQL String