How Do I Remove Single Quotes From A Table In Postgresql?

I searched around quite a bit, it would be great if someone could link me to a solution or answer my query. The thing is I have a postgresql table that contains a lot of single quo

Solution 1:

Better use replace() for this:

UPDATE tbl SET col = replace(col, '''', '');

Much faster than regexp_replace() and it replaces "globally" - all occurrences of the search string. The previously accepted answer by @beny23 was wrong in this respect. It replaced first occurrences only, would have to be:

UPDATE tbl SET col = regexp_replace(col, '''', '', 'g');

Note the additional parameter 'g' for "globally". Read about string functions in the manual.

Aside: the canonical (and SQL standard) way to escape single quotes (') in string literals is to double them (''). Using Posix style escape sequences works, too, of course. Details:

• Insert text with single quotes in PostgreSQL

Solution 2:

update tablename set fieldname=NULLwhere fieldname='''' ;

or

update tablename set fieldname=NULLwhere fieldname=E'\'' ;

Solution 3:

insert into table1(data) values ($$it's a string, it's got some single quotes$$)

Use $$ before and after the string. It will insert data.