PostgreSQL Update with Replace – How to Guide

postgresqlpostgresql-9.4update

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>

Best Answer

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.

Postgresql – Recursive query using plpgsql

You don't need a the function at all, this can be done with a single SQL statement:

with recursive tree as (id, parent) (
    select link as id, 
           parent
    from linktable
    where id = itemid

    union all

    select c.link as id,
           c.parent
    from linktable c
      join tree p on p.id = c.parent
) 
select dt.id, dt.value
from tree
  join datatable dt on dt.id = tree.id

Please see the manual for an introduction to recursive queries: http://www.postgresql.org/docs/current/static/queries-with.html

PostgreSQL – Is There a Type-Safe first() Aggregate Function?

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Best Answer

Related Solutions

Postgresql – Recursive query using plpgsql

PostgreSQL – Is There a Type-Safe first() Aggregate Function?

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`