How to use postgres function result as a separate column in DELETE query

Question

I have to delete rows in a table based on multiple comparisons with a really long function application on one of the columns. To go a bit into details, i'm decoding from base64, doing a substring, transforming to json, taking a specific field, then transforming that to inet.

I'm using those ips to compare with other ips / masks in my WHERE clause.

The problem i have is that obtaining the actual result is a pretty long string, and if i end up saying ip < foo(bar(baz(quux))) OR ip2 < foo(bar(baz(quux))) then that value for comparison will end up being calculated multiple times too.

I'm looking for a way to precompute that value, assign it a meaningful name, then use that name in my WHERE clause.

Thank you

Gordon Linoff · Accepted Answer · 2019-02-16 15:10:08Z

2

You can use a lateral join:

select . . .
from t cross join lateral
     (values (foo(bar(baz(quux))) ) v(decoded_value)
where ip < v.decoded_vaue or ip2 < v.decoded_value;

In Postgres, a delete is a little trickier. Assuming you have a primary key:

delete t
using t t2 cross join lateral
     (values (foo(bar(baz(t2.quux))) ) v(decoded_value)
where t2.primary_key = t.primary_key and
      (t2.ip < v.decoded_vaue or t2.ip2 < v.decoded_value);

edited Feb 16, 2019 at 15:10

answered Feb 16, 2019 at 13:27

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

omu_negru Over a year ago

I'm doing a DELETE, not a select, so would using a join not actually have me end up with a different table / view ?

omu_negru Over a year ago

That second one will do it i think. Thank you.

Dimitar Spasovski · Accepted Answer · 2019-02-16 18:51:47Z

Have you considered the usage of a CTE?

For example, let's say that we have the following table

CREATE TABLE test_table (
  id BIGINT PRIMARY KEY,
  some_data INTEGER
);

Let's say that we want to delete all of the rows in which the result of the expression

some_data % 10

is (>=1 and <=4) or (>=6 and <=8).

In this case the modulo operation replaces your foo(bar(baz(column_name))) call with the modulo operation.

The simple approach would be to write the following query:

DELETE FROM test_table
WHERE
  (MOD(some_data, 10) >= 1 AND MOD(some_data, 10) <= 4)
  OR
  (MOD(some_data, 10) >=6 AND MOD(some_data, 10) <= 8);

As you said, this will recalculate the same thing multiple times. In order to do the calculation only once, we can create a CTE and use the result in the delete query.

WITH precalculated_table AS (
  SELECT *, mod(some_data, 10) as mod_10_result FROM test_table
)
DELETE FROM test_table WHERE id IN (
    SELECT id from precalculated_table
  where
    (mod_10_result >= 1 and mod_10_result <= 4)
    OR
    (mod_10_result >= 6 AND mod_10_result <= 8)
);

This way, we do the calculation only once and then we use the precaculated column in the WHERE part of the DELETE query.

Collectives™ on Stack Overflow

How to use postgres function result as a separate column in DELETE query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related