How do I remove single quotes from a table in postgresql?

Question

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 quotes and I cant figure out how to get rid of them, because obviously this

  update tablename set fieldname= NULL where fieldname=' ; 

wont work.

Answer 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

Answer 2

update tablename set fieldname= NULL where fieldname='''' ;

or

update tablename set fieldname= NULL where fieldname=E'\'' ;

Answer 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.