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.

       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');

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;

No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...