Postgres Dollar Quoting

Postgre has a feature called Dollar-Quoting. This feature is used to include any text without escaping single quotes ( ‘ )

Please see the below example

SELECT 'Andrew''s Pen';  RESULT -> Andrew's pen

In above example you might have noticed that in order to add a single quote into a text string, there should be an escape quote as well. By using dollar quoting feature we can exclude additional quotes

SELECT $$Andrew’s Pen$$;  RESULT -> Andrew's pen

What if there are dollar signs in the text string

SELECT $$Andrew’s Millions Dollar Pen $$$$$$
Expected Value : Andrew’s Millions Dollar Pen $$$$
Actual Value: error

You can solve this by using dollar quotes with a token

SELECT $myCustomToken$Andrew's Millions Dollar Pen $$$$$myCustomToken$
Expected Value : Andrew’s Millions Dollar Pen $$$$
Actual Value: Andrew’s Millions Dollar Pen $$$$

In this case, expected value and actual value are same. Here we have used a custom token surrounded by two dollar marks($myCustomToken$). Now you can add as much as dollar quotes inside.

Double Quoting Usages

1. Avoid double quoting hell

SELECT '''a'', ''b'', ''c'', ''d'', ''e'', ''f''';

2. Easy to define postgres function

create or replace function custom_add (i int, j int) returns integer as '
 begin
  return i + j;
 end;
' language plpgsql;
select custom_add(10,100); -> result 110

Postgres function body is defined inside single quotes. It is easier to work with dollar quoting for functions specially when quotes are used inside functions.

create or replace function custom_add (i int, j int) returns integer as $$
 begin
  return i + j;
 end;