Escape a Single Quote in PostgreSQL

  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

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.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PostgreSQL String

  • PostgreSQL string_agg Function