CODEWITHSUNDEEP
postgresql
zulqarnain
zulqarnain

Reputation: 1735

Postgresql escape dollar sign

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?

Upvotes: 7

Views: 11226

Answers (3)

Tomáš Záluský
Tomáš Záluský

Reputation: 12030

I found this question troubleshooting problem with executing query with double dollar in literal from within linux shell. For example select '$abc$' in psql gives correct result $abc$ while psql -U me -c "select '$abc$'" called from linux shell produces incorrect result $ (provided there's no system variable abc).

In that case, wrapping into another delimiter ($wrapper$$abc$$wrapper$) won't help since the primary problem is interpreting dollars in shell context. Possible solution is escaping dollars (psql -U me -c "select '\$abc\$'") however this produces backslashes literally when called in psql. To produce same query usable in both psql and linux shell, psql -U me -c "select concat(chr(36),'abc',chr(36))" is universal solution.

Upvotes: 4

Craig Ringer
Craig Ringer

Reputation: 324511

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.

Upvotes: 3

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

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$

Upvotes: 9

Related Questions