PostgreSQL

Postgres Escape Single Quote

Almost all of us are pretty aware of single quotes, double quotes, and apostrophes used in the English subject whenever we write any document, essay, story, or academic thing. The Postgres database also uses single quotes and double quotes when inserting records in the database. But whenever we use single quotes within the string, it may give us an error. To resolve it, we use many different ways to escape single quotes. Let’s start with PostgreSQL.

Example 01:

We put data within the PostgreSQL database using the single quote around a text or string. Let’s see an example to do so. For that, you must have some string data in your database tables. So, open the query tool of your particular database by clicking on the query tool icon. We use our table “Ftest” from the “aqsayasin” database. We use the “Select” instruction on the query tool to fetch all the records from the table “Ftest” via the “*” character. The 7 records data displayed on our output area for pgAdmin 4:

Open another query tool or update the already opened one to add records in the table “Ftest”. For this purpose, we have to utilize the INSERT INTO command to add a single record within the table. We have been using ‘France’ in single quotes to add records. The record has been inserted successfully after executing this instruction on the query tool via the “run” icon:

Now, fetch the table “Ftest” records repeatedly using the SELECT instruction to see the change. The record 8 has been inserted successfully using the single quotes:

Example 02: Doubling a Single Quote

The first example was about using the single quote around the string value to add a record in a specific column of a table. But what about using a single quote somewhere between the string value? To see that, we need to glance at another Insert query. So, we have been using this insert query to add the 9th record within the table “Ftest”. We have been using the apostrophe or single quote within the string value, i.e., “France’s Car”. All the values have been inserted along with it. After executing this INSERT instruction with the “run” button, we have an error, i.e., “syntax error at or near “s”. This error is completely showing that PostgreSQL will not allow us to use the single quote or apostrophe in our string value to insert the record:

To avoid this error, we need to double the single quote by adding another single quote adjacent to it. So, we have been using the double quotes within the string value of the second column, i.e., “France’’s Car”, as shown in the below instruction. After running this instruction command, we have got the success message illustrating that the record has been successfully added to the second column “Country” of table “Ftest”:

Let’s quickly look at the table to see how the update has appeared within it. So, we have been using the SELECT instruction to get all the single row data from the table “Ftest” using the WHERE condition. This WHERE class has been specifying the ID = 9 to get only a single row record that we have just added. On execution of this instruction, we have a value with a single quote in between without any issue, i.e., “France’s Car”, which we didn’t get before:

Example 03: Using $$ Character

This was all about using a single “single quote” within the string to add the value. But what about using more than one single quote within the string value to put a record in the database? So, we have been using the INSERT into command in the query tool to add three records in the table “Ftest”. The second record is of “string” type. It has been using the single quote, i.e., apostrophe, within this string more than once, i.e., “France’s’s’snew’Car”. After running this command, we have got a syntax error as presented:

Let’s remove this error and add the string value in the table having more than one single quote within it using the INSERT INTO query in the query tool of the database. To do that, we need to put the double “$” character at the start and end of the string value, i.e., “$$ ‘France’s’s’s’new’Car’$$. So, we have executed the following INSERT INTO command in the query tool with the “run” icon. The command got executed perfectly, and the record has been added to the table “Ftest”, as shown in the output below:

Now, we have displayed the record running the SELECT instruction in the query area. Within the “Country” column, the value with many single quotes has been displayed:

Example 04: Using “Triple” Single Quotes

Let’s say you want to put single quotes around the string value to be displayed. And to achieve this goal, you set the single quotes around a string within the INSERT query as shown below. We put two single quotes on one side and two on another side so that system can take it as a string and also take single quotes as value. But, running this query will lead us to a syntax error, as shown:

To resolve this issue, we need to change our insertion method a little. We need to add three single quotes around the string. The most outer one will be used to take the value as a string. While the other two will be used to put a single quote around the string value, as shown below:

After using the SELECT instruction, we have the string value with single quotes as shown below:

Example 05: Using “E\” Method

Most of the time, we heard that we could escape single quotes without any error using the backslash before the single quote. We have tried this method within our INSERT command to add the string value with the apostrophe and backslash before a single quote. The following image shows the use of this method for the 2nd value for this insertion command. After the execution of this command in the query tool, we have received the syntax error as shown below:

So, to use a backslash before the single quote and remove this error from the output area, we need to use the character “E” at the start of the string value and its single quotes around. This method was quite perfect as the success message showed that the record was inserted:

Using the SELECT instruction to get the particular string value row, you will see that the string has been added with a single quote:

Conclusion:

This is how you can escape single quotes with special characters and use them as a value in a string record. We have discussed different ways to use different special characters to consider single quotes as string values. We have used these characters to add single quotes outside the string and within the string. We hope you found this article helpful. Check out other Linux Hint articles for more tips and information.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.