When working with data, you will encounter such instances where you have to use single quotes to represent a string. For instance, you would want to insert a string value to show possession. How do you insert a string with single quotes without raising an error?
Postgres supports using single quotes when inserting a string value. However, using the same quote within a string won’t give the expected outcome. Suppose you are struggling with escaping single quotes in a string. This post presents different options that you can use. Read on!
How to Escape Single Quotes in String
Let’s create a table that we will use for our examples. The following image represents a simple table where we insert a few values into it:
Note how we are using the single quotes to insert the VARCHAR data type. PostgreSQL relies on single quotes to insert the string values. A single quote is added at the start and end of the string. However, if you try to use the single quotes within a string, it raises an error and the string won’t be inserted into your table.
For instance, we execute an “insert” command in the following example. We want to insert the “kim’s h” string into the table. Unfortunately, we can’t execute the command successfully since PostgreSQL uses the single quotes for insertion. It raises an error.
Here are the different solutions that you can use:
1. Using Double Single Quotes
PostgreSQL treats the double single quotes as your way of escaping a single quote. Thus, you won’t get an error when using the single quotes within a string. Using the same example that raised an error, we can escape the single quotes by doubling the single quotes like in the following command:
We can confirm that our insert command is executed successfully. If we list the table contents, we can see that we have a “kim’s h” sting entry to confirm that we managed to skip the single quotes within the string. That’s one way to go about it. The only catch is that you must double the single quotes within the string while retaining the single quote at the start and end.
2. Using the Backslash Method
Another option that you can use is the E\ method when you want to escape the single quotes. Here, you start the target string with an E. Then, you can comfortably escape the single quotes with a backslash. Postgres won’t throw an error. Instead, it inserts your string with the single quotes.
Here’s an example where we want to insert Tim’s in our table. Note how we escape the single quotes:
When we use the “select” command to check the table contents, we can see that we successfully inserted the string. The catch is to know where you insert the E and the backslash in your target string. Once you do, you can comfortably escape the single quotes in your PostgreSQL database.
The E-backslash method is not recommended, but it still gets the job done.
3. Using the Dollar-Quoted Strings
Suppose you want to use more than one single quote within a string. Let’s say you want to insert your string as “Nel’s’s h” without getting an error. It’s possible to use the double quotes as demonstrated in the following:
Alternatively, you can use the $$ characters to enclose the string. You must add $$ at the start and end of the string.
Here’s an example of using the dollar-quoted string to escape the single quotes:
If we list the table’s contents, we can see that our string is successfully inserted.
With these three methods, you can comfortably escape the single quotes in the string.
Conclusion
When you want to use the single quotes within a string, you must know how to escape the single quotes. You can double the single quotes, use the E backslash method, or use the dollar-quoted string method. This tutorial presents the three methods and examples of how to use them.
