i danych
Tomasz Borzyszkowski
PostgreSQL obsługuje standardowy zestaw typów danych standardu SQL oraz kilka specyficznych typów. Typy danych dostępne w PostgreSQL możemy podzielić następująco:
Logiczne (Boolean)
Znakowe
Numeryczne
Daty i czasu
Dodatkowe typy PostgreSQL
Typ BLOB (duże obiekty binarne)
Przyjrzymy się każdemu z powyższych typów danych, zwracając szczególną uwagę na typy niestandardowe.
2
Typ Boolean jest najprostszym typem. Przechowuje on dwie wartości: True (prawda) oraz False (fałsz), a także NULL dla wartości nieznanej.
Wartości interpretowane jako True: 'TRUE' , '1' , 'yes' , 'y' ,
'true' , 't'
Wartości interpretowane jako False: 'FALSE' , '0' , 'no' ,
'n' , 'false' , 'f'
Wszystkie inne wartości, prócz NULL, będą odrzucone. PostgreSQL
nie zapamiętuje frazy użytej do określenia wartości logicznej lecz tylko wynik konwersji frazy na wartości ' t' i ' f'.
Zobacz plik: bool.sql
3
W PostgreSQL istnieją trzy rodzaje typów znakowych:
CHAR pojedynczy znak
Ciągi znaków o ograniczonej długości:
CHAR(N) ciąg złożony dokładnie z N znaków, które są uzupełniane spacjami do N znaków. W przypadku próby zapisu zbyt długiego ciągu znaków otrzymamy komunikat o błędzie, zapis taki zostanie odrzucony
VARCHAR(N) ciąg o maksymalnej długości N znaków. Ciągi tego typu nie są uzupełniane. Przepełnienia jw.
TEXT ciągi znaków o nieograniczonej długości. Jest to typ niestandardowy. Jego ograniczeniem jest Postgresowy limit wielkości linii (standardowo ok. 8 kB, chyba że zmienimy limit w źródłach i przekompilujemy Postgresa) Zobacz plik: znaki.sql 4
Zobacz plik: liczby.sql
Typy całkowite:
SMALLINT rozmiar: 2 bajty; zakres: -32 768 do 32 767
INT rozmiar: 4 bajty; zakres: -2 147 483 do 2 147 647
SERIAL jak INT ale od 0, z tą różnicą, że wartość jest nadawana automatycznie przez PostgreSQL
Typy zmiennoprzecinkowe:
FLOAT(N) liczba z precyzją do co najmniej N znaków, przechowywana za pomocą maksimum 8 bajtów
REAL liczba podwójnej precyzji (8 bajtów)
NUMERIC(P,S) liczba rzeczywista o P cyfrach, w tym S po przecinku; inaczej niż FLOAT jest ona zawsze dokładną wartością ale praca z nią jest mniej efektywna niż z innymi liczbami
MONEY to alternatywna nazwa dla NUMERIC(9,2); typ specyficzny dla PostgreSQL, chociaż popularny także w innych bazach danych
5
Zobacz plik: sequence.sql
Typ SERIAL reprezentuje liczby takie jak INT, z tym, że automatycznie zwiększa się o 1, co umożliwia łatwe tworzenie unikalnych identyfikatorów dla każdego wiersza. Z kolumnami typu SERIAL kojarzone są odpowiednie liczniki o nazwie zbudowanej wg schematu:
< nazwaTabeli>_< kolumna>_seq Licznik taki możemy łatwo wprowadzić w błąd wprowadzając zawartość do kolumny typu SERIAL. Dlatego:
Należy unikac wprowadzania wartości do takich kolumn Operacje na licznikach:
currval('seqName') bieżąca wartość numeru sekwencji
nextval('seqName') zwiększa numer sekwencji a następnie oddaje jego wartość
setval('seqName',num) ustawia seqName na num 6
Zobacz plik: daty.sql
Typ
Znaczenie
DATE
przechowuje informacje o dacie
TIME
przechowuje informacje o czasie
TIMESTAMP
przechowuje informacje o dacie i czasie
INTERVAL
informacje o różnicy między danymi typu TIMESTAMP
Można wpływać na styl daty w PostgreSQL. Służy do tego komenda: SET DATESTYLE TO 'warto??'
Wartość
Znaczenie
Przykład
US
miesiąc przed dniem
02/13/1997
European
dzień przed miesiącem
13/02/1997
---------------------------------------------------------------------------------
ISO
znak - jako separator
1997-02-13
SQL
styl tradycyjny
02/13/1997
Postgres
styl domyślny
Sat Feb 01
German
styl niemiecki
13.02.1997
7
Zobacz plik: tablice.sql
Możliwość przechowywania tablic jako elementów kolumn nie jest przewidziana w standardzie języka SQL, jednak dodano ją do PostgreSQL. Deklaracja kolumny typu tablicowego odbywa się przez dodanie nawiasów kwadratowych [] po nazwie typu. Przykład: CREATE TABLE tictactoy( squares integer [3][3] );
Powyższa tabela definiuje jedną kolumnę, będącą tablicą dwuwymiarową. Ograniczenia wielkości tablic nie są jeszcze zaimplementowane i powyższe jest równoważne definicji bez podania ograniczeń.
Tablice są, inaczej niż we większości języków programowania, indeksowane od 1. Wartości wstawiane do tablic należy ująć w nawiasy {} oraz w pojedyncze apostrofy.
8
Czasem zachodzi konieczność konwersji pomiędzy typami w bazie danych. Należy jednak uważać, ponieważ zbyt duża ich liczba świadczy o nieprawidłowym projekcie.
Operacji konwersji typów dokonuje się stosując następującą składnię:
CAST(nazwa_kol AS typ_na_który_konwertujemy)
Alternatywna składnia:
nazwa_kol :: typ_na_który_konwertujemy
Przykład:
SELECT CAST(date_placed AS CHAR(10))
FROM orderinfo;
Patrz plik baza.sql z poprzeniej serii slajdów.
9
W każdej bazie danych prócz informacji merytorycznych przechowywane są także pewne informacje pochodzace z systemu operacyjnego. Są one często związane z monitorowaniem dostępu do danych. Zmiennymi tymi są:
CURRENT_DATE bieżąca data
CURRENT_TIME bieżący czas
CURRENT_TIMESTAMP bieżący czas i data
CURRENT_USER użytkownik czytający zmienną
Z powyższych zmiennych można korzystać jak z nazw kolumn w tabeli:
SELECT item_id, quantity, CURRENT_TIMESTAMP
FROM stock;
lub bez tabeli:
SELECT CURRENT_USER, CURRENT_TIME;
10
Zawsze, gdy wprowadzamy dane do tabel PostgreSQL, odpowiada on liczbą (wyglądającą na przypadkową), oraz liczbą
wprowadzonych wierszy. Pierwsza z tych liczb oznacza
wewnętrzny, zwykle ukryty numer referencyjny (OID), który PostgreSQL zapisuje dla każdego wiersza.
W większości relacyjnych baz danych nie ma kolumny OID lub nie jest ona dostępna dla użytkownika. W PostgreSQL możemy ją zobaczyć, także jej zawartość, używając instrukcji:
SELECT oid, fname, lname FROM customer;
W prawidłowo zaprojektowanej bazie danych, z dobrze
skonstruowanymi kluczami podstawowymi, nie powinno być
potrzeby korzystania z OID. Pomimo tego, że PostgreSQL daje taką możliwość, korzystanie z tej kolumny we własnym projekcie świadczy o złym stylu programowania.
11
Zobacz plik: inherits.sql
W PostgreSQL sładnia tworzenia tabel jest następująca:
CREATE [TEMPORARY] TABLE nazwa_tabeli (
{nazwa_kol typ [ograniczenia-dla-kol][,...]}
[CONSTARAINT ogranicznia-dla-tabeli]
) [INHERITS (nazwa-istniej?cej-tabeli)]
Większość elementów definicji tabeli jest już znana. W PostgreSQL
istnieje możliwość dziedziczenia z istniejących tabel oraz ustawiania dodatkowych opcji dla kluczy obcych.
INHERITS umożliwia tworzenie nowej tabeli, dziedziczącej kolumny z już istniejących tabel. Nowa tabela zawiera wszystkie kolumny znajdujące się w tabelach wymienionych po słowie INHERITS.
Tabele tymczasowe są bardzo wygodne do przechowywania
pośrednich wyników obliczeń. Tabele zdefiniowane jako
TEMPORARY są automatycznie usuwane wraz z końcem sesji lub zakończeniem połączenia.
12
Czasem zachodzi konieczność aktualizacji wartości kluczowych, do których odwołują się jakieś klucze obce. W PostgreSQL można sobie z tym poradzić na kilka sposobów:
DEREFERABLE powoduje zmianę realizacji ograniczeń kluczy obcych. W normalnych warunkach PostgreSQL sprawdza, czy są spełnione ograniczenia kluczy obcych. Z DEREFERABLE
PostgreSQL pozwala naruszyć integralność kluczy obcych ale tylko wewnątrz transakcji.
Przykład:
CREATE TABLE orderinfo (
order_id
serial,
........
CONSTARAINT klucz_obcy FOREIGN KEY(customer_id)
REFERENCES customer(customer_id) DEREFERABLE
);
13
Zobacz plik: obce.sql
Alternatywne rozwiązanie polega na zdefiniowaniu reguł dla ograniczeń kluczy obcych, dotyczących sposobów obsługi naruszeń ograniczeń w dwóch okolicznościach:
ON DELETE podczas kasowania
ON UPDATE podczas aktualizacji
Z każdym z powyższych można stosować słowo kluczowe:
CASCADE dla uzyskania kaskadowego efektu zmiany w tabeli z kluczem podstawowym, lub
SET NULL aby ustawić wartość kolumny na NULL, ponieważ kolumna nie wskazuje już tabeli podstawowej
Opcje CASCADE i SET NULL można łączyć. Można więc pisać: ON UPDATE SET NULL ON DELETE CASCADE
14