postgresql. filter by intersection of array field values

Question

My dataset is:

# select * from test;
   list   | locked
 ---------+--------
  {a,b,c} | t
  {f,g,h} | f
  {c,d,e} | f

I need to select locked=FALSE rows with list values not intersecting with any locked=TRUE row.

By now, I'm stuck with:

# SELECT * FROM test WHERE NOT locked and NOT list && (SELECT list FROM test WHERE locked);
  list   | locked
---------+--------
 {f,g,h} | f
 (1 row)

Works but looks overheaded by comparing each locked=TRUE row. I'm looking for a way to select aggregated set of all locked=TRUE list values and compare it once for each locked=FALSE row.

redneb · Accepted Answer · 2016-09-30 15:18:51Z

1

Your query should work correctly. If I understood you correctly, you are looking for something more efficient. If you are willing to create a new table, you could something like this:

CREATE TABLE locked(elem text PRIMARY KEY);
INSERT INTO locked SELECT DISTINCT unnest(list) FROM test WHERE locked;

This table will contain all elements from all the lists from test that correspond to a locked state. I am using the unnest function to convert (what appears to be) text[] to a table of text so that I can apply DISTINCT.

Then you would use that table like so

SELECT *
FROM test
WHERE NOT locked AND
      NOT exists(SELECT FROM locked WHERE elem=ANY (list));

Note the NOT locked condition in the last query is redundant, but it might improve the performance if you use it.

If you need even better performance, you might have to further normalize the table test.

edited Sep 30, 2016 at 15:18

answered Sep 30, 2016 at 15:13

redneb

24.3k7 gold badges46 silver badges55 bronze badges

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

1 Comment

demon.mhm Over a year ago

That's a good suggestion. But ti doesn't feet my needs. Locks mechanics means concurrency. My table should work as job queue where concurrent workers takes jobs by UPDATE row whith setting lock attribute and RETURNING * so other workers can't take it. Managing separate lock table is way more overhead then comparing each row in this case.

Collectives™ on Stack Overflow

postgresql. filter by intersection of array field values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related