Postgresql escape dollar sign

Question

I've very complex data that I'm inserting into postgresql and am using double dollar ($$) to escape. However I've one row which ends with dollar sign and is causing error. The original row is like 'dd^d\w=dd$' and when escaped '$$dd^d\w=dd$$$'.

How can I escape this specific row?

Answer 1

Use any string inside the double dollar to differentiate it:

select $anything$abc$$anything$;
 ?column? 
----------
 abc$

The insert is similar:

insert into t (a, b) values
($anything$abc$$anything$, $xyz$abc$$xyz$);
INSERT 0 1
select * from t;
  a   |  b   
------+------
 abc$ | abc$

Answer 2

While Clodoaldo is quite right I think there's another aspect of this you need to look at:

Why are you doing the quoting yourself? You should generally be using parameterised ("prepared") statements via your programming language's client driver. See bobby tables for some examples in common languages. Using parameterised statements means you don't have to care about doing any quoting at all anymore, it's taken care of for you by the database client driver.

I'd give you an example but you haven't mentioned your client language/tools.