0

Suppose I have an employees table in Postgres DB where I have to insert a value for an employee name which is john's

Since it's Postgres I will escape the single quote ' by doubling them up -> ''

So john's will become john''s

Now when I select that particular row/instance using select query I have to double the quote again. So to select the value john''s I have to write 'john''''s' and my query becomes -

select * from employees where name = 'john''''s'

Is this the best approach? or Is there any alternative to this process of data insertion and selection for these particular type of data (contains quote)? Any suggestion ?

ahmedshahriar
  • 1,053
  • 7
  • 25
  • 2
    "*Since it's Postgres I will escape the single quote*" - that has nothing to do with "being Postgres". This is what the SQL standard requires. –  Apr 08 '21 at 13:32

1 Answers1

1

No you don't have to double the escaped quotes:

select * 
from employees 
where name = 'john''s'
  • Thank you. But It doesn't work on my Database. I have to use the above-mentioned method. I'm using 'PostgreSQL 13.0'. – ahmedshahriar Apr 08 '21 at 13:43
  • 1
    @ahmedshahriar: this will most definitely work: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=399f7b01d8b5b9fe699f2c25f8956d87 if it doesn't for you, then there is something you are not telling us –  Apr 08 '21 at 13:45
  • Thnx. You're correct. Actually, I'm using psycopg2 and mogrify function to perform bulk insert from JSON and the column type is "character varying". So my guess is whenever I convert the JSON into bytestring to insert data using mogrify (after adding single escape quote) it again adds an extra quote. That's why I have to use double quotes in my query. – ahmedshahriar Apr 08 '21 at 14:05