How to Use Dollar-Quoted String Constants

Dollar-quoted string constants are used to make the string constants more readable and understandable. We usually need to have a single quote in the string constants but in order to write them we will have to double them up. This makes the queries a lot more messy. To avoid this mess, we do dollar quoting.

This write-up helps us understand the use of dollar-quoted string constants in PostgreSQL.

How to Use Dollar-Quoted String Constants Postgres?

Sometimes we need to write string constants in PostgreSQL. We write these string constants like this:

SELECT 'Hello I am Peter';

The above query returns a string constant like this:

These string constants may include single quotes. For example; if we need to write: “Hello, I’m Peter”. Then the query will be written as:

SELECT 'Hello, I''m Peter';

The below snippet shows the resultant output for the given query:

In the above query, it is clear that if we need to insert a single quote in the text, we will have to double it up. Well, this was a simple case. The problem arises when a string contains many single quotes. Under this scenario, doubling the quotes makes it very difficult to read as well as maintain the query.

Dollar-Quoted String Constants

In PostgreSQL version 8.0 and further, dollar-quoting was introduced to make string constants more understandable and readable.

The above example will become:

SELECT $$Hello, I'm Peter$$;

We can clearly see that the string constant becomes more readable. Output for the above query is:

Alternatively, we can write the generic query as:

SELECT $tag$Hello, I'am Peter$tag$;

The output for this is the same as above.

Remember the tag in the above query is completely optional, it can contain zero or many characters like;

SELECT $myText$Hello, I'am Peter$myText$;

This will again result in the same output.

Uses of Dollar-Quoted String Constants

In Postgres, dollar-quoted string constants have various practical use cases and benefits:

String Constants More Readable

This technique reduces the use of doubling the single quotes wherever we need the single quote in string constants. This makes them more clear and more readable.

SELECT '''USA'', ''CANADA'', ''CHINA'', ''ENGLAND'', ''RUSSIA''';

As in the above example, it is tiring to add these quotes again and again, and also needs to add another task to insert a correct count of quotes to make the code error-free. In short, the code reduces clarity and seems unreadable.

Easy to Define Postgres Functions

A body of Postgres function is usually defined in single quotes like;

DO
 'declare
  student_count integer;
   begin
  select count(*) into student_count
  from Students;
  raise notice ''The number of Students: %'', student_count;
 End;';

This double-dollar quoting notation is easier to work with, especially in the case when quotes are used in functions. The program code becomes more readable and clear. Like in the above example there is a line:

raise notice ''The number of Students: %'', student_count;

Now if we use double dollar quote representation the code becomes:

do
   $$
 declare
  student_count integer;
 begin
  select count(*) into student_count
  from Students;
  raise notice 'The number of Students: %', student_count;
 end;
   $$

Conclusion

To insert a single quote in the text, we will have to double it up. This makes the text very complex and unreadable in case the string contains many single quotes. So the dollar-quoting came to the rescue. Dollar-quoting was introduced to make string constants more understandable, readable, and easy to manage.

How to Use Dollar-Quoted String Constants