Wyszukiwanie danych w tablicy: ANY, ALL, array_contains()

Tablice są często używane do przechowywania wielu wartości w jednej kolumnie — czy to tagi, numery, role czy preferencje. Ale jak sprawdzić, czy w tablicy jest interesująca nas wartość, albo wręcz przeciwnie — czy wszystkie elementy spełniają określony warunek? PostgreSQL daje wygodne narzędzia do takich sprawdzeń: ANY, ALL i array_contains(). Przejdźmy przez nie po kolei.

Co to jest ANY?

Wyobraź sobie, że masz tablicę, w której jest kilka wartości, np. ID produktów, numery telefonów albo lista hobby. Często pojawia się potrzeba sprawdzenia, czy w tablicy jest konkretna wartość. I tu na scenę wchodzi operator ANY. Pozwala on sprawdzić, czy nasz "szukany obiekt" znajduje się w tablicy.

Składnia ANY

SELECT *
FROM your_table
WHERE value = ANY(array_column);

Tutaj:

• array_column — to tablica, którą sprawdzamy.
• value — to wartość, której szukamy w tablicy.

Przykład użycia ANY

Załóżmy, że mamy tabelę students o takiej strukturze:

Interesują nas studenci, którzy mają wśród hobby pływanie. Zapytanie będzie wyglądać tak:

SELECT *
FROM students
WHERE 'pływanie' = ANY(hobbies);

Wynik:

Krótko mówiąc, ANY sprawdza: "Czy w tej tablicy jest ta wartość?".

Zaufaj, ale sprawdź wszystko! Używamy ALL

Teraz wyobraź sobie odwrotną sytuację: musisz się upewnić, że wszystkie elementy tablicy spełniają określoną regułę. Do tego PostgreSQL daje operator ALL. Dzięki niemu możesz sprawdzić, czy wszystkie elementy tablicy, np. mają daną wartość, są większe od jakiejś liczby albo spełniają inne logiczne warunki.

Składnia ALL

SELECT *
FROM your_table
WHERE value < ALL(array_column);

Tutaj:

• array_column — to tablica, którą sprawdzamy.
• value < ALL(...) oznacza, że wszystkie elementy tablicy muszą być większe od tej wartości.

Wróćmy do naszej tabeli students. Załóżmy, że chcemy znaleźć studentów, których wszystkie hobby zaczynają się na literę "c". Zapytanie:

SELECT *
FROM students
WHERE 'czytanie' = ALL(hobbies);

Wynik będzie pusty, bo nikt nie ma wszystkich hobby równych słowu "czytanie". Żeby przykład był bardziej jasny (i działał), pomyślmy o danych liczbowych.

Załóżmy, że mamy tabelę orders:

Znajdź wszystkie zamówienia, gdzie każda pozycja jest tańsza niż 400:

SELECT *
FROM orders
WHERE 400 > ALL(prices);

Wynik:

Filtrowanie wierszy po zawartości tablicy

Omówiliśmy podstawowe funkcje i operatory do wyszukiwania danych w tablicach, ale jak to wygląda w praktyce? Zobaczmy kilka przykładów użycia.

Przykład 1: Znajdź studentów, którzy mają choć jedno hobby — muzyka

Wyjściowa tabela:

SELECT *
FROM students
WHERE 'muzyka' = ANY(hobbies);

Wynik:

Przykład 2: Znajdź zamówienia z cenami powyżej 100 we wszystkich pozycjach

Wyjściowa tabela orders:

SELECT *
FROM orders
WHERE 100 < ALL(prices);

Wynik:

Typowe błędy i jak ich unikać

Kiedy pracujesz z tablicami, szczególnie przy wyszukiwaniu danych, jest kilka pułapek, na które możesz się natknąć.

Błąd: Nieprawidłowe użycie ANY lub ALL. Na przykład, jeśli przypadkowo pomylisz operatory, możesz dostać nieoczekiwane wyniki.

-- Zły wariant:
SELECT *
FROM students
WHERE hobbies = ANY('czytanie');

W tym przykładzie błąd polega na tym, że hobbies to tablica, a 'czytanie' to string. To tablica powinna być sprawdzana przez ANY, a nie odwrotnie.

Błąd: Indeksowanie tablic zaczyna się od 1, a nie od 0. Jeśli używasz tablic w innych funkcjach albo próbujesz ręcznie wyciągać elementy, pamiętaj o specyfice PostgreSQL.

Błąd: Brak indeksowania. Jeśli tablice są używane do częstych operacji wyszukiwania (ANY, ALL), indeksowanie może mocno przyspieszyć zapytania.