Potrzeba typów wyliczeniowych w bazie danych pojawia się praktycznie w każdej strukturze danych.
Typ enum w bazie Postgresql pełni nieocenioną funkcję w zapisie danych typu wyliczeniowego:
- Zalety:
- pozwala na znaczne ograniczenie rozmiaru kolumny
- zapewnia czytelność danych w kolumnie
- skutkuje wzrostem wydajności
- automatyczna kontrola dozwolonych danych z zestawu
- dla poprawnie zbudowanego zbioru (kolejność), można stosować wszystkie operatory porównania (< , >,= ,itd...)
- Wady:
- NIE MOŻNA W PROSTY SPOSÓB USUWAĆ JUŻ ZAPISANYCH WARTOŚCI W ZBIORZE ENUM
- mogą wystąpić problemy przy przenoszeniu struktury do innej bazy danych.
Oczywiście można stworzyć alternatywnie rozwiązania:
W tabeli podstawowej wpisywać wartości, które zajmują możliwie mało miejsca i dla łatwej czytelności danych, łączyć się z dodatkową tabelą "słownikiem"
- Zalety:
- tabela podstawowa zajmuje mniej miejsca
- ułatwione czytanie danych
- struktura jest bardziej przenośna,
- Wady:
- spada wydajność spowodowana łączeniem z tabelą odnośników
- brak automatycznej kontroli nad wpisywanymi wartościami do kolumny (trzeba dodać ograniczenia)
Można również wpisywać dane do kolumny w formie stringów
- Zalety:
- nie trzeba łączyć się z tabelą odnośników
- Wady:
- powoduje kompletnie niepotrzebny wzrost ilości zapisywanych danych
- jak w pierwszym przykładzie, brak automatycznej kontroli nad wpisywanymi wartościami do kolumny (trzeba dodać ograniczenia )
- w przypadku wartości char powoduje dodatkowy wzrost zajmowanego miejsca , z kolei typ varchar, może skutkować zmniejszeniem szybkości odczytu danych z powodu obliczania dodatkowego offseta.
Do stworzenia typu wyliczeniowego korzystamy z zapytania SQL:
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
Porównania
SELECT * FROM person WHERE current_mood > 'sad';
zwraca rekordy które zawierają
'ok', 'happy'.
Dodawanie do zestawu
ALTER TYPE mood ADD VALUE 'excited' BEFORE 'happy';
Często rodzi się potrzeba, żeby sprawdzić jakie wartości może przyjmować dany typ.
Można to zrobić zapytaniem SQL na 2 sposoby:
Prostszy
select enum_range(null::mood)
Nieco bardziej skomplikowany :) , ale za to daje wgląd w strukturę
SELECT n.nspname AS "schema", t.typname, e.enumlabel
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'mood'
GROUP BY 1,2,3;
otrzymujemy
"public";"mood";"excited"
"public";"mood";"happy"
"public";"mood";"ok"
"public";"mood";"sad"
Dane te nie są prezentowane dla nas w atrakcyjny sposób , tym bardziej, gdybyśmy zrezygnowali z warunku WHERE
Z pomocą przychodzi nam funkcja agregująca string_agg
SELECT n.nspname AS "schema", t.typname,
string_agg( e.enumlabel, '|' ORDER BY e.enumsortorder) AS enum_labels
FROM pg_catalog.pg_type t
JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
JOIN pg_catalog.pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'mood'
GROUP BY 1,2;
zwraca
"public";"mood";"sad|ok|excited|happy"
Dane są prezentowane w zwartej formie z zachowaniem kolejności zestawu ,dzięki
ORDER BY e.enumsortorder
Możliwa, lecz kłopotliwa zmiana typu . Związana z koniecznością aktualizacji danych w kolumnie.
CREATE TYPE test AS enum('a', 'b');
CREATE TABLE foo (bar test);
INSERT INTO foo VALUES ('a'), ('b');
ALTER TABLE foo ALTER COLUMN bar TYPE varchar;
DROP TYPE test;
CREATE TYPE test as enum('a', 'b', 'c');
ALTER TABLE foo ALTER COLUMN bar TYPE test
USING CASE
WHEN bar = ANY (enum_range(null::test)::varchar[])
THEN bar::test
WHEN bar = ANY ('{convert, these, values}'::varchar[])
THEN 'c'::test
ELSE NULL
END;
