Store string with special characters like quotes or backslash in postgresql table

Question

I have a string with value

'MAX DATE QUERY: SELECT iso_timestamp(MAX(time_stamp)) AS MAXTIME FROM observation WHERE offering_id = 'HOBART''

But on inserting into postgresql table i am getting error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "HOBART".

This is probably because my string contains single quotes. I don't know my string value. Every time it keeps changing and may contain special characters like \ or something since I am reading from a file and saving into postgres database.

Please give a general solution to escape such characters.

Answer 1

As per the SQL standard, quotes are delimited by doubling them, ie:

insert into table (column) values ('I''m OK')

If you replace every single quote in your text with two single quotes, it will work.

Normally, a backslash escapes the following character, but literal backslashes are similarly escaped by using two backslashes"

insert into table (column) values ('Look in C:\\Temp')

Answer 2

You can use double dollar quotation to escape the special characters in your string. The above query as mentioned insert into table (column) values ('I'm OK')

changes to insert into table (column) values ($$I'm OK$$).

To make the identifier unique so that it doesn't mix with the values, you can add any characters between 2 dollars such as
insert into table (column) values ($aesc6$I'm OK$aesc6$).

here $aesc6$ is the unique string identifier so that even if $$ is part of the value, it will be treated as a value and not a identifier.