PostgreSQL delete records

Question

I have a table where relationships between two entities are stored in both directions. So if A is linked to B,I have two records - one representing A > B and another B > A.

test=# select * from myt;
 id | from_id | to_id 
----+---------+-------
  1 |       1 |     2
  2 |       2 |     1
  3 |       1 |     3
  4 |       3 |     1
(4 rows)

Is it possible to delete records with SQL so that I have only record to represent each relationship?

It does not matter which one gets deleted.

Once you have deleted those rows, you might want to add a unique index that prevents further insert of such combinations. — user330315
– user330315, Commented Feb 3, 2014 at 15:01

Mureinik · Accepted Answer · 2014-02-03 14:13:56Z

2

You can use the using clause to perform a sort of a self join:

DELETE FROM myt a
USING       myt b
WHERE       a.from_id = b.to_id AND a.to_id = b.from_id  -- The "join"
            AND a.from_id < b.from_id -- Make sure you don't delete both copies

answered Feb 3, 2014 at 14:13

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

2 Comments

Jayadevan Over a year ago

Thanks. It worked. I am a bit lost on the AND a.from_id < b.from_id part. How does that work?

Mureinik Over a year ago

@Jayadevan Without that part, you would delete any pair that has an oposite. So suppose you have (1,2) and (2,1), you'd delete both, which isn't the intended behavior. By adding this condition I make sure to delete only one of them (arbiteraly decided to delete the one with the lower to - could have gone the other way too)

David Aldridge · Accepted Answer · 2014-02-03 14:38:18Z

0

If you always have a pair or records created, then there will always be one with from_id > to_id, and another with to_id > from_id.

Therefore you can just:

delete from myt
where  to_id > from_id;

If you really wanted to guarantee that the other row existed then you could:

delete from myt myt1
where  to_id > from_id and
       exists (select 1
              from   myt myt2
              where  myt2.from_id = myt.to_id and
                     myt2.to_id   = myt.from_id);

That shouldn't be required if the creation of the pair is guaranteed.

If you can have a link from and to the same id then you'd need to differentiate between them in a different way of course.

answered Feb 3, 2014 at 14:38

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Collectives™ on Stack Overflow

PostgreSQL delete records

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