How to Insert a Value that Contains an Apostrophe in PostgreSQL?

When working with databases, it’s common to encounter situations where values contain special characters like apostrophes. PostgreSQL, a powerful open-source database system, provides several ways to insert values that contain apostrophes safely. In this article, we’ll explore different approaches to handle this scenario and ensure data integrity.

How to Insert a Value That Contains an Apostrophe?

When inserting a value with an apostrophe for example (“It’s raining”) in PostgreSQL It interprets the apostrophe as the end of the string which leads to syntax errors. To overcome this, we can use different methods to handle apostrophes. Below are the approaches are as follows:

1. Using Double Apostrophes
2. Using Dollar-Quoted Strings
3. Using Escape Character

To understand How to insert a value that contains an apostrophe in PostgreSQL we need a table on which we will perform various operations and queries. Here we will consider a table called values which contains id and name as Columns. If you don’t know how to create a table and database then refer to this. After inserting some data into the table. The table looks like this:

values table

1. Using Double Apostrophe

Example: Let’s Insert a record into the “employee” table with ID 5 and name “Mr’s Aruna” also ensuring that the apostrophe in the name is escaped to avoid syntax errors.

insert into employee values(5, ' Mr''s Aruna ');

Output:

Inserted value containing apostrophe using double apostrophes

Explanation: In this example, we can see that the values with apostrophes can be easily inserted into the database using double apostrophes. Here the double apostrophes are used as escape sequence which escapes the apostrophe in the value.

2. Using Dollar Quoted Strings

Example: Let’s insert a record into the “employee” table with ID 6 and name “Mr’s Archana” using dollar-quoted strings, as we can saw the following query:

insert into employee values(6, $$Mr's Archana$$)

Output:

Inserted value containing apostrophe using Dollar Quoted String

Explanation: When we need to add a value containing more number of apostrophe symbols, it becomes difficult to write the query since each of those must be doubled. So, PostgreSQL provides another way called, Dollar Quoted Strings where we enclose the value within the dollar symbols.

3. Using ESCAPE Clause

Example: Let’s insert a record into the “employee” table with ID 6 and name “Mr’s Johnson“, as we can saw the following query.

insert into employee values(6 , E 'Mr \' s  Johnson')

Output:

Inserted value containing apostrophe using escape clause

Explanation: In this example, we used ESCAPE clause where the value which needs to be inserted is written inside E ‘ ‘ statement . So, in the escape clause we added an escape sequence( \ ) character to the apostrophes in between the value. Therefore, the escape sequence inside the escape clause escapes the apostrophe and the value is inserted.

Conclusion

Overall after reading whole article now you have clear understanding of how to insert values with apostrophes can be done using double apostrophes, escape characters or dollar-quoted strings in PostgreSQL. Each method provides a way to handle apostrophes and ensures the correct insertion of data containing them. Similarly these escape sequences are also used in many programming languages such as C , Python, Java etc.