insert text with single quotes in postgresql

How to Insert Text With Single Quotes in PostgreSQL

PostgreSQL is a popular database used by many websites and applications. Sometimes you may need to enter strings and texts with single quotes in your database column. However, if you directly use strings and texts with single quotes in your INSERT statements, you will get an error. In this article, we will learn how to insert text with single quotes in PostgreSQL.


How to Insert Text With Single Quotes in PostgreSQL

Let us say you have the following PostgreSQL table.

postgres=# create table students(id int, name text);

If you try to run the following statement you will get an error.

postgres=# insert into students(1, 'Mc'd');

This is because there is a single quote in our string Mc’d and we are also using single quote to wrap our text.

In such cases, you can escape the single quote in our string, with another single quote, as shown below.

postgres=# insert into students(1, 'Mc''d');
postgres=# select * from students;
 id | name
----+------
  1 | Mc'd

If you use an older version of PostgreSQL with standard_conforming_strings = off or prepend your strings with E to indicate Posix escape string syntax, then you can escape the string with backslash.

postgres=# insert into students(1, E'Mc\'d');

Alternatively, you can also wrap your string with single quotes, within double dollar signs as shown below. This method is also known as dollar quoting.

postgres=# insert into students(1, $$Mc'd$$);

In this article, we have learnt how to insert text with single quotes in PostgreSQL. You can use these methods to escape single quotes in strings or texts entered into PostgreSQL database.

Also read:

How to Get Element’s Outer HTML Using jQuery
How to Preview Image Before It is Uploaded Using jQuery
How to Get Image Size & Width Using JavaScript
How to Use Multiple jQuery Versions on Same Page
How to Convert Form Data to JS Object Using jQuery

Leave a Reply

Your email address will not be published.