0

Let's for example I have the next table:

CREATE TABLE temp
(
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    arr bigint[] NOT NULL
);

And insert rows it:

INSERT INTO temp (arr) VALUES
(ARRAY[2, 3]),
(ARRAY[2,3,4]),
(ARRAY[4]),
(ARRAY[1, 2, 3])

So, I have now in the table:

enter image description here

I want to have a query that return only arrays which are unique (in a manner that are not contains by other arrays) So, the return will be rows number 2 & 4 (the arr column)

Shay Zambrovski
  • 401
  • 5
  • 21

1 Answers1

2

This can be don using a NOT EXISTS condition:

select t1.*
from temp t1
where not exists (select * 
                  from temp t2
                  where t1.id <> t2.id
                  and t2.arr @> t1.arr);
  • seems works, what is it `<>` and `@>` means? – Shay Zambrovski Jul 12 '22 at 17:58
  • 1
    `<>` is the operator for "not equal" in SQL. And the `@>` [operator](https://www.postgresql.org/docs/current/functions-array.html) tests if one array is contained in another –  Jul 12 '22 at 17:59
  • is it possible to do it without any ids? i.e. having only column of arrays? (a result from a `group by` statement) – Shay Zambrovski Jul 12 '22 at 19:43