Praca z tymczasowymi tabelami w PostgreSQL

Tymczasowe tabele to takie tabele, które istnieją tylko w ramach bieżącej sesji albo transakcji. Jak tylko twoja sesja się skończy (albo zamkniesz transakcję), tymczasowa tabela i jej dane znikną jak ślady na piasku. Są idealne do tymczasowego przechowywania danych, obliczeń pośrednich albo przygotowania danych do bardziej skomplikowanych operacji.

Po co one są?

• Tymczasowe przechowywanie danych: Na przykład masz skomplikowane obliczenia na danych, które trzeba wykonać w kilku etapach. Zamiast obciążać główną tabelę, możesz użyć tymczasowych tabel.
• Analiza danych: Możesz zebrać dane z kilku źródeł, obrobić je, a potem wywalić tymczasową tabelę po zakończeniu analizy.
• Optymalizacja skomplikowanych zapytań: Czasem lepiej podzielić zapytanie na kilka etapów z użyciem tymczasowych tabel, żeby przyspieszyć jego wykonanie.
• Minimalizacja ryzyka: Nie ma ryzyka przypadkowego popsucia prawdziwych tabel — tymczasowe tabele są całkowicie odizolowane.

Magia tymczasowych tabel polega na tym, że ich dane są widoczne tylko dla ciebie! Inni użytkownicy bazy nie mają do nich dostępu, więc możesz spokojnie eksperymentować.

Tworzenie tymczasowych tabel

Tworzenie tymczasowej tabeli wygląda prawie tak samo jak zwykłej, tylko dodajesz słowo kluczowe TEMP albo TEMPORARY.

Składnia:

CREATE TEMP TABLE table_name (
    column_name data_type constraints,
    ...
);

Proste, nie? Tymczasowa tabela zniknie automatycznie po zakończeniu twojej sesji.

Dla przykładu stwórzmy tymczasową tabelę do przechowywania danych o studentach:

CREATE TEMP TABLE temp_students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age > 0)
);

Teraz mamy tymczasową tabelę temp_students, do której można wrzucać dane i pracować z nimi jak ze zwykłą tabelą. Tylko pamiętaj — ona zniknie, jak tylko zamkniesz sesję.

Tworzenie tymczasowej tabeli z zapytania

Czasem nie chce ci się ręcznie opisywać struktury tymczasowej tabeli — szczególnie jeśli po prostu chcesz zachować wynik jakiegoś zapytania i potem z nim popracować. Wtedy przydaje się taka forma:

CREATE TEMP TABLE nazwa_tabeli AS
SELECT ...;

Ta konstrukcja nie tylko tworzy tymczasową tabelę, ale od razu ją wypełnia danymi z zapytania.

Załóżmy, że chcesz zachować studentów, którzy nie są zapisani na żaden kurs:

CREATE TEMP TABLE temp_unregistered_students AS
    SELECT s.id, s.name
    FROM students s
    LEFT JOIN enrollments e ON s.id = e.student_id
    WHERE e.student_id IS NULL;

Teraz masz tabelę temp_unregistered_students, której możesz używać w innych częściach skryptu — bez konieczności powtarzania zapytania.

Po co to?

• Nie musisz jawnie podawać kolumn i typów — PostgreSQL sam je ogarnie na podstawie wyniku SELECT.
• Możesz wielokrotnie używać tymczasowych danych bez ponownych obliczeń.
• To szczególnie przydatne w raportach, operacjach ETL i analizie.

Tymczasowe tabele w transakcjach

Tymczasowe tabele możesz tworzyć wewnątrz transakcji. Wtedy zostaną automatycznie usunięte, jak tylko transakcja się skończy. To szczególnie przydatne, gdy chcesz mieć pewność, że tymczasowe dane nie zostaną w bazie. O transakcjach opowiem więcej za parę poziomów — na razie możesz cieszyć się beztroskim życiem :P

Przykład:

BEGIN;

CREATE TEMP TABLE temp_transactions (
    transaction_id SERIAL PRIMARY KEY,
    amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(50)
);

INSERT INTO temp_transactions (amount, status)
VALUES (100.50, 'Oczekujące');

-- Zobaczmy dane
SELECT * FROM temp_transactions;

COMMIT;
-- Teraz tabela temp_transactions zniknęła!

Jeśli zamiast COMMIT wykonasz ROLLBACK, dane i tabela też zostaną usunięte.

Użycie tymczasowych tabel w prawdziwych zadaniach

Przykład 1: Tymczasowe łączenie danych

Załóżmy, że mamy dwie tabele: students i courses. Trzeba sprawdzić, którzy studenci jeszcze nie są zapisani na kursy. Możemy najpierw zebrać dane do tymczasowej tabeli, a potem zrobić analizę.

CREATE TEMP TABLE temp_unregistered_students AS
	SELECT s.id, s.name
	FROM students s
	LEFT JOIN enrollments e ON s.id = e.student_id
	WHERE e.student_id IS NULL;

-- Teraz możemy pracować z tymczasową tabelą temp_unregistered_students.
SELECT * FROM temp_unregistered_students;

Przykład 2: Przygotowanie danych do raportu

Czasem trzeba zebrać dane z kilku tabel, przetworzyć je i przygotować raport. Tymczasowe tabele świetnie się do tego nadają.

CREATE TEMP TABLE temp_sales AS
	SELECT p.product_id, p.name, SUM(s.quantity) AS total_quantity
	FROM products p
	JOIN sales s ON p.product_id = s.product_id
	GROUP BY p.product_id, p.name;

-- Stworzyliśmy tymczasową tabelę z wynikiem. Teraz można na jej podstawie zrobić raport:
SELECT name, total_quantity FROM temp_sales WHERE total_quantity > 50;

Sztuczki i cechy tymczasowych tabel

Nazewnictwo tymczasowych tabel: jeśli masz już zwykłą tabelę o nazwie students, i tak możesz stworzyć tymczasową tabelę o tej samej nazwie! Tymczasowa tabela będzie miała priorytet w twojej sesji. Ale to może zamieszać, jeśli zapomnisz, że pracujesz z tymczasową tabelą.

Optymalizacja tymczasowych tabel: PostgreSQL automatycznie tworzy indeksy dla kolumn z ograniczeniem PRIMARY KEY albo UNIQUE nawet w tymczasowych tabelach. Jeśli chcesz przyspieszyć pracę z danymi, możesz ręcznie dodać indeksy:

CREATE INDEX idx_temp_students_age ON temp_students (age);

Usuwanie tymczasowej tabeli: jeśli chcesz usunąć tymczasową tabelę przed końcem sesji, użyj komendy DROP TABLE:

DROP TABLE temp_students;

Rozmiar danych w tymczasowej tabeli: tymczasowe tabele trzymane są w pamięci (jeśli jest wystarczająco zasobów), co sprawia, że są dużo szybsze. Ale pamiętaj, że zbyt duże dane mogą zacząć korzystać z dysku.

Częste błędy i jak ich unikać

Błąd 1: "Tabela już istnieje"

Jeśli spróbujesz stworzyć tymczasową tabelę o nazwie, która już jest zajęta przez inną tymczasową tabelę, dostaniesz błąd. Użyj CREATE TEMP TABLE IF NOT EXISTS albo usuń tabelę przed stworzeniem:

DROP TABLE IF EXISTS temp_students;
CREATE TEMP TABLE temp_students (...);

Błąd 2: "Tabela znika za wcześnie"

Jeśli tworzysz tymczasową tabelę wewnątrz transakcji, pamiętaj, że zniknie ona, gdy transakcja się skończy. Planuj swoje działania z wyprzedzeniem!