POSTGRESQL Dollar Quotes in Where Clause

Question

For people who tried or needed a solution to escape every special character (even $) in a WHERE CLAUSE in POSTGRESQL, here is how it should be use

the documentation can be somehow hard to understand, and there is no proper example of it so here is mine

e.g : if you want to make a request looking as

SELECT
    *
FROM
    <TableName> 
WHERE
    <ColumnName> = 'string with ' character';

it will throw an error cause "character'" is outside the string

Answer 1

So here is how it should be written:

SELECT
    *
FROM
    <TableName> 
WHERE
    <ColumnName> = $$string with ' character$$;

The WHERE CONDITION will take the string literally; the interface may look broken but the following instruction will still be interpreted as expected.

SELECT
    *
FROM
    <TableName> 
WHERE
    <ColumnName> = $$string with ' character$$ AND <OtherColumnName> IS NOT NULL;

This could even be another escaped string with $$.

For details about dollar quoting, look at the documentation.