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;





No comments:

Post a Comment

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

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...