Updating matrix table Postgres

Question

I have a table distance which is a matrix.

Holding: start , end, cost. For every start-end there is a counterpart: end-start.

The problem is that the cost for the counter parts are not equal.

I want to correct that, and am using the following SQL:

WITH subquery AS (
    SELECT start, end, cost
    FROM  distance
)
UPDATE distance
SET cost = subquery.cost
    
FROM subquery
WHERE distance.start = subquery.end 
AND distance.cost <> subquery.cost;

This query is now running for more then 10 hours. And I doubt if it will get me the results.

Is there a better way? I don't care if the cost comes from the start-end pair, or the end-start pair. They just need to be the same.

Using Postgres 12. The table holds about 170.000.000 records.

You're matching all (point ,x) to all (y, point). It can take a time apparently. WHERE distance.start = subquery.end and distance.end= subquery.start I guess. — Serg
– Serg, Commented Jun 26, 2021 at 8:10
Note: both start and end are keywords in SQL(see the syntax highlighting) . Better not use them as column names. — wildplasser
– wildplasser, Commented Jun 26, 2021 at 11:21
Thanks wildplasser. In reality they are named start_vid and end_vid. According to Dutch law, you can get a ticket for wild plassen....... — Maas
– Maas, Commented Jun 26, 2021 at 16:32

Gordon Linoff · Accepted Answer · 2021-06-26 11:15:04Z

1

Your logic for defining pairs is off. You are only looking at one column rather than both. Your current query is not going to do what you want. Kill it.

The second point is that you only need to update one row of the pair. So, arbitrarily choose the "ascending" row (start < end) or the "descending" row (start > end) to update, but not both. The following updates the descending row:

UPDATE distance d
    SET cost = d2.cost
    FROM distance d2
    WHERE d2.start = d.end AND
          d2.end = d.start AND
----------^ the missing condition in your query
          d2.start > d.end AND
----------^ the "descending" condition
          d2.cost <> d.cost;

answered Jun 26, 2021 at 11:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Maas Over a year ago

Thanks, but it is not working. I guess the d2.start > d.end is never met, because prior we already set both to equal. I have however another field which is an unique id. Maybe that can help?

Gordon Linoff Over a year ago

@Maas . . . Arggh! That should be d.start > d.end or d2.start > d2.end.

Maas Over a year ago

Gordon, it's working like a charm! Thanks!!

Collectives™ on Stack Overflow

Updating matrix table Postgres

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related