Today I Learned

A Hashrocket project

Escaping String Literals With Dollar Quoting

String literals in PostgreSQL are defined by surrounding the content with the ' character. For string literals that contain the ' character, you may have seen it escaped with a preceding '.

> select 'Isn''t this nice?';
     ?column?
------------------
 Isn't this nice?

This is easy enough to do, but can be error prone and doesn’t work well if SQL is being programmatically generated. A great workaround is to escape string literals using what is called dollar quoting.

> select $$Isn't this even nicer?$$;
        ?column?
------------------------
 Isn't this even nicer?

Just wrap both ends in $$ instead of '.

source

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.