Insert text with single quotes in PostgreSQL

I faced an issue when tried to insert apostrophe word into the table, seeing the issue I become panic, how would I resolve this issue, so I did google surfing and found a lot of solutions, and I want to share it with you.

To create the scenario I will create a table to execute the following PostgreSQL statement.

CREATE TABLE books (        id int generated always as identity,        title text );

Now, I will try to insert the single quote value into the Title column, let's see what output comes.

INSERT INTO books(title) VALUES('No Man's Land');

Output:

ERROR:  syntax error at or near "s"

LINE 3: INSERT INTO books (title) VALUES ('No Man's Land');

Some queries are given below please try it.

Replace a single quote (apostrophe) with double-quotes.

INSERT INTO books(title) VALUES('No Man''s Land'); --select query on books SELECT title FROM books LIMIT 1;

Propend your string with E to declare Posix escape string syntax, and you can also escape with the backslash \

INSERT INTO books (title) VALUES (E'No Man\'s Land'); --select query on books SELECT title FROM books LIMIT 1;

If we are using \ to resolve the issue, it may create an issue if we have to insert \ into the table. The following query may help you.

To further, avoid confusion among dollar-quotes, add a unique token to each pair.

INSERT INTO books (title) VALUES ($$No Man's Land$$); --select query on books SELECT title FROM books LIMIT 1;