Postgres update with replace

Question

I am doing select which is:

SELECT replace(field_1, E'<div class="ad-content"><div class="ad-title">publicidade</div><div id=\'div-gpt-ad-1426519214388-1\'> <script type=\'text/javascript\'>googletag.cmd.push(function(){googletag.display(\'div-gpt-ad-1426519214388-1\');});</script> </div></div>', '') FROM test

An that returns what I want, but when I try to update doing the same thing, It doesn't work as expected:

UPDATE test SET field_1 = replace(field_1, E'<div class="ad-content"><div class="ad-title">publicidade</div><div id=\'div-gpt-ad-1426519214388-1\'> <script type=\'text/javascript\'>googletag.cmd.push(function(){googletag.display(\'div-gpt-ad-1426519214388-1\');});</script> </div></div>', '')

It doesn't update the column without the

<div class="ad-content"><div class="ad-title">publicidade</div><div id=\'div-gpt-ad-1426519214388-1\'> <script type=\'text/javascript\'>googletag.cmd.push(function(){googletag.display(\'div-gpt-ad-1426519214388-1\');});</script> </div></div>

• jjanes: It does update them, it just updates them to the same value they already had. Why did you think it would do something different? Why do you think it should do something different? Why do you think it did do something different?

Answer 1

If the SELECT works as expected, so should your UPDATE. More: it should update all columns, which is most probably not what you want.

UPDATE test
SET field_1 = replace(field_1, $$<div class="ad-content"><div class="ad-title">publicidade</div><div id='div-gpt-ad-1426519214388-1'> <script type='text/javascript'>googletag.cmd.push(function(){googletag.display('div-gpt-ad-1426519214388-1');});</script> </div></div>$,ドル '')
WHERE field_1 LIKE $$%<div class="ad-content"><div class="ad-title">publicidade</div><div id='div-gpt-ad-1426519214388-1'> <script type='text/javascript'>googletag.cmd.push(function(){googletag.display('div-gpt-ad-1426519214388-1');});</script> </div></div>%$$

I added a WHERE clause to avoid expensive and pointless empty updates.

Related:

• Insert text with single quotes in PostgreSQL
• How do I (or can I) SELECT DISTINCT on multiple columns?

• srghma: Tnx for WHERE clause trick, I was getting to severe performance issues whole day until I found this trick