0

In PostgreSQL, strings can be quoted very flexibly using a pair of dollar signs ($) with a tag in between. For example,

SELECT $f$ Hi $f$;

Is this quoting mechanism a part of any SQL standard, or is it postgres specific?

0

1 Answer 1

1

NO, its postgresql specific. Many programing languages offer a way to write text that avoids having to be escaped.

consider

Select $$";"'\e\r\n$$ 

vs

select '\";\"\'\\e\\r\\n'

Also keep in mind that since version 9.1 Postgtesql, by default, will no longer allow passing strings with escape literals(\) in a query. In order to do this, it has to have a leading E

select E'\r\n'  

to be escaped now see standard conforming string

4
  • consider vetting & sharing your code samples and expected output. I can't figure out what you're meaning to do with the 2nd example. perhaps start here and edit your answer with the updated link once it reflects the output you mean to show? Dec 11, 2019 at 14:40
  • the 2nd example is example is C style escaped string postgresql.org/docs/12/… with standard_conforming_string off
    – zsheep
    Dec 11, 2019 at 15:31
  • I'm getting the same syntax error both locally and on shareable code with that set option. Might just be me borking it, but in general providing a rigorous minimal reproducible example will improve an answer Dec 11, 2019 at 15:43
  • it should work not sure why not Because doing select E'\' \r\n' does work of course doing the other method to escape ' works yet it did not escape slashes correctly either it returned double slashes. wonder if this behavior changed at some point and i missed it
    – zsheep
    Dec 11, 2019 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.