Praca z tablicami w PostgreSQL

Praca z tablicami w PostgreSQL

Teraz, kiedy znamy podstawy, zanurkujmy głębiej w praktyczne tworzenie tablic w zapytaniach SQL. Tutaj zaczyna się najciekawsze!

Użycie konstruktora ARRAY[] w SELECT

Konstruktor ARRAY[] jest mega wygodny w SELECT-zapytaniach, kiedy chcesz jawnie stworzyć tablicę. To jak powiedzieć PostgreSQL: „Hej, tu masz tablicę!”

-- Tworzenie tablicy liczb
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- Tworzenie tablicy stringów
SELECT ARRAY['Poniedziałek', 'Wtorek', 'Środa'] AS weekdays;

Zalety ARRAY[] nad składnią {}

1. Jawne rzutowanie typów:

-- Z ARRAY[] możesz jawnie podać typ
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- Z {} trzeba uważać
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. Lepsza czytelność w złożonych zapytaniach:

SELECT
product_name,
ARRAY[category, subcategory, brand] AS product_hierarchy
FROM products;

Przykład: tworzenie tablicy liczb

Zacznijmy od klasyki. Załóżmy, że chcesz stworzyć tablicę z liczb:

SELECT ARRAY[1, 2, 3, 4, 5] AS my_array;

Wynik będzie taki:

Zwróć uwagę: PostgreSQL zwraca tablicę w formacie {} — to po prostu jego sposób na pokazanie, że to tablica. Styl specyficzny, ale szybko się przyzwyczaisz.

Przykład: tworzenie tablicy stringów

Jeśli potrzebujesz stringów zamiast cyfr, po prostu dodaj cudzysłowy:

SELECT ARRAY['jabłko', 'banan', 'pomarańcza'] AS fruits;

Wynik:

Przy okazji, PostgreSQL uwielbia ułatwiać życie. Nawet jeśli używasz cyrylicy albo innego alfabetu, tablice i tak będą działać bez zarzutu.

Przykład: tablice z danymi innych typów (np. daty)

A co, jeśli chcemy wrzucić do tablicy daty? Prościzna:

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS important_dates;

Wynik:

Zwróć uwagę na ::DATE. Jawnie powiedzieliśmy PostgreSQL, że to typ danych DATE. Bez tego mógłby potraktować stringi jak są, a to nie byłoby do końca poprawne dla dat.

Agregacja danych do tablic z array_agg()

No to lecimy do trudniejszej i ciekawszej części. Co jeśli już mamy tabelę z danymi i chcemy je zgrupować do tablicy? Tu wchodzi funkcja array_agg().

Jedna z najmocniejszych opcji — zamiana wielu wierszy na tablice za pomocą array_agg().

Podstawowe użycie:

-- Tworzymy testową tabelę
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

INSERT INTO students VALUES
(1, 'Anna'), (1, 'Otto'), (1, 'Maria'),
(2, 'Aleks'), (2, 'Kira'),
(3, 'Elena');

-- Grupujemy studentów po grupach
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

Sortowanie elementów w tablicy:

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS students_sorted
FROM students
GROUP BY group_id;

Filtrowanie przy agregacji:

SELECT
group_id,
array_agg(student_name) FILTER (WHERE student_name LIKE 'A%') AS students_a
FROM students
GROUP BY group_id;

Praktyczne przykłady użycia

Tablice są przydatne w wielu codziennych sytuacjach: od przechowywania tagów i uprawnień dostępu po zbieranie działań użytkownika w ciągu dnia. Poniżej znajdziesz przykłady, które pomogą lepiej ogarnąć, jak i gdzie używać tablic w PostgreSQL.

Przykład 1: System tagów dla bloga

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    tags TEXT[]
);

-- Wstawianie z różnymi składniami
INSERT INTO blog_posts (title, content, tags) VALUES
    ('Poznajemy PostgreSQL', 'Treść artykułu...',
     ARRAY['PostgreSQL', 'SQL', 'Baza danych']),
    ('Web development w 2024', 'Treść artykułu...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Uczenie maszynowe', 'Treść artykułu...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Szukanie artykułów po tagach
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Przykład 2: System uprawnień użytkowników

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
permissions TEXT[]
);

INSERT INTO users (username, permissions) VALUES
('admin', ARRAY['read', 'write', 'delete', 'manage_users']),
('editor', ARRAY['read', 'write']),
('viewer', ARRAY['read']);

-- Agregacja wszystkich unikalnych uprawnień w systemie
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

Przykład 3: Historia działań użytkownika

CREATE TABLE user_actions (
user_id INTEGER,
action TEXT,
action_date DATE
);

INSERT INTO user_actions VALUES
(1, 'login', '2024-01-01'),
(1, 'view_profile', '2024-01-01'),
(1, 'edit_settings', '2024-01-01'),
(2, 'login', '2024-01-01'),
(2, 'logout', '2024-01-01');

-- Grupujemy działania użytkownika po dniach
SELECT
user_id,
action_date,
array_agg(action ORDER BY action) AS daily_actions
FROM user_actions
GROUP BY user_id, action_date
ORDER BY user_id, action_date;

4. Zapytania z tablicami: wybieranie i filtrowanie

Kiedy mamy tablice, musimy umieć je wyciągać i analizować. Możesz użyć standardowego SELECT, żeby pobrać tablicę:

SELECT tags FROM articles WHERE id = 1;

To zwróci:

Ale co, jeśli chcemy znaleźć artykuł, który ma konkretny tag, np. PostgreSQL? To temat, który dokładniej omówimy na kolejnej lekcji, ale sama idea jest prosta: tablice dają nam elastyczność i pozwalają szukać wartości w środku tablic.