Porównywanie i filtrowanie tablic: =, @>, <@, &&

Czasem pojawiają się sytuacje, gdzie trzeba ogarnąć, czy jeden zbiór wartości zawiera się w innym, czy się przecinają, czy są równe albo czy są podzbiorem siebie nawzajem. Na przykład:

• Czy user ma dostęp do konkretnych kategorii?
• Jakie produkty mają wspólne tagi?
• Jakie tablice zawierają wszystkie wymienione wartości?

Takie zadania ogarniasz przez operatory porównywania tablic w PostgreSQL: =, @>, <@ i &&. Zaraz wbijemy w każdy z tych operatorów i rozkminimy je na przykładach.

= — Porównanie tablic na równość

Ten operator sprawdza, czy dwie tablice są identyczne. Warunek zwróci TRUE, jeśli obie tablice mają tę samą długość i dokładnie ten sam zestaw elementów w tej samej kolejności.

Przykład:

SELECT ARRAY[1, 2, 3] = ARRAY[1, 2, 3] AS czy_rowne;  -- TRUE
SELECT ARRAY[1, 2, 3] = ARRAY[3, 2, 1] AS czy_rowne;  -- FALSE

Uwaga: kolejność elementów ma znaczenie. Tablica [1, 2, 3] nie jest równa [3, 2, 1].

@> — Tablica zawiera inną tablicę

Ten operator sprawdza, czy tablica zawiera wszystkie elementy innej tablicy (kolejność nie gra roli). Często używany, gdy trzeba się upewnić, że wszystkie szukane elementy są obecne.

Przykład:

SELECT ARRAY[1, 2, 3] @> ARRAY[1, 2] AS zawiera;  -- TRUE
SELECT ARRAY[1, 2, 3] @> ARRAY[4] AS zawiera;     -- FALSE

Przydatny case: sprawdzanie uprawnień. Na przykład chcesz się upewnić, że dana rola ma wszystkie wymagane permisje.

<@ — Tablica jest podzbiorem innej tablicy

Odwrotność operatora @>. Sprawdza, czy tablica w całości zawiera się w innej tablicy.

Przykład:

SELECT ARRAY[1, 2] <@ ARRAY[1, 2, 3] AS czy_podzbior;  -- TRUE
SELECT ARRAY[4] <@ ARRAY[1, 2, 3] AS czy_podzbior;     -- FALSE

Przydatny case: kiedy trzeba sprawdzić, czy dany wiersz tabeli ma wszystkie wymagane tagi albo kategorie.

&& — Przecięcie tablic

Ten operator sprawdza, czy dwie tablice mają choć jeden wspólny element.

Przykład:

SELECT ARRAY[1, 2, 3] && ARRAY[3, 4, 5] AS przecinaja_sie;  -- TRUE
SELECT ARRAY[1, 2, 3] && ARRAY[6, 7, 8] AS przecinaja_sie;  -- FALSE

Przydatny case: szukanie produktów albo rekordów, które mają wspólne kategorie lub cechy.

Użycie operatorów do filtrowania danych

Sprawdźmy, jak te operatory działają przy filtrowaniu danych w tabeli. Załóżmy, że mamy tabelę products z kolumną tags, gdzie trzymamy tablicę tagów dla każdego produktu.

Przykładowa tabela products

Znajdźmy wszystkie produkty, które mają tag electronics.

SELECT *
FROM products
WHERE tags @> ARRAY['electronics'];

Wynik:

Chcemy znaleźć produkty, które mają dokładnie tagi {home, sale}.

SELECT *
FROM products
WHERE tags = ARRAY['home', 'sale'];

Wynik:

A teraz

znajdźmy produkty, które mają przynajmniej jeden z tagów {sale, new}.

SELECT *
FROM products
WHERE tags && ARRAY['sale', 'new'];

Wynik:

Szukamy produktów, gdzie tagi to dokładnie {home, garden}.

SELECT *
FROM products
WHERE tags <@ ARRAY['home', 'garden'];

Wynik:

Przykład: sprawdzanie zawartości list z CASE

Możemy połączyć porównywanie tablic z warunkami. Na przykład, zróbmy zapytanie, które oznacza produkty z tagiem sale jako "Promocja", a resztę jako "Bez promocji":

SELECT name,
       CASE
         WHEN tags @> ARRAY['sale'] THEN 'Promocja'
         ELSE 'Bez promocji'
       END AS discount_status
FROM products;

Wynik:

Typowe błędy i jak ich unikać

Błąd: złe użycie kolejności elementów przy operatorze =. Pamiętaj, że = wymaga dokładnego dopasowania kolejności elementów. Jeśli kolejność nie jest ważna, użyj @> albo &&.

Błąd: niezgodność typów danych. Tablice muszą być tego samego typu. Na przykład ARRAY['1', '2'] nie jest równe ARRAY[1, 2], bo pierwsza to tablica stringów, a druga liczbowa. Zawsze sprawdzaj typy przed porównaniem.

Błąd: brak indeksów. Jeśli często filtrujesz wiersze po tablicach, koniecznie zakładaj indeksy (GIN), żeby przyspieszyć zapytania.

Te operatory pozwalają ogarniać mocne filtrowanie i porównywanie danych, które często spotkasz w realnych projektach: od analizy tagów produktów po zarządzanie uprawnieniami. Używając ich dobrze, uprościsz sobie życie i zoptymalizujesz swoje zapytania.