Rozdzial 3 PostgreSQL

PostgreSQL jest prawdopodobnie najbardziej rozbudowan¹ oraz oferuj¹c¹ najwiêksze
mo¿liwoci baz¹ danych typu open source. Autorzy ksi¹¿ki — John C. Worsley oraz D.
Drake — opracowali ten praktyczny przewodnik po PostgreSQL, wykorzystuj¹c swoje
wieloletnie dowiadczenie w projektowaniu rozwi¹zañ opartych na tym systemie.

W pocz¹tkowych rozdzia³ach tej ksi¹¿ki przedstawiono podstawowe zagadnienia
dotycz¹ce PostgreSQL. Czytelnik zostanie przeprowadzony przez proces pobierania
PostgreSQL oraz jego instalacji przez u¿ytkownika. Opisano psql — klienta PostgreSQL
wykorzystuj¹cego interfejs wiersza poleceñ, a tak¿e omówiono wszystkie podstawowe
w³aciwoci jêzyka SQL obs³ugiwane w PostgreSQL.

W dalszych rozdzia³ach zaprezentowano zaawansowane zagadnienia, takie jak:

•

Zastosowanie JDBC w celu korzystania z baz danych PostgreSQL z poziomu

programów napisanych w jêzyku Java

•

Zastosowanie jêzyka PL/pgSQL do pisania procedur wyzwalanych oraz procedur

sk³adowanych

•

Instalacja, konfiguracja oraz wykorzystanie LXP -- narzêdzia s³u¿¹cego do

wspomagania projektowania witryn WWW z wykorzystaniem PostgreSQL

Ponadto poznamy sposób tworzenia kopii zapasowych oraz odtwarzania bazy danych
PostgreSQL; dowiemy siê, jak zarz¹dzaæ kontami u¿ytkowników, tworzyæ nowe bazy
danych, jak uruchamiaæ i zatrzymywaæ bazy danych, a tak¿e w jaki sposób wykonywaæ
inne zadania administratora baz danych. „PostgreSQL. Praktyczny przewodnik” koñczy
siê kompletnym opisem instrukcji PostgreSQL.

5RKUVTGħEK

Część I Wstęp i instalacja ...............................................................13

Rozdział 1.

Darmowa wersja Open Source ................................................................................................ 15

Zestaw funkcji PostgreSQL..................................................................................................... 17

Gdzie szukać dodatkowych informacji.................................................................................... 19

Rozdział 2.

Przygotowanie do instalacji..................................................................................................... 21

10 kroków instalacji PostgreSQL ............................................................................................ 25

Część II Korzystanie z PostgreSQL................................................41

Rozdział 3. ! "#

Wprowadzenie do SQL............................................................................................................ 43

Wprowadzenie do teorii relacyjnych baz danych.................................................................... 45

Instrukcje SQL......................................................................................................................... 48

Typy danych ............................................................................................................................ 66

Tabele w PostgreSQL .............................................................................................................. 91

Rozdział 4. $ %

Wstęp do psql .......................................................................................................................... 95

Zastosowanie tabel................................................................................................................... 99

Wprowadzanie danych za pomocą poleceń INSERT oraz COPY ........................................ 109

Pobieranie wierszy za pomocą instrukcji SELECT............................................................... 114

Aktualizacja wierszy za pomocą instrukcji UPDATE .............................................................. 144

Usuwanie wierszy za pomocą instrukcji DELETE ............................................................... 147

Wykorzystanie zapytań podrzędnych .................................................................................... 148

Zastosowanie perspektyw...................................................................................................... 150

Inne zastosowania SQL ......................................................................................................... 153

Rozdział 5. & '()

Operatory ............................................................................................................................... 155

Funkcje................................................................................................................................... 171

Rozdział 6. * #

Klient psql: zaawansowana problematyka ............................................................................ 213

PgAccess: Klient z interfejsem graficznym........................................................................... 229

Rozdział 7. $+ , #

Indeksy................................................................................................................................... 237

Zaawansowane techniki operacji na tabelach........................................................................ 242

Tablice ................................................................................................................................... 253

Automatyzacja popularnych procedur................................................................................... 259

Transakcje i kursory .............................................................................................................. 270

Rozszerzanie możliwości PostgreSQL .................................................................................. 277

Część III Administracja PostgreSQL .......................................... 287

Rozdział 8. - ' .%

Uwierzytelnianie klienta........................................................................................................ 289

Sesje szyfrowania .................................................................................................................. 300

Rozdział 9. $! /!0 #

Uruchamianie i zatrzymywanie PostgreSQL ........................................................................ 311

Inicjowanie systemu plików .................................................................................................. 319

Tworzenie i usuwanie bazy danych....................................................................................... 323

Utrzymanie bazy danych ....................................................................................................... 329

Archiwizacja i odtwarzanie danych....................................................................................... 335

Rozdział 10. $! (+) ) ( (+) )1 #"%

Zarządzanie użytkownikami.................................................................................................. 349

Zarządzanie grupami ............................................................................................................. 359

Nadawanie uprawnień ........................................................................................................... 363

Część IV Programowanie w PostgreSQL .................................... 369

Rozdział 11. 2#

Dodanie obsługi języka PL/pgSQL do bazy danych ............................................................. 371

Struktura języka ..................................................................................................................... 373

Zastosowanie zmiennych....................................................................................................... 378

Sterowanie wykonaniem programu ....................................................................................... 390

PL/pgSQL a procedury wyzwalane ....................................................................................... 406

Rozdział 12. 345 "6%

Konfigurowanie sterownika JDBC PostgreSQL ................................................................... 410

Zastosowanie sterownika PostgreSQL .................................................................................. 410

Zastosowanie JDBC............................................................................................................... 413

Zagadnienia specyficzne dla PostgreSQL i JDBC ................................................................ 419

Rozdział 13. 7 "

Dlaczego wykorzystywać LXP?............................................................................................ 421

Podstawowe właściwości....................................................................................................... 422

Instalacja i konfiguracja LXP ................................................................................................ 423

Podstawowe informacje dotyczące znaczników LXP ........................................................... 429

Zmienne i obiekty LXP ......................................................................................................... 431

Wykorzystanie znaczników kontekstu klienta w LXP............................................................ 434

Analiza składni znacznika ..................................................................................................... 436

Logika sterowania wykonaniem ............................................................................................ 440

Pętle iteracyjne....................................................................................................................... 445

Włączanie zawartości ............................................................................................................ 446

Wyświetlanie znaczników zewnętrznych za pomocą znacznika <xtag> .............................. 458

Część V Opis poleceń ...................................................................461

Rozdział 14. & 8 "9#

Dodatki .........................................................................................471

Dodatek A :) / #

Dodatek B &($54

Dodatek C 5 ' () &; %

Dodatek D < =.

) .

Ten rozdział opisuje historię języka SQL i podstawowe pojęcia z nim związane; zawiera także
podstawy dla następnego rozdziału, który dotyczyć będzie zastosowania języka SQL w Postgre-
SQL. Omówiono tu podstawy relacyjnego modelu baz danych, rozszerzenia baz danych zoriento-
wanych obiektowo, strukturę instrukcji SQL oraz przedstawiono ogólny opis typów danych, ope-
ratorów i funkcji obsługiwanych przez PostgreSQL.

SQL, Strukturalny Język Zapytań (ang. Structured Query Language), jest dojrzałym, potężnym
i uniwersalnym relacyjnym językiem zapytań. Początki SQL sięgają badań rozpoczętych przez
IBM w 1970 r. W kilku następnych podrozdziałach omówimy historię SQL, jego poprzedników
oraz różnych rozwijanych przez lata standardy SQL.

Model relacyjny, stanowiący w dużej mierze podstawę założeń koncepcyjnych SQL, został formal-
nie zdefiniowany w 1970 r. przez dr. E. F. Codda, naukowca IBM, w dokumencie zatytułowanym
„Relacyjny model danych dla dużych współdzielonych banków danych” (ang. A Relational Model of
Data for Large Shared Data Banks). Artykuł ten wzbudził wielkie zainteresowanie zarówno dzięki
możliwościom realizacji, jak i praktycznemu zastosowaniu komercyjnemu takiego systemu.

W 1974 r. IBM rozpoczął projekt „System/R” i dzięki pracom Donalda Chamberlina i innych
opracował SEQUEL, Strukturalny Angielski Język Zapytań (ang. Structured English Query Lan-
guage). System/R został po raz pierwszy zaimplementowany przez IBM w latach 1974 – 75,
w programie nazwanym SEQUEL-XRM. W latach 1976 – 77 został on całkowicie zmieniony, tak,
aby umożliwić jednoczesną pracę wielu użytkownikom oraz wykorzystywania wielu tabel. Po we-
ryfikacji system został na krótko nazwany „SEQUEL/2”, a następnie z powodów prawnych zmie-
niono jego nazwę na „SQL”.

W 1978 r. rozpoczęto metodyczne testowanie prowadzone przez klientów. Testowanie to, demon-
strując zarówno użyteczność, jak i praktyczność systemu, okazało się sukcesem firmy IBM. W wy-
niku tego IBM rozpoczął rozwijanie produktów komercyjnych, które implementowały SQL bazując
na prototypie System/R — między innymi SQL/DS (wprowadzony w 1981 r.) oraz DB2 (z 1983 r.).

Kilku innych producentów oprogramowania (między innymi Oracle) zaakceptowało powstanie
modelu relacyjnego i stworzyło produkty bazujące na SQL (firma Oracle faktycznie wyprzedziła
IBM, wprowadzając na rynek dwa lata wcześniej — w 1979 r. — pierwszy komercyjny system
zarządzania relacyjną bazą danych —SZBD) — Sybase i Ingres (oparty na projekcie Ingres, opra-
cowanym przez Uniwersytet Kalifornijski w Berkeley).

Nazwa PostgreSQL jest, jak można odgadnąć, grą słów związaną z nazwą Ingres. Zarówno
PostgreSQL, jak i Ingres sięgają swoimi korzeniami do systemu Ingres, opracowanego przez
Uniwersytet Kalifornijski w Berkeley.

SQL bazuje głównie na algebrze relacyjnej i rachunku relacyjnym krotki. Algebra relacyjna,
wprowadzona w 1972 r. przez E.F. Codda, dostarczyła podstawowych pojęć przetwarzania składni
SQL. Jest ona proceduralnym sposobem tworzenia zapytań sterowanych danymi i odnosi się do
logiki jak zapytania strukturalnego. Z drugiej strony, rachunek relacyjny krotki (TRC, ang. tuple
relational calculus) wpływa na odpowiedni wygląd SQL. Rachunek relacyjny wykorzystuje wyra-
żenia deklaracyjne odnosząc się do logiki co zapytania strukturalnego.

Język SQL, oprócz właściwości, które implementują funkcje będące zaledwie częścią algebry lub
rachunku relacyjnego, zawiera funkcje dodatkowe. Należą do nich:

Obsługa wstawiania, modyfikacji i usuwania danych

Użytkownicy mogą wstawiać, usuwać i modyfikować zapisane rekordy danych.

Operatory arytmetyczne

Dozwolone są operacje arytmetyczne, takie jak dodawanie, odejmowanie, mnożenie i dzielenie
(na przykład (wartość1 * 5) + wartość2), jak również porównywanie za pomocą
operatorów (na przykład wartość3 >= wartość4).

Wyświetlanie danych

Użytkownicy mogą wyświetlać związki generowane przez zapytania (takie jak zawartości tabel).

Przypisanie (Aliasy)

Zamiast wymuszonego użycia domyślnej nazwy relacji, użytkownicy mogą zmienić nazwę
relacji będącej wynikiem zapytania. Nazwa ta, zależnie od zapytania, może pochodzić od nazwy
kolumny lub funkcji.

Funkcje agregacji (grupowe)

Użytkownicy mogą grupować powiązane wiersze i obliczać na ich podstawie średnie, sumy,
zliczenia, wartości maksymalne oraz wartości minimalne.

ANSI — Amerykański Narodowy Instytut Standardów (ang. American National Standards Insti-
tute) — znormalizował język SQL w 1986 r. (nr normy X3.135) a ISO — Międzynarodowa Orga-
nizacja Standardów (ang. International Standards Organization) — dokonała normalizacji tego
języka w 1987 r. FIPS — Federalny Standard Przetwarzania Informacji (ang. Federal Information
Processing Standard) rządu Stanów Zjednoczonych zaadoptował standard ANSI/ISO. W 1989 r.
opublikowany został zweryfikowany standard, znany powszechnie jako SQL89 lub SQL1.

Większą część standardu SQL89, między innymi ze względu na sprzeczne interesy producentów
komercyjnych, pozostawiono niekompletną, a wiele właściwości zdefiniowano jako zależne od
implementacji. W celu wzmocnienia standardu komitet ANSI dokonał przeglądu swoich poprzed-
nich prac i ratyfikował w 1992 r. standard SQL92 (nazywany również SQL2). Standard ten zajął
się kilkoma słabymi punktami SQL89 i określił koncepcyjne właściwości SQL, które w tamtym
czasie przekraczały możliwości jakiejkolwiek istniejącej implementacji SZBD. W rzeczywistości,
definicja standardu SQL92 była w przybliżeniu sześciokrotnie obszerniejsza od swojego poprzed-
nika. Wynikiem tej niewspółmierności było zdefiniowanie przez autorów trzech poziomów zgod-
ności z SQL92: wejściowy SQL (ang. Entry-level conformance) — zawierał zaledwie minimalne
ulepszenia względem SQL89; pośredni SQL (ang. Intermediate-level conformance) — stanowił
ogólnie osiągalny zbiór zasadniczych udoskonaleń oraz pełny SQL (ang. Full conformance)
— oferował pełną zgodność z właściwościami SQL92.

W 1999 r. ANSI/ISO opracowały standard SQL99 (nazywany również SQL3). Standard ten obej-
muje niektóre bardziej zaawansowane i poprzednio ignorowane obszary współczesnych systemów
SQL, takie jak koncepcje obiektowo-relacyjnej bazy danych, interfejsy poziomu wywołania oraz
zarządzanie integralnością. SQL99 zastępuje poziomy zgodności określane przez SQL92 swoimi
własnymi stopniami zgodności: Podstawowym SQL99 (ang. Core SQL99) i Zaawansowanym
SQL99 (ang. Enhanced SQL99).

Obecnie PostgreSQL jest w większości zgodny ze standardem poziomu wejściowego, jak również
z wieloma właściwościami poziomów pośredniego i pełnego. Dodatkowo, wiele nowych właści-
wości w SQL99 wykazuje podobieństwo do obiektowo-relacyjnych pojęć, pierwotnie wprowa-
dzonych przez PostgreSQL (są to na przykład tablice, funkcje i dziedziczenie).

PostgreSQL jest zaawansowanym technicznie Systemem Zarządzania Obiektowo-Relacyjną Bazą
Danych (ang. Object-Relational Database Management System — ORDBMS). Systemy ORDBMS
stanowią rozszerzenie bardziej tradycyjnych systemów RDBMS — Systemów Zarządzania Rela-
cyjnymi Bazami Danych (ang. Relational Database Management Systems). System RDBMS
umożliwia użytkownikom zapisywanie powiązanych ze sobą danych w dwuwymiarowych struktu-
rach danych nazywanych tabelami. Dane te mogą składać się z wielu zdefiniowanych typów, ta-
kich jak liczby całkowite, liczby zmiennoprzecinkowe, ciągi znakowe oraz znaczniki czasu. Dane
wstawiane do tabeli są porządkowane za pomocą przypominającego siatkę systemu pionowych ko-
lumn i poziomych wierszy. Model relacyjny został zbudowany na solidnych podstawach pojęciowej
prostoty, która prawdopodobnie stanowi zarówno jego największą zaletę, jak i największą wadę.

Aspekt obiektowo-relacyjny PostgreSQL dodaje wiele ulepszeń do prostego, relacyjnego modelu
danych. Należy do nich między innymi obsługa tablic (wiele wartości w pojedynczej kolumnie),
dziedziczenie (związek tabela macierzysta — tabela potomna) oraz funkcje (metody programowe
wywoływane przez instrukcje SQL). Dla zaawansowanych programistów PostgreSQL oferuje na-
wet rozszerzalność swoich typów danych oraz języków proceduralnych.

Z powodu tej obiektowo-relacyjnej koncepcji tabele są często nazywane klasami, podczas gdy do
wierszy i kolumn można odwoływać się odpowiednio jako do przykładów obiektów i atrybutów
obiektów. W naszej książce terminologia ta używana będzie zamiennie. Do innych struktur danych
SQL, takich jak wskaźniki i widoki, można się odwoływać tak, jak do obiektów bazy danych.

Zauważmy, że określenie obiektowo-relacyjny nie jest jednoznaczne z terminem obiektowo
zorientowany, odnoszącym się do wielu nowoczesnych języków programowania. Chociaż
PostgreSQL obsługuje kilka ulepszeń obiektowych modelu relacyjnego, to jednak nadal
właściwym jest uważanie PostgreSQL za przykład Systemu Zarządzania Relacyjnymi
Bazami Danych (SZBD).

Wprawdzie PostgreSQL jest powszechnie uznawany za system zarządzania bazami danych lub
„bazę danych”, jednak prawdopodobnie brak jest powszechnego zrozumienia tego, co konkretnie
oznacza termin baza danych. Baza danych w PostgreSQL jest obiektowo-relacyjną implementacją
tego, co w SQL99 nazywane jest formalnie schematem.

Baza danych jest po prostu zapisanym zbiorem danych, które są wzajemnie logicznie powiązane.
Zazwyczaj dane te są dostępne w środowisku wielodostępnym. Powyższe uwagi dotyczą także Po-
stgreSQL, chociaż istnieją dobrze zdefiniowane prawa i ograniczenia związane ze wspomnianym
dostępem.

Być może nie wszyscy wiedzą, że system PostgreSQL może jednocześnie zarządzać kilkoma ba-
zami danych, z których każda posiada swojego właściciela oraz zawiera unikalne tabele, widoki,
wskaźniki, sekwencje i funkcje.

W celu utworzenia tabeli, funkcji lub dowolnego innego obiektu bazy danych należy uzyskać po-
łączenie z konkretną bazą danych poprzez klienta PostgreSQL. Po uzyskaniu połączenia można
utworzyć obiekt, który jest wówczas obiektem bazy, z którą uzyskano połączenie, a zatem jest
niedostępny z jakiejkolwiek innej bazy danych (chociaż klient może posiadać otwarte połączenia
z kilkoma różnymi bazami danych).

Tworząc podstawowe obiekty bazodanowe w osobnych bazach danych zmniejsza się ryzyko po-
wstawania konfliktu nazw przy wybieraniu nazwy tabeli, którą już wybrano w innym celu (na
przykład gdy dwóch użytkowników chce wykorzystać w dwóch oddzielnych aplikacjach tabelę
nazywaną products). Jest tak, ponieważ żadna z baz danych nie zawiera informacji o elemen-
tach innej bazy danych i nie podejmie próby utworzenia jakiegokolwiek związku logicznego po-
między nimi. Ponadto, ponieważ ta sama zasada ma zastosowanie do obiektowo-relacyjnych
obiektów danych, użytkownicy w swojej bazie danych mogą nawet tworzyć funkcje i definicje ję-
zyka, które są niedostępne dla innych użytkowników połączonych z innymi bazami danych dzia-
łającymi w PostgreSQL.

Domyślnie PostgreSQL instaluje tylko jedną funkcjonalną bazę danych, o nazwie template1.
Nazwa ta wskazuje na szablonowy charakter tej bazy. Każda baza danych utworzona później niż
template1

jest zasadniczo kopią zawierającą wszystkie obiekty tej bazy danych, między innymi

strukturę tabeli, funkcje, języki itd. Dosyć często dla nowych użytkowników PostgreSQL tworzy
się domyślne bazy danych o takiej samej nazwie, jak ich nazwa użytkownika PostgreSQL. Robi
się tak dlatego, ponieważ w przypadku, gdy nazwa bazy danych nie jest określona, PostgreSQL
próbuje połączyć się z bazą danych o takiej samej nazwie, jak nazwa użytkownika uzyskującego
połączenie.

Tabele są prawdopodobnie najważniejszym pojęciem SQL, które należy dokładnie zrozumieć. To
w nich właśnie znajdują się wszystkie dane. Posiadanie gruntownej wiedzy na temat tabel jest
podstawowym warunkiem poprawnego planowania i projektowania struktur danych SQL oraz
programowych procedur uzyskiwania dostępu do tych danych i wykorzystywania ich.

Tabela składa się z kolumn i wierszy, a ich przecięcia tworzą pola. Dla czytelników, którzy wcze-
śniej wykorzystywali arkusze kalkulacyjne (na przykład Excel) można dodać, że obydwa te
obiekty są graficznie przedstawiane w taki sam sposób, a pola w tabeli są odpowiednikami komó-
rek w arkuszu kalkulacyjnym. Ogólnie mówiąc, kolumny w tabeli opisują nazwę i typ danych od-
najdywanych (lub wprowadzanych) w tej kolumnie w każdym wierszu. Wiersze w tabeli repre-
zentują rekordy złożone z pól opisanych od lewej do prawej przez nazwy i typy odpowiadających
im kolumn. Każde pole w wierszu jest pośrednio związane z pozostałymi polami w tym samym
wierszu. W tym sensie kolumny mogą być uznawane za deskryptory poszczególnych sekwencyj-
nych elementów wiersza, a każdy wiersz może być uznawany za zapisany rekord odpowiadający
temu opisowi.

W tabeli 3.1 przedstawiono prostą tabelę books, wykorzystywaną w wymyślonej przez nas księ-
garni Book Town. W zamieszczonych dalej przykładach będziemy często odwoływać się do tej
tabeli. Każdy z jej zapisanych rekordów opisuje książkę za pomocą identyfikatora numerycznego,
tytułu, identyfikatora autora oraz identyfikatora tematu. Charakterystyki te są opisane — od lewej
do prawej — za pomocą kolumn tabeli (id, title, author_id oraz subject_id).

Tabela 3.1. Przykładowa tabela SQL

title

author_id

subject_id

7808

The Shining

4156

156

The Tell-Tale Heart

4513

Dune

1866

4267

2001: A Space Odyssey

2001

1608

The Cat in the Hat

1809

1590

Bartholomew and the Oobleck

1809

Jak można zauważyć, tabela jest opisana za pomocą czterech kolumn, w ustalonym porządku
— od lewej do prawej. Tabelę aktualnie wypełnia sześć wierszy (określanych także jako krotki
albo rekordy). Ważne jest, aby zrozumieć, że w relacyjnej bazie danych tabela posiada ustalony
porządek kolumn, natomiast same wiersze są właściwie nieuporządkowane. Nieco później (ponieważ

strukturę zapytania SQL przedstawiono rozdziale 4., „Zastosowanie SQL w PostgreSQL”) prze-
konamy się, że w języku SQL istnieją sposoby porządkowania pobranych wierszy. Jednakże
w samej bazie danych wiersze nie są automatycznie porządkowane w żaden konsekwentny i prze-
widywalny sposób. Kiedy wynik zapytania SQL ma zawierać uporządkowane dane, należy do-
kładnie rozważyć sposób uporządkowania rekordów i jawnie je określić.

Każda tabela musi posiadać przynajmniej jedną kolumnę, ale tabele mogą czasami nie zawierać
żadnych wierszy, ponieważ każda pionowa kolumna odpowiada względnie stałemu atrybutowi da-
nych przedstawianych w tej tabeli (jak kolumna title w tabeli books w poprzednim przykła-
dzie). Jeśli nie istniałaby żadna kolumna, zawartości wierszy byłyby wieloznaczne, podczas gdy
brak wierszy w tabeli po prostu oznacza brak danych. W PostgreSQL 7.1 tabela może zawierać
maksymalnie 1600 kolumn oraz nieograniczoną liczbę wierszy (tzn. ograniczenia są związane je-
dynie z możliwościami sprzętowymi, takimi jak pojemność dysku).

W tabeli 3.1 nazwy kolumn dosyć wyraźnie wskazują znaczenie każdej kolumny. Decyzja doty-
cząca nazewnictwa kolumn jest raczej dowolna, chociaż — w celu uniknięcia wieloznaczności
należy z uwagą dobierać nazwy i konwencje nazw dla tabel.

Każda kolumna tabeli posiada skojarzony z nią typ danych, chociaż być może nie jest to natych-
miast oczywiste. Typ danych kolumny pomaga dokładniej opisać rodzaj informacji, jaką ona za-
wiera, ale jednocześnie ogranicza rodzaj danych, które mogą być wstawione do kolumny. Na
przykład kolumna author_id jest typu integer, co oznacza, że jakiekolwiek próby wstawie-
nia danych nie będących liczbą całkowitą (na przykład 110a) nie powiodą się. Typy danych zo-
stały opisane dokładniej w podrozdziale pod tytułem „Typy danych”.

W obecnym podrozdziale wprowadzono ogólne pojęcia logicznej organizacji danych w relacyj-
nych bazach danych i w tabelach; w następnym wyjaśnimy, dlaczego instrukcje stanowią podsta-
wę wszystkich interakcji z bazą danych.

Pojęcia dotyczące relacyjnych baz danych i tabel będą oczywiście dla nas zupełnie niejasne, jeżeli
nie będziemy mieli pojęcia o tym, w jaki sposób bezpośrednio modyfikować te dane. SQL składa
się ze strukturalnych instrukcji, za pomocą których dodajemy, modyfikujemy i usuwamy wszyst-
kie dane w bazie danych. Instrukcje te tworzą podstawę komunikacji z serwerem PostgreSQL.

W tym podrozdziale dokonamy analizy anatomii instrukcji SQL. Podzielimy instrukcję na części
strukturalne, wyjaśniając znaczenie każdej z nich oraz ich wzajemne powiązania. Wyświetlanie
przykładowych instrukcji PostgreSQL jest możliwe za pomocą standardowego klienta wiersza
poleceń PostgreSQL — psql.

Nasze przykłady instrukcji SQL będą zwykle wykonywane w przykładowej bazie booktown
— bazie danych naszej wymyślonej księgarni, Book Town. Wynik działania instrukcji w psql
jest poprzedzany domyślnym znakiem zachęty, który wygląda następująco:

booktown=#

$%&

Niektóre prostsze przykłady, o ile nie są konkretnie związane z Book Town, mogą wykorzystywać
standardową, testową bazę danych testdb. Domyślnie znak zachęty psql wyświetla jedynie
nazwę bazy danych, z którą trwa połączenie oraz znaki =# wskazujące, że system jest gotowy do
przyjęcia nowego polecenia (zauważymy jednak, że symbol = będzie zmieniał się dynamicznie
w wyniku śledzenia przez psql stanu wejściowej instrukcji SQL). Dla zaznajomienia się z posta-
cią wyniku działania klienta psql będziemy wyświetlać ten znak zachęty wraz z wejściową in-
strukcją SQL oraz wynikiem jej działania.

Bardziej szczegółowo klienta psql opiszemy w rozdziale 4. W tym miejscu wspominamy o nim
jedynie po to, aby wyjaśnić źródło i styl zawartych w tej książce przykładów SQL.

Schemat bazy danych booktown (wraz z przykładowymi danymi) można znaleźć
w znajdującym się na dołączonej do książki płycie CD-ROM pliku booktown.sql. W celu
zainstalowania tej bazy danych należy w wierszu poleceń wpisać polecenie psql –U
postgres template1 –f /mnt/cdrom/booktown.sql

(gdzie /mnt/cdrom

jest ścieżką do zainstalowanego napędu CD, a postgres jest superużytkownikiem PostgreSQL).

Instrukcje SQL zawsze zaczynają się poleceniem (jest nim słowo lub grupa słów; opisują one, jaką
operację zainicjuje instrukcja). Polecenie można nazwać czasownikiem instrukcji SQL, ponieważ
opisuje ono operację, jaka będzie wykonywana. Instrukcje zwykle zawierają przynajmniej jedną
klauzulę; klauzule są formalnymi modyfikatorami, opisującymi bardziej szczegółowo funkcję in-
strukcji SQL.

W tabeli 3.2 zawarto wykaz najczęściej wykorzystywanych poleceń PostgreSQL.

Chociaż oczywiście SQL posiada cechy języków programowania, to jednak podczas jego projek-
towania brano pod uwagę zapewnienie jak największej łatwości wykorzystywania i czytelności.
W rezultacie instrukcje SQL często są bardzo podobne do zwykłych, stanowiących polecenia zdań
w języku angielskim. Istotną właściwością SQL jest to, że jego instrukcje informują serwer, jakie
dane ma znaleźć, a nie jak ma je znaleźć; tymczasem, gdybyśmy posłużyli się zwykłym językiem
programowania, podanie drugiej z wymienionych informacji byłoby konieczne. Czytanie dobrze
zaprojektowanego zapytania SQL powinno być prawie tak łatwe, jak czytanie zwykłego zdania.

W tekstach SQL słowo zapytanie jest używane często zamiennie ze słowem instrukcja.
W tej książce terminu zapytanie używa się tylko w odniesieniu do instrukcji, które zwracają
dane (na przykład instrukcji SELECT), a nie ogólnie do instrukcji SQL, które mogą zamiast
tego tworzyć, dodawać i modyfikować dane.

Poprawne utworzenie wszystkich obiektów bazy danych wymaga zainstalowania obsługi języka pgplsql.
Jeżeli obsługa tego języka nie zostanie automatycznie zainstalowana podczas wykonywania skryptu należy
usunąć nowoutworzoną bazę danych i samodzielnie zainstalować obsługę tego języka: createlang –d
template1 plpgsql

i ponownie uruchomić skrypt — przyp. red.

(

Tabela 3.2. Podstawowe polecenia PostgreSQL

Polecenie

Opis

CREATE DATABASE

Tworzy nową bazę danych

CREATE INDEX

Tworzy nowy indeks dla kolumny tabeli

CREATE SEQUENCE

Tworzy nową sekwencję w istniejącej bazie danych

CREATE TABLE

Tworzy nową tabelę w istniejącej bazie danych

CREATE TRIGGER

Tworzy nową definicję procedury wyzwalanej

CREATE VIEW

Tworzy nową perspektywę dla istniejącej tabeli

SELECT

Pobiera rekordy z tabeli

INSERT

Dodaje przynajmniej jeden nowy rekord do tabeli

UPDATE

Modyfikuje dane w istniejących rekordach tabeli

DELETE

Usuwa istniejące rekordy z tabeli

DROP DATABASE

Usuwa istniejącą bazę danych

DROP INDEX

Usuwa indeks kolumny z istniejącej tabeli

DROP SEQUENCE

Usuwa istniejący generator sekwencji

DROP TABLE

Usuwa istniejącą tabelę

DROP TRIGGER

Usuwa istniejącą definicję wyzwalacza

DROP VIEW

Usuwa istniejący widok tabeli

CREATE USER

Dodaje do systemu konto nowego użytkownika PostgreSQL

ALTER USER

Modyfikuje konto istniejącego użytkownika PostgreSQL

DROP USER

Usuwa konto istniejącego użytkownika PostgreSQL

GRANT

Przyznaje użytkownikowi uprawnienia dotyczące obiektu bazy danych

REVOKE

Odbiera uprawnienia użytkownika dotyczące obiektu bazy danych

CREATE FUNCTION

Tworzy nową funkcję SQL w bazie danych

CREATE LANGUAGE

Tworzy nową definicję języka w bazie danych

CREATE OPERATOR

Tworzy nowy operator SQL w bazie danych

CREATE TYPE

Tworzy nowy typ danych SQL w bazie danych

PostgreSQL interpretuje strukturalne instrukcje SQL jako sekwencję znaczników, zwykle roz-
dzielonych spacjami (lub znakami nowego wiersza) znajdującymi się poza apostrofami, chociaż
niektóre znaczniki mogą być umieszczone jako sąsiadujące ze sobą, o ile nie spowoduje to wielo-
znaczności (jak na przykład w sytuacji, kiedy operatory są umieszczone bezpośrednio obok identy-
fikatorów). W tym kontekście znacznik jest słowem lub znakiem, które mogą być zidentyfikowane
przez serwer w czasie przetwarzania lub interpretowania instrukcji SQL.

Technicznie każdy znacznik może być słowem kluczowym, identyfikatorem, identyfikatorem uję-
tym w apostrofy, stałą (nazywaną również literałem) lub jednym z kilku specjalnych symboli zna-
kowych. Słowa kluczowe to ciągi, które PostgreSQL rozpoznaje jako wyrazy z predefiniowanymi,
specyficznymi dla SQL lub PostgreSQL znaczeniami. Są to między innymi polecenia SQL, klau-
zule, nazwy funkcji oraz specjalne terminy zwiększające czytelność, które często towarzyszą
opcjonalnie poleceniom SQL (na przykład termin WORK w poleceniu COMMIT). Z kolei identyfi-
katory reprezentują nazwy zmiennych dla tabel, kolumn i innych obiektów baz danych.

$%&

)

W PostgreSQL zarówno słowa kluczowe, jak i identyfikatory odwołują się do wewnętrznie zdefi-
niowanych funkcji, wartości lub rekordów, natomiast stałe opisują fragmenty danych, które są in-
terpretowane dosłownie — mogą to być liczby lub ciągi znakowe.

Instrukcje SQL mogą zawierać specjalne symbole znakowe. Są to znaki zastrzeżone (na przykład
nawiasy okrągłe, średnik, nawiasy kwadratowe), które logicznie wpływają na znaczenie i roz-
mieszczenie słów kluczowych, identyfikatorów i literałów. Można je traktować jako znaki inter-
punkcji instrukcji SQL.

Operatory zaliczają się do kategorii specjalnych symboli znakowych. Mogą one być używane do
oznaczania operacji lub wyliczeń logicznych pomiędzy wartościami danych (literałami lub warto-
ściami reprezentowanymi przez identyfikatory); zazwyczaj posiadają rozmiar od jednego do czte-
rech znaków.

Bardziej szczegółowo właściwości tych podstawowych elementów SQL zostały opisane w kolej-
nych punktach.

!"#

Jak wspomnieliśmy w poprzednim punkcie, każdy element sekwencyjny instrukcji SQL uznawany
jest za znacznik. Warto zauważyć, że wszystkie znaczniki mogą znajdować się w tym samym
wierszu, bądź też mogą być rozmieszczone w kilku wierszach, gdyż analizator składni w Postgre-
SQL ignoruje dodatkowe spacje.

Rozważmy instrukcję SQL przedstawioną w przykładzie 3.1. Najpierw będzie ona wykonana
w pojedynczym wierszu, a następnie w dwóch oddzielnych. Obydwie instrukcje SELECT żądają
wyświetlenia przez system całej zawartości tabeli my_list.

Przykład 3.1. Spacje i znaki nowego wiersza

testdb=#

SELECT * FROM my_list;

todos
------------------------------------------------
Pick up laundry.
Send out bills.
Wrap up Grand Unifying Theory for publication.
(3 rows)

testdb=#

SELECT *

testdb-#

FROM

testdb-#

my_list;

todos
------------------------------------------------
Pick up laundry.
Send out bills.
Wrap up Grand Unifying Theory for publication.
(3 rows)

W przykładzie 3.1 pomiędzy znacznikami drugiej instrukcji występuje kilka znaków nowego
wiersza oraz kilka spacji. Jak widać, na podstawie identycznego wyniku, PostgreSQL ignoruje do-
datkowe znaki nowego wiersza i spacje, czyniąc obydwie instrukcje semantycznie równoważnymi.
Można skorzystać z tej właściwości, umieszczając długi ciąg znaczników w wielu wierszach i po-
prawiając w ten sposób czytelność instrukcji SQL. Prawdopodobnie nie jest to konieczne dla instrukcji

tak prostych, jak te podane w przykładzie 3.1, ale może okazać się pomocne dla złożonych in-
strukcji SQL, zawierających wiele klauzul, wyrażeń i warunków. W tej książce będziemy czasami
umieszczać niektóre instrukcje w kilku wierszach, aby łatwiej pokazać, jakie zadanie spełnia każ-
da część instrukcji.

$ #

Słowa kluczowe są zastrzeżonymi terminami SQL, które posiadają określone znaczenie syntak-
tyczne dla serwera. Typowymi słowami kluczowymi są na przykład INSERT, UPDATE, SELECT
i DELETE.

Wszystkie polecenia SQL są słowami kluczowymi, chociaż wiele słów kluczowych nie stanowi
pełnych poleceń. Na przykład polecenie INSERT INTO jest poprawnym poleceniem SQL, a sło-
wo INTO jest zastrzeżonym słowem kluczowym. Jednakże, jak można się domyślać, słowo INTO
— użyte bez kontekstu — nie posiada konkretnego znaczenia.

Jak już wspomniano, identyfikatory są nazwami zmiennych odnoszących się do obiektów bazy
danych. Nazwy te są dowolnie określane przez twórcę obiektu bazy danych w momencie tworze-
nia tego obiektu. W PostgreSQL obiektami, do których można odwoływać się przez identyfikato-
ry, mogą być bazy danych, tabele, kolumny, indeksy, perspektywy, sekwencje, reguły, procedury
wyzwalane lub funkcje.

W przykładzie 3.2 wprowadzono do prostej tabeli states trzy informacje dotyczące stanu Oregon.

Przykład 3.2. Słowa kluczowe i polecenia

booktown=#

INSERT INTO states VALUES (33, 'Oregon', 'OR');

INSERT 3389701 1

W przykładzie tym pokazano polecenie SQL INSERT INTO wykorzystujące słowa kluczowe
SQL INSERT, INTO i VALUES.

Polecenie INSERT INTO modyfikuje tabelę, do której następuje odwołanie poprzez identyfikator
states

. W tym przypadku modyfikacja polega na wstawieniu nowego rekordu.

Chociaż zazwyczaj nie jest to wymagane, jednak czasami identyfikatory należy ujmować w cu-
dzysłowy —oznacza to, że powinny być one interpretowane dosłownie. Na przykład, aby obejrzeć
wszystkie kolumny tabeli states, możemy skorzystać z prostej instrukcji następującej postaci:

booktown=#

SELECT * FROM states;

$%&

Słowami kluczowymi użytymi w tej instrukcji są SELECT i FROM, podczas gdy identyfikatorami
są gwiazdka * (wskazująca wszystkie kolumny) i states (nazwa tabeli). Za pomocą tego pole-
cenia wybieramy wszystkie kolumny z tabeli states, oglądając tym samym ich zawartość.

Ten sam efekt można osiągnąć za pomocą nieco innej instrukcji; tym razem identyfikator umie-
ścimy w cudzysłowach:

booktown=#

SELECT * FROM "states";

Jak można zauważyć, jeżeli w cudzysłowach umieścimy identyfikator zapisany małymi literami,
wynik będzie identyczny. Jednakże jeszcze inna instrukcja, w której w cudzysłowach umieszczono
identyfikator stAtEs, zakończy się niepowodzeniem:

booktown=#

SELECT * FROM "stAtEs";

ERROR: Relation 'stAtEs' does not exist

Instrukcja ta zakończy się niepowodzeniem, ponieważ informuje ona PostgreSQL, aby szukał tabeli
o dosłownej nazwie stAtEs (a nie states). Innymi słowy, poprzez wykorzystanie cudzysłowów
w instrukcji wyraźnie zażądano, aby PostgreSQL zinterpretował nazwę identyfikatora dosłownie.

Wszystkie identyfikatory nie będące identyfikatorami ujętymi w cudzysłowy są zwijane lub prze-
kształcane na zapisane małymi literami. Jeśli wpiszemy stAtEs lub STATES (tj. dowolną kom-
binację dużych i małych liter) bez cudzysłowów, PostgreSQL przed rozpoczęciem wykonywania
instrukcji automatycznie zamieni zapis identyfikatora na małe litery (states).

Przekształcenie identyfikatorów nie ujętych w cudzysłowy do nazw zapisanych małymi
literami jest konwencją specyficzną dla PostgreSQL. Standard SQL92 określa,
że identyfikatory nie ujęte w cudzysłowy są zawsze przekształcane na identyfikatory zapisane
wielkimi literami. W przypadku PostgreSQL zdecydowano, że nie będzie się on stosować
się do tej części standardu SQL92, ze względu zarówno na tradycję, jak i na czytelność.

Na tę cechę PostgreSQL szczególną uwagę powinni zwrócić administratorzy baz danych
znający inne produkty SQL — takie jak Oracle — którzy oczekują automatycznej zmiany
zapisu identyfikatora na wielkie litery. Kwestia wielkości liter — aby uniknąć konfliktów
z innymi konwencjami — powinna być dokładnie rozważona przez programistów
zainteresowanych pisaniem dających się łatwo przenieść aplikacji.

Ponieważ analizator składni może odczytywać i poprawnie interpretować instrukcje zawierające
zarówno wielkie, jak i małe litery (zakładając, że ich składnia jest poprawna), należy ostrożnie ko-
rzystać z właściwości dotyczących wielkich i małych liter. Przy intensywnej pracy w SQL wyko-
rzystywanie obu wielkości może zarówno podnieść, jak i obniżyć efektywność.

W celu uzyskania czytelności zalecamy zapisywanie identyfikatorów małymi literami, a słów klu-
czowych — wielkimi. Taka konwencja jest stosowana w całej tej książce. Poprzez wizualne od-
dzielenie ustalonej, systematycznej terminologii od definiowanych przez użytkownika obiektów
danych, można znacznie ułatwić szybkie czytanie i rozumienie złożonych instrukcji SQL.

Użycie cudzysłowów jest wymagane tylko wtedy, gdy identyfikator obiektu bazy danych jest
identyczny ze słowem kluczowym, bądź kiedy identyfikator posiada w swojej nazwie przynajm-
niej jedną wielką literę. W każdej z tych sytuacji należy pamiętać, aby ująć identyfikator w apo-
strofy, zarówno przy tworzeniu obiektu, jak i przy każdym późniejszym odwołaniu się do niego
(na przykład w instrukcjach SELECT, DELETE lub UPDATE).

Jeżeli identyfikator, który jest zapisywany identycznie, jak istniejące słowo kluczowe, nie zostanie
ujęty w cudzysłowy, PostgreSQL zwróci komunikat o błędzie, ponieważ zinterpretuje wspomnia-
ny identyfikator jako słowo kluczowe. Gdyby na przykład istniała tabela, której nazwa brzmiałaby
select

, wówczas przy próbie użycia pokazanej tu instrukcji, stanowiącej zapytanie dotyczące tej

tabeli, pojawiłby się komunikat o błędzie:

testdb=#

SELECT * FROM select;

ERROR: parser: parse error at or near "select"

Jak można zauważyć, nie ujęte w cudzysłowy zapytanie dotyczące tabeli select powoduje po-
jawienie się komunikatu o błędzie. Aby zaznaczyć, że select jest w rzeczywistości tabelą, a nie
słowem kluczowym, nazwę select należy umieścić w cudzysłowach. Zatem poprawna składnia
pozwalająca obejrzeć tabelę select jest następująca:

testdb=#

SELECT * FROM "select";

selected
----------
0
1
52
105
(4 rows)

Należy pamiętać, że podobnie muszą być traktowane wszystkie identyfikatory zawierające przy-
najmniej jedną dużą literę. Na przykład, jeżeli z jakiegoś powodu utworzona została tabela o na-
zwie ProDucts (należy zwrócić uwagę na wielkie litery „P” i „D”) i podjęto decyzję, aby ją
usunąć (co prawdopodobnie trzeba uczynić przy takiej nazwie), to aby dokładnie opisać nazwę ta-
beli, identyfikator ProDucts należy ująć w cudzysłowy.

booktown=#

DROP TABLE ProDucts;

ERROR: table "products" does not exist
booktown=#

DROP TABLE "ProDucts";

DROP

Technika ta może być wyjątkowo użyteczna w pewnych okolicznościach, nawet jeżeli czytelnik
sam nigdy nie nadaje nazw obiektom bazy danych zgodnie z tymi kryteriami. Na przykład, im-
portowanie danych poprzez zewnętrzne połączenie ODBC (choćby poprzez Microsoft Access)
może spowodować powstanie nazw tabel zapisanych dużymi literami. Gdyby nie było funkcji
identyfikatorów ujmowanych w cudzysłowy, nie byłoby żadnego sposobu poprawnego odwoły-
wania się do tych tabel.

Nazwy zarówno słów kluczowych, jak i identyfikatorów w PostgreSQL posiadają ograniczenie
maksymalnej długości, wynoszące 31 znaków. Sprawdzone pod względem składni słowa kluczowe

$%&

lub identyfikatory, które przekraczają wspomniany limit długości są automatycznie obcinane.
Identyfikatory mogą zaczynać się dowolną literą (od a do z) lub znakiem podkreślenia „_”, a dalej
mogą wystąpić litery, cyfry (od 0 do 9) lub znaki podkreślenia „_”. Słowa kluczowe nie mogą za-
czynać lub kończyć się znakiem podkreślenia „_”, podczas gdy ograniczenie to nie dotyczy iden-
tyfikatorów. Ani słowa kluczowe, ani identyfikatory nie powinny nigdy zaczynać się cyfrą.

W punkcie „Kiedy cudzysłowy są wymagane” opisaliśmy, jak można wykorzystać identyfikatory
ujmowane w cudzysłowy do „wyeliminowania” niewrażliwości identyfikatorów na wielkość liter.
Takie samo „nagięcie zasad” można zastosować po to, aby umożliwić zastosowanie identyfikato-
rów rozpoczynających się od cyfry. Chociaż PostgreSQL nie pozwoli na utworzenie tabeli wyko-
rzystującej nazwę 1st_bent_rule bez cudzysłowów, to jednak nazwa ta jest dopuszczalna,
jeżeli zostanie ujęta w cudzysłowy.

W przykładzie 3.3 najpierw próba utworzenia niepoprawnie nazwanej tabeli kończy się niepowo-
dzeniem; następnie udaje się „nagiąć” zasadę, na mocy której nie można rozpoczynać nazwy słów
kluczowych ani identyfikatorów od cyfry poprzez użycie cudzysłowów.

Przykład 3.3. „Naginanie” zasad

booktown=#

CREATE TABLE 1st_bent_rule (rule_name text);

ERROR: parser: parse error at or near "1"
booktown=#

CREATE TABLE "1st_bent_rule" (rule_name text);

CREATE

Ponadto, chociaż użycie samych cudzysłowów w nazwie ujętej w cudzysłowy jest oczywiście nie-
dopuszczalne, to inne zwykle niedozwolone znaki — takie jak spacje i znaki & — są w niej do-
puszczalne. Należy zauważyć, że chociaż standard SQL ANSI/ISO zabrania używania identyfika-
torów z takimi samymi nazwami, jakie mają słowa kluczowe SQL, to PostgreSQL (jak wiele
innych implementacji języka SQL) prezentuje mniej rygorystyczne podejście w tym względzie,
pozwalając na wykorzystywanie takich nazw ujętych w cudzysłowy.

Należy pamiętać, że chociaż wykorzystanie cudzysłowów może okazać się bardzo pomocne
w nietypowych okolicznościach, to jednak jeśli chcemy opracowywać przenośne instrukcje i rela-
cje w standardowym SQL, powinniśmy stosować się do standardów ANSI/ISO wszędzie tam,
gdzie tylko jest to możliwe.

Chociaż przy pracy z bazą danych duża ilość danych jest zapisywana na dysku, a odwołania do
nich następują za pośrednictwem identyfikatorów (na przykład nazw tabel, nazw kolumn i funk-
cji), to jednak są oczywiście sytuacje, w których do systemu muszą zostać wprowadzone nowe da-
ne. Można to zaobserwować podczas wprowadzania nowych rekordów, kiedy formułowane są
klauzule w celu określenia kryteriów usuwania lub modyfikacji, lub wtedy, gdy wykonywane są
obliczenia na istniejących rekordach. Dane te są wprowadzane za pomocą stałych, które czasami
nazywane są literałami — ponieważ dosłownie przedstawiają wartość w instrukcji SQL (zamiast
odwoływania się do istniejącej wartości za pomocą identyfikatora).

Stałą z domniemanym typem jest taka stała, której typ jest rozpoznawany automatycznie przez
analizator składni PostgreSQL na podstawie samej składni. PostgreSQL obsługuje pięć typów
stałych z domniemanym typem:

• Ciąg znaków
• Ciąg bitów
• Liczba całkowita
• Liczba zmiennoprzecinkowa
• Wartość logiczna

Stała-ciąg znaków jest dowolną sekwencją znaków ujętych w apostrofy. Stałe takie są wykorzy-
stywane przy wstawianiu danych znakowych do tabeli lub przy przekazywaniu danych znakowych
do innego obiektu bazy danych. Praktycznym przykładem konieczności istnienia stałych-ciągów
znaków jest aktualizacja imion i nazwisk autorów w tabeli authors w Book Town.

booktown=#

SELECT * FROM authors;

Patrząc na zawartość tabeli można zauważyć, że wartość first_name o identyfikatorze id 16
— Louisa May — została zapisana błędnie jako Luoisa May. Aby to poprawić, można wykorzy-
stać instrukcję UPDATE ze stałą-ciągiem znaków, zgodnie z tym, co pokazano w przykładzie 3.4.

Przykład 3.4. Zastosowanie stałych-ciągów znaków

booktown=#

UPDATE authors

booktown-#

SET first_name = 'Louisa May'

booktown-#

WHERE first_name = 'Luoisa May';

UPDATE 1
booktown=#

SELECT * FROM authors;

Instrukcja UPDATE wykonana w przykładzie 3.4 wykorzystuje stałe-ciągi znaków Louisa May
i Luoisa May w połączeniu ze słowami kluczowymi SET i WHERE. Instrukcja ta aktualizuje za-
wartość tabeli, do której nastąpiło odwołanie za pomocą identyfikatora authors i — jak pokaza-
no — poprawia błędny zapis.

Jednakże fakt, że stałe-ciągi znaków są ujmowane w pojedyncze apostrofy, stwarza oczywisty problem
semantyczny polegający na tym, że jeżeli sama sekwencja zawiera pojedynczy apostrof, to dokładne

$%&

granice stałej nie są jednoznacznie określone. W celu uniknięcia fizycznego umieszczenia poje-
dynczego apostrofu w ramach ciągu można wpisać dwa sąsiadujące pojedyncze apostrofy. Analiza-
tor składni zinterpretuje dwa pojedyncze, sąsiadujące apostrofy w ramach stałej ciągu znakowego
jako pojedynczy, dosłowny apostrof. PostgreSQL dopuszcza również wbudowywanie pojedynczych
apostrofów poprzez wykorzystanie — tak, jak w języku C — odwrotnego ukośnika.

testdb=#

SELECT 'PostgreSQL''s great!' AS example;

example
---------------------
PostgreSQL's great!
(1 row)
booktown=#

SELECT 'PostgreSQL\'s C-style slashes are great!' AS example;

example
-----------------------------------------
PostgreSQL's C-style slashes are great!
(1 row)

PostgreSQL obsługuje również sekwencje sterujące z wykorzystaniem odwrotnego ukośnika.
Wymieniono je w tabeli 3.3.

Tabela 3.3. Sekwencje sterujące w stylu języka C obsługiwane przez PostgreSQL

Sekwencja sterująca

Opis

\\
\'
\b
\f
\n
\r
\t

\xxx

Odwrotny ukośnik

Apostrof

Znak backspace

Koniec strony

Nowy wiersz

Powrót karetki

Tabulator

Znak ASCII o wartości ósemkowej wynoszącej xxx

Rezultatem specjalnego, opisanego w tabeli 3.3 znaczenia odwrotnych ukośników jest
konieczność zastosowania kolejnego odwrotnego ukośnika w celu wprowadzenia tego znaku
do ciągu znaków; na przykład w ciągu 'Pojedynczy odwrotny ukośnik to:
\\'

para odwrotnych ukośników zostanie przekształcona na pojedynczy odwrotny ukośnik.

Kiedy do PostgreSQL wprowadzane są dwa ciągi znakowe ujęte w cudzysłowy i rozdzielone pew-
ną liczbą spacji oraz gdy w ciągu tych spacji wprowadzono co najmniej jeden znak nowego wier-
sza, wówczas ciągi te są łączone i przedstawiane tak, jak gdyby zostały wpisane jako jedna stała.
Ilustruje to przykład 3.5.

Przykład 3.5. Stałe-ciągi znaków w kilku wierszach

booktown=#

SELECT 'book'

booktown-#
booktown-#

'end' AS example;

example
---------
bookend
(1 row)

booktown=#

SELECT 'bookend' AS example;

example
---------
bookend
(1 row)

Jak widać, semantyka obydwu instrukcji jest równoważna. Jednakże do takiej interpretacji wyma-
gany jest przynajmniej jeden znak nowego wiersza, ponieważ same spacje spowodowałyby nastę-
pujący błąd:

booktown=#

SELECT 'book' 'end' AS mistake;

ERROR: parser: parse error at or near "'"

Błąd ten powstał dlatego, że przy braku nowego wiersza PostgreSQL zakłada, że następuje odwo-
łanie do dwóch oddzielnych stałych. Jeżeli chcemy połączyć w ten sposób dwie stałe-ciągi znako-
we występujące w jednym wierszu, to możemy skorzystać z faktu, że PostgreSQL obsługuje ope-
rator kontaktencji „||” do łączenia tekstów (bardziej szczegółowe informacje na temat tego
operatora przedstawiono w rozdziale 5. — „Operatory i funkcje”).

booktown=#

SELECT 'book' || 'end' AS example;

example
---------
bookend
(1 row)

Stałe-ciągi bitów zapewniają sposób bezpośredniego przedstawiania wartości binarnej za pomocą
dowolnej sekwencji jedynek i zer. Podobnie do stałych-ciągów znakowych są one ograniczone
przez pojedyncze apostrofy. Muszą być również poprzedzone przez prowadzący znak B (pisany
wielką lub małą literą). Znak ten informuje PostgreSQL, że stała jest ciągiem bitowym, a nie zwy-
kłym ciągiem znakowym.

Pojedynczy apostrof otwierający musi występować bezpośrednio po prowadzącym znaku B, a ciąg
bitowy nie może zawierać znaków innych niż 0 lub 1. Chociaż wewnątrz ciągu bitowego nie mo-
gą występować spacje, ciąg ten może występować w kilku wierszach, tak jak zwykłe stałe-ciągi
znakowe, opisane w podrozdziale zatytułowanym „Stałe-ciągi znaków”.

Stałe-ciągi bitów są w zasadzie użyteczne jedynie przy tych operacjach na tabelach lub funkcjach, które
wymagają wartości binarnych. W przykładzie 3.6 przedstawiono wykorzystanie stałej-ciągu bitowego
dla prostej tabeli zawierającej szereg bajtów. Bajt ciągu bitowego jest wstawiany do listy bajtów w ta-
beli my_bytes, a następnie jego wstawienie jest weryfikowane za pomocą prostego zapytania.

Przykład 3.6. Wykorzystanie stałych-ciągów bitowych

testdb=#

INSERT INTO my_bytes VALUES (B'00000000');

testdb=#

SELECT my_byte FROM my_bytes;

my_byte
----------
10000000
10000001
10000101
11111111
00000000
(5 rows)

$%&

Znacznie częściej niż stałe-ciągi bitów są wykorzystywane stałe-liczby całkowite. PostgreSQL
identyfikuje stałą tego typu jako znacznik, który zawiera jedynie sekwencje cyfr (bez kropki dzie-
siętnej) i znajduje się na zewnątrz pojedynczych apostrofów. SQL definiuje zatem stałe całkowite
jako sekwencję cyfr dziesiętnych bez kropki dziesiętnej. Zakres dostępnych wartości dla stałej cał-
kowitej zależy głównie od kontekstu, w jakim jest ona wykorzystywana, ale domyślnie dla Post-
greSQL ten typ danych jest 4-bajtową liczbą całkowitą ze znakiem, z przedziału od –2 147 483
648 do 2 147 483 647.

Stałe całkowite są wykorzystywane wszędzie, gdzie należy przedstawić wartość całkowitą. Są one
często używane w działaniach matematycznych, jak również w poleceniach SQL, które odnoszą
się do kolumn zawierających liczby całkowite. W przykładzie 3.7. w prosty sposób przedstawiono
wykorzystanie stałych całkowitych do aktualizacji numerycznego identyfikatora autora za pomocą
polecenia UPDATE.

Rozważmy ponownie tabelę authors wykorzystywaną w poprzednich podrozdziałach, która
wiąże numeryczny identyfikator autora z dwoma ciągami znakowymi reprezentującymi imię i na-
zwisko autora. Załóżmy, że z powodów administracyjnych uznano za niezbędne, aby każdy iden-
tyfikator autora o wartości mniejszej niż 100 zmodyfikować na wartość większą niż 100.

Pierwszym krokiem, jaki należy wykonać, jest znalezienie wszystkich autorów o określonej (czyli
mniejszej niż 100) wartości identyfikatora. Można wykorzystać stałą całkowitą w klauzuli WHERE
instrukcji SELECT w celu wykonania sprawdzenia „mniejszy niż”.

Przykład 3.7. Wykorzystanie stałych całkowitych

booktown=#

SELECT * FROM authors WHERE id < 100;

id | last_name | first_name
-------+-----------+------------------
16 | Alcott | Louisa May
(1 row)

booktown=#

SELECT * FROM authors WHERE id = 116;

id | last_name | first_name
-------+-----------+------------------
(0 rows)

booktown=#

UPDATE authors

booktown-#

SET id = 116

booktown-#

WHERE id = 16;

UPDATE 1
booktown=#

SELECT * FROM authors WHERE id = 116;

id | last_name | first_name
-------+-----------+------------------
116 | Alcott | Louisa May
(1 row)

W przykładzie 3.7 klauzula WHERE w instrukcji SELECT porównuje identyfikator kolumny — id
— ze stałą całkowitą wynoszącą 100, zwracając jeden wiersz. Kiedy zostanie znaleziony autor z od-
powiednim identyfikatorem, wykonywana jest druga instrukcja SELECT, aby sprawdzić, czy istnieje
autor z identyfikatorem równym 116. Ma to na celu sprawdzenie, czy nowy identyfikator nie jest
wykorzystywany przez innego autora w tabeli authors, ponieważ kolumna ta została określona
jako wymagająca unikalnego identyfikatora. Na koniec wykonywane jest polecenie UPDATE, po-
nownie z wykorzystaniem stałych całkowitych — zarówno w klauzuli SET, jak i WHERE.

Stałe-liczby zmiennoprzecinkowa są podobne do stałych-liczb całkowitych, ale są używane nie
tylko do przedstawiania liczb dziesiętnych, ale wszystkich liczb. Stałe tego typu są wymagane
wtedy, kiedy wartość zmiennoprzecinkowa musi być przedstawiona w instrukcji SQL dosłownie.

Stała zmiennoprzecinkowa może być przedstawiona w kilku postaciach — jak pokazano w tabeli
3.4. Każde wystąpienie znaków ## reprezentuje przynajmniej jedną cyfrę.

Tabela 3.4. Reprezentacje zmiennoprzecinkowe

Reprezentacja

Przykład

##.##
##e[+-]##
[##].##[e[+-]##]
##.[##][e[+-]##]

6.4
8e-8
.04e8
4.e5

W pierwszej postaci przed lub po kropce dziesiętnej musi występować przynajmniej jedna cyfra,
po to, by PostgreSQL rozpoznał wartość jako stałą zmiennoprzecinkową, a nie stałą całkowitą.
W pozostałych przypadkach przed lub po klauzuli wykładnika oznaczonej na liście jako e musi
występować przynajmniej jedna cyfra. Stałą całkowitą od stałej zmiennoprzecinkowej odróżnia
obecność kropki dziesiętnej, klauzuli wykładnika lub obydwu tych elementów naraz

Każdy z tych poprawnych formatów jest przedstawiony w przykładzie 3.8 w prostej instrukcji
SQL SELECT, która ilustruje różnorodność konwencji zmiennoprzecinkowych.

Przykład 3.8. Poprawne wartości zmiennoprzecinkowe

booktown=#

SELECT .04 AS small_float,

booktown-#

-16.63 AS negative_float,

booktown-#

4e3 AS exponential_float,

booktown-#

6.1e-2 AS negative_exponent;

small_float | negative_float | exponential_float | negative_exponent
-------------+----------------+-------------------+-------------------
0.04 | -16.63 | 4000 | 0.061
(1 row)

"#$"%

Stałe logiczne są znacznie prostsze od jakichkolwiek innych wartości stałych rozpoznawanych
przez PostgreSQL, ponieważ mogą one przyjmować jedynie dwie wartości: prawda (true) i fałsz
(false). Kiedy PostgreSQL napotyka jedną z tych wartości poza apostrofami, wówczas w sposób
domyślny interpretuje ją jako stałą logiczną, a nie jako stałą ciągu znakowego. To ważne rozróż-
nienie przedstawiono w przykładzie 3.9.

Przykład 3.9. Różnica pomiędzy zapisami true i 'true'

testdb=#

SELECT true AS boolean_t,

testdb-#

'true' AS string_t,

testdb-#

false AS boolean_f,

testdb-#

'false' AS string_f;

$%&

Kiedy analizator składni PostgreSQL napotka wyrażenia true i false poza pojedynczymi apostro-
fami, oznacza to, że są one wartościami logicznymi. Jak pokazano w przykładzie 3.9, PostgreSQL
wyświetla wartości typu logicznego jako t lub f. Należy jednak uważać, by nie wykorzystywać
wartości t lub f jako stałych logicznych, ponieważ nie zostanie to poprawnie zinterpretowane
przez PostgreSQL i spowoduje wystąpienie błędu.

Specjalne symbole znakowe są znakami z predefiniowanym znaczeniem syntaktycznym w Post-
greSQL. Z tego powodu zazwyczaj nie dopuszcza się ich wykorzystywania jako nazw identyfika-
torów, chociaż — jak wspomniano przy omawianiu identyfikatorów ujmowanych w cudzysłowy
— jeżeli istnieje taka potrzeba, ograniczenie to może zostać wyeliminowane poprzez użycie cu-
dzysłowów.

Niektóre specjalne symbole znakowe pomagają tworzyć „interpunkcję” instrukcji SQL, podobnie
jak nawiasy, kropki i przecinki w języku angielskim. W tabeli 3.5 pokazano niektóre typowe sym-
bole syntaktyczne rozpoznawane przez PostgreSQL.

Tabela 3.5. Symbole interpunkcji

Znak

Definicja

(gwiazdka)

Wykorzystywany w poleceniu SELECT do sprawdzania wszystkich kolumn
w tabeli oraz w funkcji agregacji count() do liczenia wszystkich wierszy
w tabeli.

()
(nawiasy okrągłe)

Wykorzystywane do grupowania wyrażeń, wymuszania priorytetu operatorów
i do tworzenia wywołań funkcji. Użycie nawiasów okrągłych jest bardzo
subiektywne i zależy od kontekstu, w jakim są stosowane.

[]
(nawiasy kwadratowe)

Wykorzystywane do wyboru konkretnych elementów w tablicy lub w deklaracji
typu tablicy (na przykład w poleceniu CREATE TABLE).

;

(średnik)

Używany do zakończenia polecenia SQL. W instrukcji może być wykorzystany

jedynie wewnątrz stałej ciągu znakowego lub identyfikatora cytowanego.

(przecinek)

Niektóre polecenia używają przecinka do oddzielenia elementów w ramach listy.

(kropka)

Używana w stałych zmiennoprzecinkowych (na przykład 3.1415) jak również

w odwołaniach do nazw kolumn jako do elementów podrzędnych tabel
(na przykład table_name.column_name).

(dwukropek)

Używany do pobierania wycinków tablic.

(symbol dolara)

Używany w treści definicji funkcji po to, aby reprezentować parametr
pozycyjny lub argument.

Operator jest kolejnym typem specjalnego symbolu znakowego. Używany jest do wykonywania
operacji na identyfikatorach lub stałych; zwraca wartość wynikową. Operatory mogą być używane
w operacjach matematycznych — takich jak dodawanie — jak również do wykonywania operacji
porównań i operacji logicznych.

Rozważmy jeszcze raz tabelę books i jej pole numeryczne author_id. Przypomnijmy sobie,
że kolumna author_id zawiera liczby całkowite wykorzystywane do identyfikacji autora. Wy-
obraźmy sobie teraz, że z powodu modyfikacji systemu wszystkie identyfikatory autorów muszą
zostać zwiększone o 1 500. Można to osiągnąć poprzez ocenę wyniku operacji (wyrażenia opera-
tora) w instrukcji UPDATE działającej na kolumnie author_id. Wymaga to użycia operatora
dodawania (+). Oto stosowny przykład:

Przykład 3.10. Operatory w instrukcjach

booktown=#

SELECT * FROM books;

id | title | author_id | subject_id
-------+-----------------------------+-----------+------------
7808 | The Shining | 4156 | 9
4513 | Dune | 1866 | 15
4267 | 2001: A Space Odyssey | 2001 | 15
1608 | The Cat in the Hat | 1809 | 2
1590 | Bartholomew and the Oobleck | 1809 | 2
25908 | Franklin in the Dark | 15990 | 2
1501 | Goodnight Moon | 2031 | 2
1234 | The Velveteen Rabbit | 25041 | 3
2038 | Dynamic Anatomy | 1644 | 0
156 | The Tell-Tale Heart | 115 | 9
41473 | Programming Python | 7805 | 4
41477 | Learning Python | 7805 | 4
41478 | Perl Cookbook | 7806 | 4
41472 | Practical PostgreSQL | 1212 | 4
190 | Little Women | 116 | 6
(15 rows)

booktown=#

UPDATE books SET author_id = author_id + 1500;

UPDATE 15
booktown=#

SELECT * FROM books;

id | title | author_id | subject_id
-------+-----------------------------+-----------+------------
7808 | The Shining | 5656 | 9
4513 | Dune | 3366 | 15
4267 | 2001: A Space Odyssey | 3501 | 15
1608 | The Cat in the Hat | 3309 | 2
1590 | Bartholomew and the Oobleck | 3309 | 2
25908 | Franklin in the Dark | 17490 | 2
1501 | Goodnight Moon | 3531 | 2
1234 | The Velveteen Rabbit | 26541 | 3
2038 | Dynamic Anatomy | 3144 | 0
156 | The Tell-Tale Heart | 1615 | 9
41473 | Programming Python | 9305 | 4
41477 | Learning Python | 9305 | 4
41478 | Perl Cookbook | 9306 | 4
41472 | Practical PostgreSQL | 2712 | 4
190 | Little Women | 1616 | 6
(15 rows)

Jak widać, każda wartość author_id dla rekordu jest modyfikowana wynikiem działania ope-
ratora + wykonanego na poprzedniej wartości author_id.

Typowe operatory, które mogą być nam znane, to podstawowe operatory matematyczne: znak +
dla dodawania dwóch wartości liczbowych, znak – dla odejmowania jednej wartości liczbowej od
drugiej itd. Do bardziej wyrafinowanych operatorów należą między innymi operatory bitowe & i |
modyfikujące wartości binarne na poziomie bitów.

$%&

Oprócz operatorów w postaci symboli znakowych ważne jest zapamiętanie słów kluczowych SQL,
które często również są nazywane operatorami. Dotyczy to zwłaszcza operatorów logicznych AND,
OR

i NOT. Chociaż z technicznego punktu widzenia są one słowami kluczowymi, to jednak są uzna-

wane za operatory z powodu operacyjnego wyniku ich oddziaływania na stałe i identyfikatory.

W tabeli 3.6 wyszczególniono niektóre podstawowe operatory PostgreSQL.

Tabela 3.6. Podstawowe operatory PostgreSQL

Kategoria

Operator

Definicja

Operatory
matematyczne

+
(dodawanie)

Dodaje dwa typy numeryczne

–
(odejmowanie)

Odejmuje jeden typ numeryczny od drugiego

(dzielenie)

Dzieli jeden typ numeryczny przez drugi

*
(mnożenie)

Mnoży jeden typ numeryczny przez drugi

(silnia)

Zwraca silnię liczby całkowitej

(wartość bezwzględna)

Zwraca wartość bezwzględną wartości numerycznej

Operatory
porównania

=
(równość)

Porównuje, czy dwie wartości są równe

(mniejszy niż)

Ocenia, czy jedna liczba jest mniejsza od drugiej

(większy niż)

Ocenia, czy jedna liczba jest większa od drugiej

(wyrażenie regularne)

Wykonuje porównanie wyrażenia regularnego
na wartościach tekstowych

Operatory
logiczne

NOT

Zwraca wartość przeciwną do danego wyrażenia
logicznego

AND

Zwraca wartość true jeżeli obydwa wyrażenia logiczne
posiadają tę właśnie wartość

Zwraca wartość true jeżeli przynajmniej jedno wyrażenie
logiczne posiada tę wartość

Wiele operatorów posiada różne znaczenia, zależne od kontekstu. Operator = jest szczególnie
ważny ze względu na swe znaczenie wtedy, gdy jest używany w klauzuli SET instrukcji UPDATE.

W większości wyrażeń operator = jest operatorem równości (używanym do sprawdzenia, czy dwie
wartości są równe). Jednakże, kiedy operator ten występuje po klauzuli SET i nazwie identyfikato-
ra w instrukcji UPDATE, jest on traktowany jako operator przypisania. Oznacza to, że jest on wy-
korzystywany do przyporządkowania nowej wartości istniejącemu identyfikatorowi, zgodnie
z tym, co wskazuje termin SET.

Więcej informacji na temat operatorów można znaleźć w punkcie zatytułowanym „Operatory”.

Komentarze są blokami tekstu, które za pomocą specjalnych sekwencji znakowych mogą wbudo-
wać w kod SQL tekst nie będący elementem SQL. Mogą one być używane wewnątrz bloków kodu,

ponieważ PostgreSQL usuwa obszary komentarza ze strumienia wejściowego i traktuje je jako spa-
cje. Dostępne są dwa style komentarza: komentarze jednowierszowe i komentarze wielowierszowe.

Komentarze jednowierszowe są poprzedzone dwoma myślnikami (––) i mogą występować w wier-
szu samodzielnie lub za znacznikami SQL (same komentarze nie są uznawane przez analizator
składni PostgreSQL za znaczniki, ponieważ wszystkie (aż do końca wiersza) dane znakowe nastę-
pujące po sekwencji -- są traktowane jako spacje). Odzwierciedla to przykład 3.11.

Przykład 3.11. Komentarze jednowierszowe

testdb=#

SELECT 'Test' –- Komentarz mozna umiescic po poprawnym znaczniku SQL,

testdb-#

-- lub umiescic go w samodzielnym wierszu.

testdb-#

AS example;

example
---------
Test
(1 row)

Komentarze wielowierszowe zaczynają się sekwencją ukośnik-gwiazdka (/*), kończą zaś se-
kwencją gwiazdka-ukośnik (*/). Taki styl zapisywania komentarzy może już być znany progra-
mistom języka C, chociaż pomiędzy interpreterem PostgreSQL a interpreterem języka C istnieje
zasadnicza różnica, polegająca na tym, że komentarze PostgreSQL mogą być zagnieżdżane. Zatem
użycie sekwencji */ podczas tworzenia komentarza wielowierszowego, umieszczonego wewnątrz
innego komentarza wielowierszowego, powoduje zamknięcie wewnętrznego komentarza, ale nie
zamyka komentarza zewnętrznego. Stosowną ilustrację tego daje przykład 3.12.

Przykład 3.12. Komentarze wielowierszowe

testdb=#

SELECT 'Multi' /* Ten komentarz zajmuje

testdb*#

* kilka wierszy. Mozna go rowniez

testdb*#

* /* bezpiecznie zagniezdzac */ */

testdb-#

|| '-test' AS example;

example
------------
Multi-test
(1 row)

Zagnieżdżone komentarze mogą być użyteczne w przypadku, kiedy mamy plik zawierający in-
strukcje SQL, z których duża część ma być przed przesłaniem do PostgreSQL do interpretacji
i wykonania ujęta w komentarze. Jeśli w tym pliku już używano komentarzy wielowierszowych,
a istnieje potrzeba skomentowania dużego fragmentu, który zawiera wspomniane komentarze, Po-
stgreSQL rozpozna, że sekwencja (*/) zamykająca komentarz ma za zadanie zamknąć tylko
ostatnio otwarty komentarz, a nie cały komentowany obszar.

Sam znak gwiazdki (bez sąsiadującego znaku ukośnika) nie posiada żadnego specjalnego
znaczenia w ramach komentarza. Dodatkowe gwiazdki w przykładzie 3.12 dotyczącym
komentarzy wielowierszowych służą jedynie zapewnieniu odpowiedniej estetyki i czytelności.

Podsumowując, instrukcja SQL składa się ze znaczników; każdy znacznik reprezentuje słowo kluczo-
we, identyfikator, identyfikator cytowany, stałą lub specjalny symbol znakowy. W tabeli 3.7 wykorzy-
stano prostą instrukcję SELECT, aby zilustrować podstawową, ale pełną instrukcję SQL i jej składowe.

$%&

Tabela 3.7. Proste zapytanie SQL

SELECT

id, name

FROM

states

Typ znacznika

Opis

Słowo kluczowe

Polecenie

Identyfikatory

Kolumny identyfikatora i nazwy

Słowo kluczowe

Klauzula

Identyfikator

Nazwa tabeli

Jak widać, instrukcja SELECT zawiera słowa kluczowe SELECT i FROM. Łącznie słowo kluczowe
FROM

i znacznik states tworzą klauzulę, która modyfikuje i uszczegóławia polecenie SELECT.

Znaczniki id, name i states są identyfikatorami instrukcji. Identyfikatory id i name określają
wybrane kolumny, podczas gdy identyfikator states podaje nazwę tabeli, z której mają zostać
pobrane dane. Zatem, za pomocą pokazanego ostatnio zapytania SQL PostgreSQL otrzymuje po-
lecenie wyświetlenia kolumn id i name dla każdego wiersza z tabeli states. W przykładzie
3.13 pokazano wynik tego zapytania w bazie booktown.

Przykład 3.13. Przykładowe zapytanie SQL

booktown=#

SELECT id, name FROM states;

id | name
----+------------
42 | Washington
51 | Oregon
(2 rows)

booktown=#

W tabelach 3.8 i 3.9 pokazano inną przykładową instrukcję, bardziej skomplikowaną od poprzed-
nio opisanej. Instrukcja ta wykorzystuje polecenie UPDATE, z klauzulami SET i WHERE, które
określają odpowiednio, czym aktualizować rekordy i jak znaleźć rekordy do aktualizacji.

Tabela 3.8. Przykładowa instrukcja UPDATE: klauzula SET

UPDATE

states

SET

słowo kluczowe
polecenie

identyfikator
nazwa tabeli

słowo kluczowe
klauzula

identyfikator
kolumna

operator
przypisanie

stała całkowita
nowa wartość id

Tabela 3.9. Przykładowa instrukcja UPDATE: klauzula WHERE

WHERE

name

'Oregon'

słowo kluczowe
klauzula

identyfikator
nazwa kolumny

operator
równoważność

stała w postaci ciągu
wartość ciągu do dopasowania

Podczas wykonywania instrukcja ta bada kolumnę name każdego rekordu w celu dopasowania do
warunku określonego w klauzuli WHERE (równoważności z ciągiem 'Oregon'). Następnie, dla każ-
dego wiersza spełniającego wspomniany warunek, instrukcja aktualizuje kolumnę id wartością 51.

Analizując wspomnianą instrukcję UPDATE stwierdzamy, że posiada ona trzy słowa kluczowe, trzy
identyfikatory, dwa operatory i dwie stałe. Słowami kluczowymi są UPDATE

(polecenie SQL), SET

(określa, jaką aktualizację należy przeprowadzić) i WHERE (identyfikuje wiersze do aktualizacji).
Identyfikatorami są nazwa tabeli states, nazwa kolumny id oraz nazwa kolumny name.

Obydwa operatory są reprezentowane przez operator =. W klauzuli SET operator ten jest użyty do
przypisania (przyporządkowania nowej wartości do zidentyfikowanej kolumny istniejącego rekor-
du); jest to specjalne zastosowanie, które jest unikalne dla klauzuli SET. W klauzuli WHERE ope-
rator = jest użyty do sprawdzenia wartości. W tym przypadku oznacza to, że operator równości
sprawdzi wartość kolumny name rekordu względem ciągu znaków o wartości Oregon.

Stałymi w tej instrukcji są stała całkowita 51 (nowa wartość dla kolumny id) oraz ciąg znaków
Oregon (porównywany z kolumną name za pomocą klauzuli WHERE).

W przykładzie 3.14 dokonano aktualizacji tabeli states poprzez ustawienie kolumny id na
wartość 51, ilekroć kolumna name posiada wartość Oregon. Następnie sprawdzono wyniki dzia-
łania instrukcji UPDATE za pomocą instrukcji SELECT.

Przykład 3.14. Aktualizacja SQL

booktown=#

UPDATE states

booktown-#

SET id = 51

booktown-#

WHERE name = 'Oregon';

UPDATE 1
booktown=#

SELECT * FROM states

booktown-#

WHERE name = 'Oregon';

id | name | abbreviation
----+--------+--------------
51 | Oregon | OR
(1 row)

SQL uznawany jest za język charakteryzujący się ścisłą kontrolą typów. Oznacza to, że z każdym
elementem danych reprezentowanym przez PostgreSQL skojarzony jest typ danych, nawet jeżeli
nie jest to ewidentnie oczywiste. Typ wartości danych zarówno definiuje, jak i ogranicza rodzaj
operacji, które mogą być na niej wykonane.

Oprócz tego, że każdy element danych jest skojarzony z typem, typy odgrywają także dużą rolę
w tworzeniu tabel. Jak wspomniano w podrozdziale zatytułowanym „Wprowadzenie do teorii rela-
cyjnych baz danych”, tabele składają się z jednej lub więcej kolumn. Kolumny te muszą — oprócz
nazwy — posiadać także konkretny typ danych.

Chociaż PostgreSQL zapewnia dużą różnorodność wbudowanych typów danych, istnieje
opcja dodawania do PostgreSQL nowych typów danych za pomocą polecenia CREATE
TYPE

. W celu uzyskania dokładniejszej informacji należy odwołać się do odpowiedniego

opisu dotyczącego instrukcji CREATE TYPE.

W tabeli 3.10 wymieniono typy danych oficjalnie obsługiwane przez PostgreSQL, jak również
wszystkie aliasy rozpoznawane przez PostgreSQL (nazwy alternatywne, które posiadają identycz-
ne znaczenia). Istnieje wiele innych typów wewnętrznych (co oznacza, że nie są one wykorzysty-
wane w zwykły sposób) lub nie zalecanych (przestarzałych), które nie zostały tu wymienione.

Tabela 3.10. Typy danych obsługiwane przez PostgreSQL

Kategoria

Typ danychOpis

Standaryzacja

Typy logiczne
i binarne

Boolean

, bool

Pojedyncza wartość: true lub false

SQL99

bit(n)

Ciąg binarny o długości n bitów (dokładnie
n bitów)

SQL92

bit varying(n)

varbit(n)

Zmienny ciąg binarny o długości n bitów
(do długości n bitów)

SQL92

Typy znakowe

character(n)

char(n)

Ciąg znakowy o długości n znaków

SQL89

character

varying(n)

varchar(n)

Ciąg znaków o zmiennej długości do n
znaków

SQL92

text

Ciąg znaków o zmiennej i nieograniczonej
długości

Specyficzna
dla PostgreSQL

Typy
numeryczne

smallint

, int2

Dwubajtowa liczba całkowita ze znakiem

SQL89

integer

, int, int4 Czterobajtowa liczba całkowita ze znakiem

SQL92

bigint

, int8

Ośmiobajtowa liczba całkowita ze znakiem
(o długości do 18 cyfr)

Specyficzna
dla PostgreSQL

real

, float4

Czterobajtowa liczba zmiennoprzecinkowa

SQL89

double

precision

float8

, float

Ośmiobajtowa liczba zmiennoprzecinkowa

SQL89

numeric (p,s)

decimal (p,s)

Dokładny typ numeryczny o dokładności p
i skali s

SQL99

money

Waluta amerykańska o ustalonej dokładności Specyficzna

dla PostgreSQL,
nie jest zalecana

serial

Czterobajtowa, automatycznie zwiększająca
się liczba całkowita

Specyficzna
dla PostgreSQL

Typy daty
i czasu

date

Data kalendarzowa (dzień, miesiąc i rok)

SQL92

time

Godzina

SQL92

time with time
zone

Godzina wraz z informacją o strefie
czasowej

SQL92

timestamp
(includes time
zone)

Data i czas

SQL92

interval

Arbitralnie określony okres czasu

SQL92

Typy
geometryczne

box

Prostokątna ramka na płaszczyźnie
dwuwymiarowej

Specyficzna
dla PostgreSQL

line

Nieskończona linia na płaszczyźnie
dwuwymiarowej

Specyficzna
dla PostgreSQL

Tabela 3.10. Typy danych obsługiwane przez PostgreSQL — ciąg dalszy

Kategoria

Typ danychOpis

Standaryzacja

lseg

Odcinek linii na płaszczyźnie
dwuwymiarowej

Specyficzna
dla PostgreSQL

circle

Okręg o określonym środku i promieniu

Specyficzna
dla PostgreSQL

path

Otwarte i zamknięte ścieżki geometryczne
na płaszczyźnie dwuwymiarowej

Specyficzna
dla PostgreSQL

point

Punkt geometryczny na płaszczyźnie
dwuwymiarowej

Specyficzna
dla PostgreSQL

polygon

Zamknięta ścieżka geometryczna
na płaszczyźnie dwuwymiarowej

Specyficzna
dla PostgreSQL

Typy sieciowe

cidr

Specyfikacja sieciowa protokołu
internetowego

Specyficzna
dla PostgreSQL

inet

Sieciowy adres protokołu internetowego
z opcjonalnymi bitami podsieci

Specyficzna
dla PostgreSQL

macaddr

Adres MAC (na przykład adres sprzętowy
karty Ethernet)

Specyficzna
dla PostgreSQL

Typy
systemowe

oid

Identyfikator obiektu (wiersza)

Specyficzna
dla PostgreSQL

xid

Identyfikator transakcji

Specyficzna
dla PostgreSQL

Chociaż większość typów danych implementowanych w PostgreSQL bezpośrednio wywodzi się
ze standardów SQL, istnieją także pewne aktywnie utrzymywane typy danych, które są niestan-
dardowe (takie jak typy geometryczne i przestrzenne); dlatego nie zawsze będzie możliwe znale-
zienie równoważnych typów w innych systemach zarządzania bazami danych obsługujących SQL.

Nieco dalej w tym rozdziale opiszemy najczęściej używane typy. W książce tej nie zostaną przed-
stawione bardziej szczegółowo typy niestandardowe lub bardziej wyrafinowane, takie jak typy
geometryczne, sieciowe i bitowe. Podamy informacje na temat poprawności wykorzystania, wa-
runków zapisywania, formatów wejściowych i wyjściowych oraz ogólnych konwencji składnio-
wych typów danych. Zanim jednak przejdziemy do bardziej szczegółowego omówienia konkret-
nych typów danych, zaprezentujemy kilka innych ważnych zagadnień, miedzy innymi słowo
kluczowe NULL.

%&'(

Pomimo przedstawionej wcześniej zasady, że kolumna może posiadać tylko jeden typ danych
i logicznie akceptuje jedynie ten typ, istnieje wartość, na którą mogą zostać zdefiniowane wszyst-
kie kolumny, bez względu na to, jaki jest ich typ. Jest to wartość, na którą ustawiona zostanie ko-
lumna, kiedy użyjemy słowa kluczowego SQL NULL. Zasadniczo NULL nie posiada żadnej warto-
ści danych, nie jest więc uznawana za typ. Jest to wartość systemowa, która wskazuje bazie
danych, że pole, w którym jest ona ulokowana, nie zawiera żadnej wartości. Jedynym odstęp-
stwem od zasady, że każda kolumna może zawierać wartość NULL jest sytuacja, kiedy dla kolum-
ny określone jest ograniczenie NOT NULL.

Wartość NULL jest często wykorzystywana w miejscach, gdzie wartość jest opcjonalna. Może to
być wygodny sposób pomijania danych bez konieczności uciekania się do dziwnych lub dowol-
nych konwencji, takich jak zapisywanie wartości ujemnych w polach liczb całkowitych w celu za-
prezentowania, że dane pominięto. Chociaż z czasem wymagania systemowe mogą się zmieniać,
to znaczenie NULL pozostaje zawsze to samo.

Wartość NULL może być traktowana jako meta-wartość, wartość, która reprezentuje brak wartości
i która nigdy nie będzie równoważna wartości innej niż NULL. Przy działaniach na wartościach
NULL

często napotykanym problemem jest to, że są one łatwo mylone z pustymi ciągami znako-

wymi, które — kiedy zostaną wybrane — zwracają klientowi pustą wartość. Powodem wspomnia-
nych pomyłek jest fakt, że wartości NULL po wybraniu również zwracają pustą wartość. Są one
jednak czymś zupełnie innym od pustych ciągów znaków i należy o tym pamiętać, aby uniknąć
tworzenia błędnych zapytań lub błędnego kodu. Kolumna typu ciąg znaków zawierająca pustą
wartość nadal zawiera ciąg znaków, chociaż znaki, z których składa się ciąg, są spacjami. Oznacza
to, że w kolumnie istnieje wartość. Tymczasem wartość NULL oznacza całkowity brak wartości
w kolumnie a nie to, że jest ona pusta.

Jest to ważna różnica, ponieważ zasady dotyczące działań SQL dotyczące wartości NULL są zu-
pełnie inne niż zasady dla operacji związanych z pustymi ciągami znaków. To wewnętrzne roz-
różnienie jest szczególnie ważne w odniesieniu do powiązań, które są omawiane w rozdziale 4.

Zwrócenie zarówno wartości NULL, jak i wartości pustej pokazano w przykładzie 3.15, w którym
pobierany jest zbiór pięciu książek z tabeli books. Pierwsze zapytanie SELECT pokazuje, że praw-
dopodobnie istnieją dwie książki, które zostały wstawione bez tytułów. Jednak po pomyślnym
sprawdzeniu okazuje się, że chociaż żadna z tych dwóch książek nie posiada widocznego tytułu, to
jedna z nich posiada dla tytułu wartość pustą (id 100), podczas gdy druga posiada wartość NULL.

Przykład 3.15. Sprawdzanie wartości NULL

booktown=#

SELECT id, title FROM books;

booktown=#

SELECT id, title FROM books WHERE title = '';

id | title
-----+-------
100 |
(1 row)

booktown=#

SELECT id, title FROM books WHERE title IS NULL;

id | title
-----+-------
101 |
(1 row)

W przykładzie 3.16 zademonstrowano bardziej praktyczne (i prawdopodobne) wykorzystanie
NULL

w tabeli nazywanej editions, która wiąże numer ISBN książki z datą jej wydania.

Przykład 3.16. Wykorzystanie wartości NULL

booktown=#

SELECT isbn, publication FROM editions;

isbn | publication
------------+-------------
039480001X | 1957-03-01
0394800753 | 1949-03-01
0385121679 |
(3 rows)

booktown=#

SELECT isbn, publication FROM editions WHERE publication IS NULL;

isbn | publication
------------+-------------
0385121679 |
(1 row)

Wartości NULL można zastosować po to, aby opisać książki, które nie są jeszcze wydane, albo
książki, których data wydania była nieznana w czasie wprowadzania opisujących je informacji do
bazy danych. Wprowadzanie dowolnej nielogicznej daty dla książek z grup spełniających te kryteria
mogłoby być mylące. W obydwu przypadkach użycie wartości NULL jest najlepszym rozwiązaniem.

%&)*+

Wartość logiczna jest prostą strukturą danych, mogącą przyjmować jedynie wartości true
(prawda)

lub false (fałsz). PostgreSQL obsługuje zdefiniowany w SQL99 typ danych

boolean

, któremu odpowiada specyficzny dla PostgreSQL alias bool.

Wartości logiczne, tak jak wszystkie inne typy danych, mogą być również ustawione na NULL.
Jeżeli wartość logiczna jest ustawiona na NULL, nigdy nie będzie zinterpretowana jako prawda lub
fałsz, ale zawsze jako NULL. Może to wydawać się oczywiste, ale jest ważne, by o tym pamiętać,
jeśli chcemy sprawdzać ustawienie wartości logicznych NULL poprzez sprawdzenie ich ustawienia
na wartość fałsz (operacja ta się nie uda). W celu sprawdzenia ustawienia wartości logicznych
ustawionych na NULL należy użyć frazy IS NULL. Właściwość przyjmowania wartości: prawda,
fałsz lub NULL (i związane z tym zasady dotyczące oznaczenia wartości NULL jako wartości nie
będącej ani prawdą, ani fałszem) znana jest jako logika trójwartościowa.

W tabeli 3.11 pokazano poprawne, rozpoznawane przez PostgreSQL wartości stałych dla stanów
prawda i fałsz. Wybór konkretnej konwencji zależy jedynie od preferencji użytkownika. Wszyst-
kie warianty wartości prawda i fałsz są interpretowane przez serwer identycznie.

Tabela 3.11. Obsługiwane stałe prawda i fałsz

Prawda

Fałsz

true

't'

'true'

'y'

'yes'

'1'

false

'f'

'false'

'n'

'no'

'0'

Decydując się na użycie stałych z tabeli 3.1 należy pamiętać, że każda wartość (z wyjątkiem
wartości true i false) musi zostać ujęta w pojedyncze apostrofy. Jeżeli tego nie zrobimy,
ich użycie spowoduje błąd serwera.

W przykładzie 3.17 utworzono tabelę o nazwie daily_inventory, która rejestruje, jakie
książki są w magazynie, a jakich nie ma, wiążąc numer ISBN z wartością logiczną. Po utworzeniu
tabeli jest ona wypełniana danymi za pomocą szeregu instrukcji INSERT wprowadzających stałe-
ciągi znaków (numer ISBN) oraz różne poprawne stałe logiczne.

Przykład 3.17. Prosta tabela logiczna

booktown=#

CREATE TABLE daily_inventory (isbn text, is_stocked boolean);

CREATE
booktown=#

INSERT INTO daily_inventory VALUES ('0385121679', true);

INSERT 3390926 1
booktown=#

INSERT INTO daily_inventory VALUES ('039480001X', 't');

INSERT 3390927 1
booktown=#

INSERT INTO daily_inventory VALUES ('044100590X', 'true');

INSERT 3390928 1
booktown=#

INSERT INTO daily_inventory VALUES ('0451198492', false);

INSERT 3390929 1
booktown=#

INSERT INTO daily_inventory VALUES ('0394900014', '0');

INSERT 3390930 1
booktown=#

INSERT INTO daily_inventory VALUES ('0441172717', '1');

INSERT 3390931 1
booktown=#

INSERT INTO daily_inventory VALUES ('0451160916');

INSERT 3390932 1

Po wpisaniu rekordów do tabeli można łatwo sprawdzić, czy książki są w magazynie, poprzez wy-
konanie instrukcji SELECT, jak pokazano w przykładzie 3.18.

Przykład 3.18. Sprawdzenie wartości logicznych

booktown=#

SELECT * FROM daily_inventory WHERE is_stocked = 'yes';

isbn | is_stocked
------------+----------
0385121679 | t
039480001X | t
044100590X | t
0441172717 | t
(4 rows)

Istnieje możliwość implikowania wartości true poprzez odwołanie się do nazwy kolumny bez żad-
nego operatora lub modyfikującego słowa kluczowego. W dobrze zaprojektowanych tabelach, jak
pokazano w przykładzie 3.19, możemy dzięki temu uzyskać zapytania o bardziej intuicyjnej treści.

Przykład 3.19. Implikowanie wartości logicznej true

booktown=#

SELECT * FROM daily_inventory WHERE is_stocked;

isbn | is_stocked
------------+----------
0385121679 | t
039480001X | t
044100590X | t
0441172717 | t
(4 rows)

Chociaż drugie zapytanie nie określa wartości true albo false, to jednak pominięcie operatora
porównania wyraźnie wskazuje na poszukiwanie wartości true.

Podobnie, aby poszukiwać wartości false można porównywać wartość danej kolumny z dowol-
ną ze stałych logicznych podanych w tabeli 3.11 albo przed nazwą kolumny wykorzystać słowo
kluczowe NOT. Każdą z tych metod przedstawiono w przykładzie 3.20.

Przykład 3.20. Sprawdzanie wartości logicznych false

booktown=#

SELECT * FROM daily_inventory WHERE is_stocked = 'no';

isbn | is_stocked
------------+----------
0451198492 | f
0394900014 | f
(2 rows)

booktown=#

SELECT * FROM daily_inventory WHERE NOT is_stocked;

isbn | is_stocked
------------+----------
0451198492 | f
0394900014 | f
(2 rows)

Można tutaj zauważyć, że język SQL zaprojektowano z myślą o jego czytelności. Przy właściwym
nazywaniu tabel i kolumn zapytanie SQL może być czytane niemal tak łatwo, jak zdanie w języku
angielskim.

Czytelników, którzy zajmują się programowaniem w sposób nieco bardziej zaawansowany, może
zainteresować możliwość wykorzystywania operatora nierównoważności (!=) do porównywania
wartości pola logicznego z dowolną z wartości podanych w tabeli 3.11 (na przykład WHERE
is_stocked != 't'

). Następujące trzy warianty składniowe są równoważne:

SELECT * FROM daily_inventory WHERE NOT is_stocked;
SELECT * FROM daily_inventory WHERE is_stocked = 'no';
SELECT * FROM daily_inventory WHERE is_stocked != 't';

Można zauważyć, że chociaż do tabeli z przykładu 3.17 wstawiono siedem wierszy, to zapytanie
zwróciło sześć wierszy dotyczących książek znalezionych w magazynie i tych, których w nim nie
było. Dzieje się tak dlatego, że ostatnie operacja INSERT w przykładzie 3.17 nie wprowadziła
żadnej wartości dla kolumny is_stocked, co spowodowało, że książka o numerze ISBN
0451160916 w kolumnie is_stocked ma wartość NULL.

Jak już powiedziano, wartość

NULL

nie zostanie zapisana ani jako true, ani jako false. W tej

sytuacji do sprawdzenia wierszy, w których występują wartości NULL, można wykorzystać frazę
SQL IS NULL. Można też zamiast tego wykorzystać operator !=, ale wiąże się to z ryzykiem
związanym z przenośnością do innych baz danych. Następująca składnia przedstawia zapytanie
SQL wykorzystujące frazę IS NULL:

booktown=#

SELECT * FROM daily_inventory WHERE is_stocked IS NULL;

isbn | is_stocked
------------+----------
0451160916 |
(1 row)

Ponieważ IS NULL jest ogólną frazą SQL, zatem taką samą klauzulę WHERE można wykorzy-
stywać w instrukcji UPDATE w celu poprawienia wszystkich przypadkowych wartości NULL.

Przykład 3.21. Poprawianie wartości NULL

booktown=#

UPDATE daily_inventory SET is_stocked = 'f' WHERE is_stocked IS NULL;

UPDATE 1

Typy znakowe są wymagane zawsze, kiedy istnieje potrzeba odwołania się do danych znakowych,
takich jak bloki tekstowe ASCII. Są one powszechnie wykorzystywane do zapisywania nazw, ad-
resów itd.

W języku SQL istnieją dwa typy znakowe — character oraz character varying. Po-
nadto, PostgreSQL obsługuje ogólny typ text, który nie wymaga wyraźnej deklaracji górnego
limitu rozmiaru pola. Kolumny typu text są automatycznie dopasowywane pod względem roz-
miaru do umieszczanych w nich danych i mogą zmieniać swój rozmiar bez ograniczeń (pamiętając
oczywiście o ograniczeniu 1GB dla pojedynczego pola). W tabeli 3.12 pokazano znakowe typy
danych dostępne w PostgreSQL.

Tabela 3.12. Typy znakowe

Typ

Wielkość

Opis

character(n)

char(n)

(4 + n) bajtów

Ciąg znaków o ustalonej długości, dopełniony
spacjami tak, aby posiadał długość n znaków

character varying(n)

varchar(n)

Do (4 + n) bajtów

Ciąg znaków o zmiennej długości z ograniczeniem
wynoszącym n znaków

text

Zmienna

Ciąg znaków o zmiennej i nieograniczonej długości

Wartość n w tabeli 3.12 reprezentuje dowolnie określoną liczbę znaków. Liczba ta jest definiowa-
na dla kolumny podczas tworzenia tabeli.

Chociaż typ danych text nie jest częścią standardów SQL ANSI/ISO, to wiele SZBD
— między innymi Sybase czy MS SQL Server — zawiera ten typ.

Typy numeryczne PostgreSQL są wykorzystywane do przedstawiania zarówno wartości całkowitych,
jak i dziesiętnych zmiennoprzecinkowych. PostgreSQL obsługuje następujące typy numeryczne:

• Dwu-, cztero- i ośmiobajtowe liczby całkowite
• Cztero- i ośmiobajtowe liczby zmiennoprzecinkowe
• Liczby dziesiętne o ustalonej dokładności

PostgreSQL obsługuje specjalne typy, które mieszczą się w grupie typów numerycznych, między
innymi nie zalecany typ money oraz specjalną konstrukcję serial.

Tabela 3.13. Przegląd typów numerycznych

Typ danychWielkość

Zakres

bigint

, int8

8 bajtów

Wartości całkowite z przedziału od –9 223 372 036 854 775 807
do +9 223 372 036 854 775 807

double precision

float8

, float

8 bajtów

Wartości zmiennoprzecinkowe, 15 cyfr znaczących,
nieograniczony rozmiar (przy ograniczonej dokładności)

integer

, int, int4

4 bajtyWartości całkowite z przedziału od –2 147 483 648

do +2 147 483 647

numeric(p, s)

decimal(p, s)

Zmienna

Wartości zmiennoprzecinkowe zdefiniowane ogólnie jako p cyfr
(łącznie z cyframi położonymi na prawo od kropki dziesiętnej)
z s cyframi na prawo od kropki dziesiętnej

real

, float4

4 bajty

Wartości zmiennoprzecinkowe, sześć cyfr znaczących,
nieograniczony rozmiar (przy ograniczonej dokładności)

smallint

, int2

2 bajtyWartości całkowite z przedziału od –32 768 do +32 767

money

4 bajty

Wartości zmiennoprzecinkowe (przy skali wynoszącej
dwie cyfry na prawo od kropki dziesiętnej) z przedziału
od –21 474 836,48 do +21 474 836,47

serial

4 bajtyWartości całkowite z przedziału od 0 do 2 147 483 647

Jak pokazano w tabeli 3.13, kilka typów danych PostgreSQL posiada aliasy, które są równoważne
z odpowiadającymi im typami danych. Ułatwia to pracę, ale czasami może wprowadzać zamieszanie
spowodowane tym, że niektóre aliasy brzmią znajomo. Jeżeli użytkownik nie rozumie, z jakim ty-
pem danych skojarzony jest alias, który właśnie wykorzystuje, może przypadkowo odwołać się do
błędnego typu danych. Na przykład, w PostgreSQL typy danych real i double precision re-
prezentują wartości, które w wielu innych językach są traktowane jako wartości zmiennoprzecinko-
we. Jednakże obydwa te typy posiadają aliasy zawierające słowo float (float i float8 dla
double precision

oraz float4 dla real). Problemy mogą pojawić się w sytuacji, jeżeli

spróbujemy użyć aliasu float myśląc, że jest on związany z typem real, natomiast w rzeczywi-
stości jest on skojarzony z typem double precision.

Typ numeric (znany również jako decimal) jest specjalnie zaprojektowanym typem danych
numerycznych, który może reprezentować dowolnie duże i dokładne wartości w ramach ustalonej
długości, określonej przez użytkownika. Podczas tworzenia tabeli zawierającej kolumnę typu nu-
meric

można określić w nawiasach dwie wartości: dokładność i skalę.

Dokładność jest maksymalną liczbą cyfr, które mogą występować w wartości numerycznej (łącz-
nie z cyframi znajdującymi się na prawo od kropki dziesiętnej), podczas gdy skala opisuje, ile
z tych cyfr ma znaleźć się na prawo od kropki dziesiętnej. Jeżeli wartości dokładności i skali nie
zostaną wcześniej określone, to domyślnie przyjmuje się 30 cyfr dla dokładności i 6 cyfr dla skali.
Maksymalna dokładność (a stąd i maksymalna skala) jaką można ustalić, wynosi 1000. Ustawienie
dokładności na 1000 umożliwiłoby wpisanie maksymalnie 1000 cyfr, co stanowi w większości
przypadków wystarczającą dokładność.

W przypadku naruszenia dokładności lub skali kolumny numerycznej PostgreSQL
nie zawsze zwróci błąd.

Jeżeli spróbujemy wstawić liczbę, która jest większa niż przydzielony zakres dokładności, to
— w przeciwieństwie do typów danych zmiennoprzecinkowych — otrzymamy błąd nadmiaru.
Poza tym ograniczeniem możemy wstawić dowolną liczbę, która mieści się w przyjętej dokład-
ności i skali kolumny typu numeric.

Na przykład, w kolumnie numeric(11

można bez problemów wstawić wartość 9,99999999,

posiadającą o dwie cyfry za dużo na prawo od kropki dziesiętnej (chociaż wartość jest zaokrąglona
w górę do 10,000000). Jednak próba wstawienia wartości 99999,99999999 nie powiedzie się.
Pokazano to w przykładzie 3.22.

Poprzez wykorzystanie funkcji obcinania numerycznego trunc() w ramach polecenia INSERT,
można uniknąć problemów, które powstają przy próbach wstawiania zbyt długich wartości. Funk-
cja ta gwarantuje, że liczba zostanie obcięta do rozmiaru odpowiedniego dla kolumny, do której
jest wstawiana. Użytkownik musi podać długość, do której powinna zostać obcięta liczba, co
oznacza, że musi pamiętać dokładności, które wcześniej określił. Użycie funkcji trunc() także
zilustrowano w przykładzie 3.22.

Przykład 3.22. Unikanie błędów nadmiaru

tempdb=#

INSERT INTO numbers VALUES (9.99999999);

INSERT 3390697 1
tempdb=#

SELECT * FROM numbers;

number
--------------
10.000000
(1 row)

tempdb=#

INSERT INTO numbers VALUES (99999.99999999);

ERROR: overflow on numeric ABS(value) >= 10^5 for field with precision 11

åscale 6
tempdb=#

INSERT INTO numbers VALUES (trunc(99999.99999999, 6));

INSERT 3390698 1
tempdb=#

SELECT * FROM numbers;

number
--------------
10.000000
99999.999999
(2 rows)

tempdb=#

INSERT INTO numbers VALUES (trunc(9.99999999, 6));

INSERT 3390699 1
tempdb=#

SELECT * FROM numbers;

number
--------------
10.000000
99999.999999
9.999999
(3 rows)

Typ money zapisuje notację waluty amerykańskiej oraz zwykłe wartości numeryczne. Według
poglądów prezentowanych w tej książce typ money nie jest typem zalecanym i radzimy, aby nie
wykorzystywać go zbyt często. Prezentujemy go w tym miejscu, ponieważ jest to nadal funkcjo-
nalny typ danych i może być używany w istniejących systemach PostgreSQL.

Polecaną alternatywą dla typu money jest typ numeric, o skali równej 2, co zapewnia przedsta-
wianie wartości monet, oraz o dokładności wystarczająco dużej, aby zapisać możliwie największe
kwoty pieniędzy (łącznie z dwoma cyframi na dokładność w setnych częściach waluty). Formato-
wanie podobne do reprezentowanego przez typ money można osiągnąć za pomocą funkcji
to_char

, jak pokazano w przykładzie 3.23. W przykładzie tym pokazano operator łączenia tek-

stu oraz funkcję formatowania tekstu ltrim(), które zostaną opisane w rozdziale 4.

Przykład 3.23. Alternatywne wykorzystanie typu numeric zamiast typu money

booktown=#

\d money_example

booktown=#

INSERT INTO money_example VALUES ('$12.24', 12.24);

INSERT 3391095 1
booktown=#

SELECT * FROM money_example;

money_cash | numeric_cash
------------+--------------
$12.24 | 12.24
(1 row)

booktown=#

SELECT money_cash,

booktown-#

'$' || ltrim(to_char(numeric_cash, '9999.99'))

booktown-#

AS numeric_cashified

booktown-#

FROM money_example;

money_cash | numeric_cashified
------------+-------------------
$12.24 | $12.24
(1 row)

Typ serial jest niestandardowym, ale użytecznym skrótem, który pozwala łatwo tworzyć w ta-
beli kolumnę identyfikatora, zawierającą unikalną wartość dla każdego wiersza. Typ serial łą-
czy funkcje czterobajtowego typu danych integer, indeksu i sekwencji. W przykładzie 3.24 po-
kazano typ serial, który wykorzystano do generowania unikalnego identyfikatora dla każdego
wiersza w tabeli auto_identified.

Przykład 3.25 ilustruje uzyskanie takiego samego efektu przy wykorzystaniu kolumny integer,
funkcji nextval() oraz sekwencji. Według poglądów prezentowanych w tej książce obydwie te
metody są identyczne.

W celu uzyskania dodatkowych informacji dotyczących używania sekwencji należy odwołać się
do rozdziału 7., zatytułowanego „Zaawansowane możliwości”.

Przykład 3.24. Zastosowanie typu danych serial

booktown=#

CREATE TABLE auto_identified (id serial);

NOTICE: CREATE TABLE will create implicit sequence 'auto_identified_id_seq'
for SERIAL column 'auto_identified.id'
NOTICE: CREATE TABLE/UNIQUE will create implicit index 'auto_identified_id_key'
for table 'auto_identified'
CREATE

Przykład 3.25. Osiąganie tego samego celu przy użyciu kolumny integer, funkcji nextval() oraz sekwencji

booktown=#

CREATE SEQUENCE auto_identified_id_seq;

CREATE
booktown=#

CREATE TABLE auto_identified

booktown-#

(id integer UNIQUE DEFAULT nextval('auto_identified_id_seq'));

NOTICE: CREATE TABLE/UNIQUE will create implicit index 'auto_identified_id_key'

åfor table 'auto_identified'
CREATE

Po usunięciu tabeli, domyślne sekwencje utworzone dla typów serial nie są automatycznie
usuwane. Jak pokazano w przykładzie 3.24, jeżeli usuwamy tabelę, która posiadała kolumnę
serial

, powinniśmy „posprzątać” po tego typu sekwencjach za pomocą polecenia DROP

SEQUENCE

Typy daty i czasu są wygodnym sposobem zapisywania danych związanych z datą i czasem w jed-
nolitej strukturze danych SQL, bez obaw dotyczących konwencji zapisywania (co miałoby miejsce
w przypadku zapisywania takich danych za pomocą znakowych typów danych). Do wszystkich obli-
czeń związanych z datą i czasem PostgreSQL wykorzystuje daty kalendarza gregoriańskiego. Grego-
riańska reprezentacja daty to powszechnie używany kalendarz z miesiącami od stycznia do grudnia,
najprawdopodobniej dobrze znany każdemu z nas. Na podstawie kalendarza gregoriańskiego, po-
przez ustalenie długości roku na 365,24 dnia, możliwe jest obliczenie dowolnej daty począwszy od
4713 r. p.n.e., aż do dowolnej daty w przyszłości.

PostgreSQL obsługuje wszystkie typy daty i czasu zdefiniowane przez SQL92, pokazane w tabeli
3.14, jak również niektóre specyficzne dla PostgreSQL rozszerzenia, które mają pomóc w prze-
zwyciężeniu ograniczeń SQL92 dotyczących stref czasowych.

W celu zagwarantowania kompatybilności z wcześniejszymi wersjami PostgreSQL programiści
w dalszym ciągu zapewniają obsługę typów danych datetime i timespan. Typ datetime
jest równoważny typowi timestamp, a typ timespan — typowi interval.

Jeżeli tabela i sekwencja zostały już utworzone, przed wykonaniem tego ćwiczenia muszą zostać usunięte: DROP
sequence auto_identified_id_seq; DROP TABLE auto_identified;

— przyp. red.

Tabela 3.14. Typy daty i czasu

Typ danychPamięć

Opis

Zakres

date

4 bajtyData kalendarzowa (rok, miesiąc i dzień)

Od 4713 p.n.e.
do 32767 n.e.

time

4 bajtyTy

lko czas, bez informacji o strefie czasowej

Od 00:00:00:00
do 23:59:59:99

time with time
zone

4 bajtyTylko czas. Zawiera informacje o strefie

czasowej

Od 00:00:00:00+12
do 23:59:59:99–12

timestamp with

time zone

timestamp

8 bajtów

Zarówno data kalendarzowa, jak i czas.
Zawiera informacje o strefie czasowej

Od 1903 n.e.
do 2037 n.e.

interval

12 bajtów Ogólny przedział czasowy

Od –1 780 000 000 lat
do 1 780 000 000 lat

Innymi typami danych data/czas są abstime i reltime. Są to typy o mniejszej dokładności.
Jednakże podane tu typy są wewnętrznymi typami PostgreSQL i mogą — oba lub jeden z nich
— zniknąć w przyszłych jego wersjach. Zaleca się zatem, aby projektując nowe aplikacje, pamię-
tać o typach danych zgodnych z SQL oraz aby tak szybko, jak to możliwe, przekształcić te nie za-
lecane typy występujące w starych aplikacjach.

W PostgreSQL można wprowadzać daty w różnych formatach, miedzy innymi w formacie ISO-
8601, tradycyjnym formacie SQL, oryginalnym formacie PostgreSQL itd. Kilka formatów daty
wymieniono w tabeli 3.15. Odnoszą się one do typów danych date i timestamp.

Tabela 3.15. Poprawne formaty daty

Przykładowy format

Opis

July 1, 2001

Nazwa miesiąca, numer dnia i rok

Sunday July 1, 2001

Nazwa dnia tygodnia, nazwa miesiąca, dzień i rok

July 15, 01 BC

Nazwa miesiąca, numer dnia i rok przed naszą erą

2001-07-01

Format standardu ISO-8601: zapisane numerycznie rok, miesiąc i dzień

20010715

ISO-8601: sformatowana numerycznie jako pełny rok, miesiąc, dzień

010715

ISO-8601: sformatowana numerycznie jako rok zapisany dwucyfrowo, miesiąc, dzień

7/01/2001

Format amerykański: zapisane numerycznie miesiąc, dzień i rok

1/7/2001

Format europejski: zapisane numerycznie dzień, miesiąc i rok

2001.182

Format numeryczny, z pełnym rokiem i sekwencyjnie podanym numerem dnia w roku

Określając nazwę miesiąca w wartości daty wprowadzanej do PostgreSQL można podać pełną na-
zwę miesiąca lub wybrać ją ze zbioru skrótów zdefiniowanych dla każdego miesiąca. Skróty te
wymieniono w tabeli 3.16.

Tabela 3.16. Skróty nazw miesiąca

Miesiąc

Skrót

JanuaryJan

FebruaryFeb

March

Mar

April

Apr

MayMay

June

Jun

JulyJul

August

Aug

September

Sep, Sept

October

Oct

November

Nov

December

Dec

W tabeli 3.17 wymieniono skróty nazw dni tygodnia.

Tabela 3.17. Skróty dla dni tygodnia

Dzień

Skrót

SundaySun

MondayMon

TuesdayTue, Tues

WednesdayWed, Weds

ThursdayThu, Thur, Thurs

FridayFri

SaturdaySat

Pomimo dużej różnorodności sposobów, na jakie PostgreSQL może interpretować wartości daty,
są one zawsze zapisywane jednolicie i będą zwracane w spójnym formacie. Tak więc dysponuje-
my wieloma metodami dostosowania do własnych potrzeb domyślnego formatu, w którym będą
zwracane wartości daty i czasu.

Chociaż w trakcie wybierania danych, za pomocą kilku funkcji formatowania (na przykład
to_char()

) zawsze można sformatować wartości daty, to jednak skonfigurowanie

własnych ustawień domyślnych, tak, aby były jak najbardziej zbliżone do najczęściej
wykorzystywanych konwencji, jest bardziej efektywne niż konieczność ręcznej konwersji
typu i formatowania tekstu.

W celu ustawienia ogólnego formatu wyjściowego data/czas można, za pomocą polecenie SET,
ustawić wartość parametru DATESTYLE. Zmienna ta może zostać ustawiona na jeden z czterech
dostępnych, ogólnych stylów, pokazanych w tabeli 3.18.

Tabela 3.18. Formaty wynikowe daty

Format ogólny

Opis

Przykład

ISO

Standard ISO-8601

2001-06-25 12:24:00-07

SQL

Tradycyjny styl SQL

06/25/2001 12:24:00.00 PDT

Postgres

Oryginalny styl PostgreSQL

Mon 25 Jun 12:24:00 2001 PDT

German

Regionalny styl dla Niemiec

25.06.2001 12:24:00.00 PDT

Dla przykładu, w celu ustawienia daty dla SQL, można wykorzystać następującą instrukcję SQL:

booktown=#

SET DATESTYLE TO SQL;

SET VARIABLE

Jeżeli po ustawieniu tej zmiennej wykonamy zapytanie SELECT current_timestamp, Post-
greSQL powinien zwrócić bieżący czas, wykorzystując żądany format ISO:

booktown=#

SELECT current_timestamp;

timestamptz
-------------------------------
06/16/2002 14:32:49.13293 GMT
(1 row)

Podczas działania PostgreSQL można wykorzystać polecenie SHOW do wyświetlenia bieżącej
wartości zmiennej DATESTYLE:

booktown=#

SHOW DATESTYLE;

NOTICE: DateStyle is SQL with US (NonEuropean) conventions
SHOW VARIABLE

Format wyjściowy daty PostgreSQL posiada także dwa inne warianty, pokazane w tabeli 3.19: eu-
ropejski i nie-europejski (amerykański), które bardziej szczegółowo opisują, jak wyświetlać datę.
Określają one, czy w formacie daty najpierw jest dzień, po którym następuje miesiąc, czy też na
odwrót. Można zastosować ten wariant oprócz poprzednich czterech formatów ogólnych za pomo-
cą tej samej instrukcji SET DATESTYLE. W takim przypadku wariant ten nie zmodyfikuje wy-
branego formatu ogólnego, z wyjątkiem ustawień dotyczących miesiąca i dnia.

Tabela 3.19. Rozszerzone formaty wynikowe daty

Format miesiąc/dzień

Opis

Przykład

europejski

dzień/miesiąc/rok

12/07/2001 17:34:50.00 MET

amerykański (nieeuropejski)

miesiąc/dzień/rok

07/12/2001 17:34:50.00 PST

Ponadto można ustawić zarówno format ogólny, jak i konwencję dzień/miesiąc poprzez ustawienie
obydwu zmiennych, przekazując obie te zmienne rozdzielone przecinkiem do polecenia SET. Ko-
lejność zmiennych nie jest ważna dla polecenia SET tak długo, jak długo zmienne wzajemnie się
nie wykluczają (na przykład SQL i ISO), jak widać w przykładzie 3.26.

Przykład 3.26. Ustawianie formatów daty

booktown=#

SET DATESTYLE TO ISO,US;

SET VARIABLE
booktown=#

SHOW DATESTYLE;

NOTICE: DateStyle is ISO with US (NonEuropean) conventions
SHOW VARIABLE
booktown=#

SET DATESTYLE TO NONEUROPEAN, GERMAN;

SET VARIABLE
booktown=#

SHOW DATESTYLE;

NOTICE: DateStyle is German with European conventions
SHOW VARIABLE

Jeżeli nie został określony format miesiąc/dzień, wybrany będzie zwykle rozsądny format domyśl-
ny (na przykład dla formatu regionalnego Niemiec wartością domyślną jest format europejski).

Chociaż SET DATESTYLE jest wygodnym sposobem ustawienia wynikowego formatu daty, to
należy zauważyć, że jest to zmienna trybu wykonania (ang. run-time), co oznacza, że istnieje ona
tylko przez okres trwania sesji połączenia z bazą danych. Dostępne są dwie metody pozwalające
określić wartość domyślną dla zmiennej DATESTYLE, dzięki czemu można uniknąć jawnego
ustawiania zmiennej dla każdej nowej sesji.

• Możemy zmienić zmienną środowiskową PGDATESTYLE na serwerze, na którym działa

postmaster. Na przykład, za pomocą powłoki bash, do pliku .bash_profile użytkownika
postgres można dodać wiersz export PGDATESTYLE="SQL US". Kiedy użytkownik
postgres uruchomi postmaster, zmienna PGDATESTYLE zostanie odczytana i zastosowana
globalnie do każdego formatowania daty i czasu wykonywanego przez PostgreSQL.

• Jeżeli chcemy, aby to klient (a nie serwer) konfigurował styl wyświetlania daty i czasu, to możemy

zmienić zmienną środowiskową PGDATESTYLE wykorzystywaną przez aplikację kliencką
(zakładając, że została ona napisana za pomocą biblioteki libpq) w momencie rozpoczynania sesji.
Na przykład, za pomocą polecenia export możemy ustawić w wierszu polecenia bash zmienną
PGDATESTYLE

, zanim uruchamiający się psql ustawi format do wykorzystania.

Wartości czasu, podobnie jak wartości daty, mogą być wprowadzane do tabeli na wiele sposobów.
Powszechnie wykorzystywane formaty wymieniono w tabeli 3.20. Stosuje się je do wartości typu
time

i time with time zone.

Tabela 3.20. Poprawne formaty czasu

Przykładowy format

Opis

01:24

ISO-8601, z dokładnością do minut

01:24 AM

Równoważne z poprzednim formatem („AM” jest dołączone jedynie w celu
zwiększenia czytelności i nie wpływa na wartość)

01:24 PM

Równoważne z kolejnym z przedstawionych tu formatów (13:24); w celu użycia
„PM” godzina musi być mniejsza lub równa 12)

13:24

Czas 24-godzinny; format równoważny z poprzednim (01:24 PM)

01:24:11

ISO-8601, z dokładnością do sekund

01:24;11.112

ISO-8601, z dokładnością do mikrosekund

012411

ISO-8601, z dokładnością do sekund; format numeryczny

W PostgreSQL możliwe jest bardziej szczegółowe opisanie wartości czasu. Umożliwiający to format
definiuje się jako time with time zone; wprowadza on dodatkowe parametry strefy czasowej
po wartości czasu. Obsługiwane formaty pokazano w tabeli 3.21.

Tabela 3.21. Poprawne formaty strefy czasowej

Przykładowy format

Opis

01:24:11-7

ISO-8601, GMT + 7 godzin

01:24:11-07:00

ISO-8601, GMT + 7 godzin, zero minut

01:24:11-0700

ISO-8601, GMT + 7 godzin, zero minut

13:24:11 PST

ISO-8601, Standardowy Czas Pacyfiku (ang. Pacific Standard Time), GMT + 8 godzin

PostgreSQL umożliwia wykorzystanie wszystkich skrótów stref czasowych standardu ISO.

Typ danych time with time zone jest obsługiwany przez PostgreSQL głównie w celu do-
stosowania się do istniejących standardów SQL i w celu osiągnięcia zgodności z innymi systema-
mi zarządzania bazami danych. Jeżeli istnieje potrzeba pracy z użyciem stref czasowych, zaleca
się wykorzystanie typu danych timestamp omawianego w podrozdziale zatytułowanym „Znacz-
niki czasu”. Głównym powodem jest fakt, że z powodu stosowania czasu letniego strefy czasowe
nie zawsze mogą być właściwie zinterpretowane bez podania skojarzonej daty.

PostgreSQL śledzi całą informację dotyczącą stref czasowych jako numeryczne przesunięcie
względem czasu GMT (ang. Greenwich Mean Time), który jest również znany jako UTC (ang.
Universal Coordinated Time). Domyślnie wyświetlanie czasu w PostgreSQL będzie wykorzysty-
wać strefę czasową, skonfigurowaną w systemie operacyjnym serwera użytkownika. Jeżeli chcie-
libyśmy, aby wartość czasu związana była z inną strefą czasową, mamy do dyspozycji cztery spo-
soby modyfikacji wyjścia. Możemy mianowicie:

Ustawić zmienną środowiskową TZ na serwerze

Zmienna ta jest uznawana przez proces serwera jako domyślna strefa czasowa w chwili
rozpoczynania pracy procesu postmaster. Można ją ustawić na przykład w pliku .bash_profile
użytkownika postgres, za pomocą polecenia bash export TZ='zone'.

Ustawić zmienną środowiskową PGTZ w aplikacji klienckiej

Jeżeli ustawiona zostanie zmienna środowiskowa PGTZ, może być ona odczytana przez każdą
aplikację kliencką napisaną z wykorzystaniem libpq, po czym może być zinterpretowana jako
domyślna strefa czasowa klienta.

Wykorzystać instrukcję SQL SET TIMEZONE TO

Ta instrukcja SQL ustawia strefę czasową sesji na określoną strefę (na przykład SET TIMEZONE
TO 'UTC'

Wykorzystać klauzulę SQL AT TIME ZONE

Ta klauzula SQL92 może być użyta do określenia strefy jako tekstowej strefy czasowej
(na przykład PST) lub jako przedziału (na przykład interval('-07:00')). Klauzula ta
może być zastosowana w obrębie instrukcji SQL po wartości zawierającej etykietę czasową
(na przykład SELECT my_timestamp AT TIME ZONE 'PST').

Kiedy zmienna strefy czasowej zostanie ustawiona na niepoprawną strefę czasową,
większość systemów domyślnie ustawi wartość GMT. Ponadto, jeżeli podczas
konfigurowania PostgreSQL ustawiona została opcja kompilatora USE_AUSTRALIAN_RULES,
strefa czasowa EST będzie odnosiła się do czasu urzędowego wschodnioaustralijskiego
(ang. Australian Eastern Standard Time) — z przesunięciem wynoszącym +10:00 godzin
względem GMT — a nie do czasu urzędowego wschodnioamerykańskiego
(ang. U.S. Eastern Standard Time).

Znaczniki czasu (timestamp) PostgreSQL łączą w pojedynczy typ danych funkcje typów date
i time PostgreSQL. Składnia wartości znacznika czasu obejmuje poprawny format daty, po któ-
rym następuje przynajmniej jeden znak spacji oraz poprawny format czasu. Ostatnim, opcjonal-
nym elementem jest wartość strefy czasowej, jeżeli została ona określona.

W ten sposób obsługiwana jest każda kombinacja formatów daty i czasu wymienionych w tabeli
3.15 i tabeli 3.20. W tabeli 3.22 przedstawiono niektóre przykłady poprawnych formatów znaczni-
ka czasowego.

Tabela 3.22. Niektóre poprawne formaty etykiety czasowej

Przykładowy format

Opis

1980-06-25 11:11-7

Format danych ISO-8601, z dokładnością do minut i strefą czasową PST

25/06/1980 12:24:11.112

Europejski format danych, z dokładnością do mikrosekund

06/25/1980 23:11

Amerykański format danych, z dokładnością do minut w systemie 24-godzinnym

25.06.1980 23:11:12 PM

Niemiecki regionalny format danych, z dokładnością do sekund i dołączonym
symbolem PM

Chociaż PostgreSQL obsługuje składnię tworzenia kolumny lub wartości za pomocą typu
timestamp without time zone

, to w przypadku PostgreSQL 7.1.2 wynikowy typ

danych nadal zawiera strefę czasową.

Standard SQL92 określa typ danych interval, który reprezentuje stały okres czasu. Z charakteru
przedziału czasowego wynika, że reprezentuje on jedynie ilość czasu i nie zaczyna się ani nie koń-
czy na jakiejś ustalonej dacie lub czasie. Przedziały czasowe mogą być użyteczne do obliczania
nowej daty lub czasu poprzez ich dodawanie do lub odejmowanie od początkowych wartości daty
i czasu. Mogą one być również przydatne do szybkiego określenia dokładnego przedziału czaso-
wego pomiędzy dwoma wartościami daty lub czasu. Można to osiągnąć poprzez odejmowanie
wartości dat, wartości czasów lub przedziałów czasowych.

Przedział czasowy w PostgreSQL mogą określać następujące dwa warianty składniowe:

qty unit [ ago ]

qty1 unit [, qty2 unit2 ... ] [ ago ]

Oto znaczenie ich elementów:

qty

Określa wielkość przedziału czasowego, która może być liczbą całkowitą lub — w przypadku
mikrosekund — liczbą zmiennoprzecinkową; dokładne znaczenie tej liczby jest wyjaśniane
przez następny parametr unit.

unit

Określa argument qty (podając jednostkę czasu). Parametr unit może być jednym
z następujących słów kluczowych: second, minute, day, week, month, year, decade,
century

, millenium. Może on być również skrótem (dowolnie krótkim, pod warunkiem,

że nie zostanie pomylony z innym słowem kluczowym) lub liczbą mnogą poprzednio
wspomnianych jednostek czasu.

ago

Opcjonalne słowo kluczowe przedziału czasowego ago określa, czy użytkownik opisuje okres
czasu przed czy po skojarzonym czasie. Można je traktować jako znak ujemny dla typów daty
i czasu.

W przykładzie 3.27 pokazano składnię funkcjonalną połączonych wartości date i interval.
Można zauważyć, że odejmowanie zanegowanego przedziału czasowego (na przykład takiego, który
posiada termin ago) jest funkcjonalnie identyczne z dodawaniem zwykłego przedziału czasowego.
Można to traktować podobnie, jak wynik dodawania liczb ujemnych do wartości całkowitych.

Przykład 3.27. Interpretacja formatów przedziału czasowego

booktown=#

SELECT date('1980-06-25');

date
------------
1980-06-25
(1 row)

booktown=#

SELECT interval('21 years 8 days');

interval
-----------------
21 years 8 days
(1 row)

booktown=#

SELECT date('1980-06-25') + interval('21 years 8 days')

booktown-#

AS spanned_date;

spanned_date
------------------------
2001-07-03 00:00:00-07
(1 row)

booktown=#

SELECT date('1980-06-25') - interval('21 years 8 days ago')

booktown-#

AS twice_inverted_interval_date;

twice_inverted_interval_date
------------------------------
2001-07-03 00:00:00-07
(1 row)

W wersji 7.2 parametry funkcji interval powinny zostać przekazane w apostrofach, nie w nawiasie
i apostrofach. Poprawna instrukcja SELECT przybierze postać SELECT interval'21 years
8 days';

— przyp. red.

PostgreSQL obsługuje wiele specjalnych stałych wykorzystywanych przy odwoływaniu się do dat
i czasów. Stałe te reprezentują popularne wartości daty i czasu, takie jak now, tomorrow
i yesterday. Predefiniowane stałe daty i czasu obsługiwane przez PostgreSQL wymieniono
w tabeli 3.23.

Tabela 3.23. Stałe daty i czasu

Stała

Opis

current

Czas bieżącej odroczonej transakcji. Stała current, w przeciwieństwie do now,
nie jest znacznikiem czasowym. Reprezentuje bieżący czas systemowy i można się
do niego odwoływać bez względu na to, jaki jest to czas

epoch

1970-01-01 00:00:00+00 (Uniksowe „Urodziny”)

infinity

Stała abstrakcyjna, która oznacza czas późniejszy niż wszystkie inne poprawne
wartości daty i czasu

-infinity

Stała abstrakcyjna, która oznacza czas wcześniejszy niż wszystkie inne poprawne
wartości daty i czasu

now

Znacznik czasowy bieżącej transakcji

today

Północ bieżącego dnia

tomorrow

Północ następnego dnia po dniu bieżącym

yesterday

Północ poprzedniego dnia przed dniem bieżącym

PostgreSQL obsługuje również trzy wbudowane funkcje do pobierania bieżącego czasu, daty
i znacznika czasowego. Są one trafnie nazwane current_date, current_time i current_
timestamp

Etykiety czasowe now i current, jeśli patrzymy jedynie na ich nazwy, mogą wydawać się
identyczne. Różnią się one jednakże znacznie w aspekcie zapisywania ich w tabeli. Stała now jest
tłumaczona na etykietę czasową czasu systemowego przy wykonaniu jakiegokolwiek polecenia,
które się do niej odwołuje (na przykład czas wprowadzenia do bazy, jeżeli odwołanie do etykiety
now

nastąpiło w instrukcji INSERT). Stała current — przeciwnie — ponieważ jest odroczo-

nym identyfikatorem, więc faktycznie w bazie danych pojawi się jako fraza current. Następnie
może ona zostać przetłumaczona (na przykład za pomocą funkcji to_char()) na znacznik cza-
sowy skojarzony z czasem transakcji każdego zapytania, które żąda tej wartości.

Innymi słowy, stała current zawsze, kiedy zostanie wywołana, poda użytkownikowi „bieżący”
czas, bez względu na to, kiedy została zapisana w tabeli. Stała current może być wykorzystana
w specjalnych sytuacjach, takich jak śledzenie procesu, kiedy może istnieć potrzeba obliczenia róż-
nicy pomiędzy etykietą czasową utworzoną za pomocą stałej now a bieżącą datą i czasem, w celu
określenia całkowitego czasu trwania procesu. W przykładzie 3.28 przedstawiono wykorzystanie
stałych now i current do utworzenia dziennika zdarzeń. Po pierwsze tworzona jest tabela, aby
w niej ulokować nazwę zadania, datę i czas jego rozpoczęcia oraz datę i czas jego zakończenia. Na-
stępnie dodawane są do niej dwa zadania z wykorzystaniem stałej now (aby ustawić datę rozpoczę-
cia) oraz stałej current (w celu ustawienia daty zakończenia). Zadania te dodaje się po to, by po-
kazać, że ani jedno z nich nie jest zakończone. Gdyby zadanie zostało zakończone, tabela mogłaby
być uaktualniona, aby pokazać etykietę czasową now dla kolumny timefinished tego zadania.

Użycie stałych czasu/daty wymaga ujęcia ich nazw w pojedyncze apostrofy.
Poprawną reprezentację takich stałych przedstawiono w przykładzie 3.28.

Przykład 3.28. Użycie stałych current i now

booktown=#

CREATE TABLE tasklog

booktown=#

(taskname char(15),

booktown=#

timebegun timestamp,

booktown=#

timefinished timestamp);

CREATE
booktown=#

INSERT INTO tasklog VALUES

booktown=#

('delivery', 'now', 'current');

INSERT 169936 1
booktown=#

INSERT INTO tasklog VALUES

booktown=#

('remodeling', 'now', 'current');

INSERT 169937 1
booktown=#

SELECT taskname, timefinished - timebegun AS timespent FROM tasklog;

taskname | timespent
-----------------+-----------
delivery | 00:15:32
remodeling | 00:04:42
(2 rows)

Stałą now wykorzystujemy zwykle zapisując w tabeli etykietę czasową transakcji lub nawet funk-
cję current_timestamp, która jest równoważna wynikowi now. W przykładzie 3.29 pokaza-
no, jak potencjalnie katastrofalne skutki projektowe mogłoby spowodować niewłaściwe rozumie-
nie użycia tych stałych. Przedstawiono parę instrukcji INSERT, z których jedna wykorzystuje
stałą now a druga current. Obserwując pierwszy wiersz zwracany z dwóch zapytań (wiersz za-
wierający etykietę czasową current) zauważymy, że zmienia się on w każdym zapytaniu, poka-
zując uaktualniony czas systemowy, podczas gdy drugi wiersz pozostaje taki sam (jest to wiersz,
w którym użyto stałej now).

Przykład 3.29. Porównanie stałej now z current

booktown=#

INSERT INTO shipments (customer_id, isbn, ship_date)

booktown-#

VALUES (1, '039480001X', 'current');

INSERT 3391221 1
booktown=#

INSERT INTO shipments (customer_id, isbn, ship_date)

booktown-#

VALUES (2, '0394800753', 'now');

INSERT 3391222 1
booktown=#

SELECT isbn, ship_date FROM shipments;

isbn | ship_date
------------+------------------------
039480001X | current
0394800753 | 2001-08-10 18:17:49-07
(2 rows)

booktown=#

SELECT isbn,

booktown-#

to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS')

booktown-#

AS value

booktown-#

FROM shipments;

isbn | value
------------+---------------------
039480001X | 2001-08-10 18:21:22
0394800753 | 2001-08-10 18:17:49
(2 rows)

booktown=#

SELECT isbn, to_char(ship_date, 'YYYY-MM-DD HH24:MI:SS') AS value

booktown-#

FROM shipments;

isbn | value
------------+---------------------
039480001X | 2001-08-06 16:29:21
0451160916 | 2001-08-14 17:36:41
0590445065 | 2001-08-12 19:09:47
0694003611 | 2001-08-11 17:52:34
0679803335 | 2001-08-09 14:30:07
0760720002 | 2001-08-05 16:34:04
0394900014 | 2001-08-11 20:34:08
0385121679 | 2001-08-08 16:53:46
1885418035 | 2001-08-14 20:41:39
0929605942 | 2001-08-10 15:29:42
0441172717 | 2001-08-14 15:42:58
044100590X | 2001-08-12 15:46:35
0451457994 | 2001-08-07 18:56:42
0451198492 | 2001-08-15 21:02:01
0823015505 | 2001-08-14 14:33:47
039480001X | 2001-08-10 20:47:52
0451160916 | 2001-08-14 20:45:51
0590445065 | 2001-08-14 20:49:00
0694003611 | 2001-08-06 14:49:44
039480001X | 2001-08-13 16:42:10
0451160916 | 2001-08-08 15:36:44
0590445065 | 2001-08-10 14:29:52
0694003611 | 2001-08-15 18:57:40
0679803335 | 2001-08-09 16:30:46
0760720002 | 2001-08-07 20:00:48
0394900014 | 2001-08-13 16:47:04
0385121679 | 2001-08-12 20:39:22
1885418035 | 2001-08-07 18:31:57
039480001X | 2001-09-14 23:46:32
039480001X | 2001-09-15 00:42:22
0394800753 | 2001-08-11 16:55:05
0394800753 | 2001-08-07 17:58:36
0394800753 | 2001-08-06 18:46:36
0394800753 | 2001-08-08 17:46:13
0394800753 | 2001-09-22 18:23:28
0394800753 | 2001-09-23 03:58:56

(36 rows)

Typy geometryczne w PostgreSQL reprezentują dwuwymiarowe obiekty przestrzenne. Typy te nie
są standardowymi typami danych SQL i nie będą dokładnie omawiane w tej książce. W tabeli 3.24
przedstawiono krótki przegląd wszystkich dostępnych typów geometrycznych.

Oryginalny model relacyjny określa, że wartości reprezentowane przez kolumny w tabeli stanowią
niepodzielne elementy danych. Systemy obiektowo-relacyjnych baz danych, takie jak PostgreSQL,
dopuszczają wykorzystanie wartości podzielnych w strukturach danych nazywanych tablicami.

Tabela 3.24. Typy geometryczne

Nazwa typu

Wielkość

Opis

Składnia

point

16 bajtów

Obiekt bezwymiarowy, nie posiadający
żadnych właściwości poza swoim
położeniem; x i y są liczbami
zmiennoprzecinkowymi

(x, y)

lseg

32 bajtyOdcinek prostej; podane punkty

są punktami

końcowymi odcinka

((x1, y1)

, (x2, y2))

box

32 bajty

Prostokątna ramka; podane punkty
stanowią przeciwne wierzchołki ramki

((x1, y1)

, (x2, y2))

path

4 + 32

∗ n bajtów Zamknięty tor (podobny do wielokąta);

połączony zbiór n punktów

((x1, y1)

, ...)

path

4 + 32

∗ n bajtów Otwarty tor; połączony zbiór n punktów

[(x1, y1)

, ...]

polygon

4 + 32

∗ n bajtów Wielokąt (podobny do zamkniętego toru),

posiadający n punktów końcowych,
definiujących odcinki prostej tworzące
brzegi wielokąta

((x1, y1)

, ...)

circle

24 bajtyPunkt (x, y) stanowi środek, a r promień

okręgu

<(x, y)

, r>

Tablica jest zbiorem wartości danych, do których można się odwołać za pomocą pojedynczego
identyfikatora. Może być ona zbiorem wartości danych posiadających wbudowany lub zdefinio-
wany przez użytkownika typ, przy zastrzeżeniu, że wszystkie wartości w tablicy muszą być tego
samego typu. Dostęp do tablicy można uzyskać z tabeli poprzez notację indeksową z użyciem na-
wiasów kwadratowych (na przykład moja_tablica[0]). Można również wykorzystać stałą
tablicową z użyciem nawiasów klamrowych ujętych w pojedyncze apostrofy (na przykład
'{wartość_1,wartość_2,wartość_3}'

&!"!")

Zasady określania składni tablicy pozwalają definiować ją jako tablicę o stałej lub zmiennej długo-
ści, chociaż dla PostgreSQL 7.1.2 ograniczenie dotyczące rozmiaru tablicy o stałej długości nie
jest egzekwowane. Oznacza to, że tablica zawsze posiada stałą liczbę elementów, jednakże nadal
można dynamicznie zmieniać jej rozmiar. Na przykład dopuszczalne jest, aby pojedyncza kolumna
zdefiniowana jako tablica zawierała trzy wartości w pierwszym rekordzie, cztery wartości w dru-
gim i nie zawierała żadnej wartości w trzecim.

Dodatkowo tablice mogą być zdefiniowane jako wielowymiarowe, co oznacza, że każdy element
tablicy może w rzeczywistości reprezentować inną tablicę, a nie wartość niepodzielną. Wartości,
które zostaną wybrane z tablicy wielowymiarowej, będą umieszczone w nawiasach klamrowych
i oddzielone przecinkami:

booktown=#

SELECT books FROM favorite_books WHERE employee_id=102;

books
------------------------------------------------------------------------------
{"The Hitchhiker's Guide to the Galaxy","The Restauraunt at the End of the

åUniverse"}

(1 row)

W celu faktycznego wstawienia wartości tablicowych do kolumny tabeli potrzebna jest metoda
odwołania się w instrukcji SQL do kilku wartości jako do tablicy. Formalną składnię stałej tabli-
cowej stanowi grupa wartości oddzielonych separatorami (dla wbudowanych typów danych są to
przecinki), ujętych w nawiasy klamrowe — {} — które z kolei są ujęte w pojedyncze apostrofy:

'{wartość1 , wartość2 [, ...]}'

Wartościami w tej składni mogą być dowolne, poprawne typy danych PostgreSQL. Ponieważ cała
tablica jest ujęta w pojedyncze apostrofy, aby w treści tablicy umieścić znaki apostrofów, należy po-
przedzić je znakiem ukośnika, podobnie jak to ma miejsce w przypadku stałych-ciągów znaków.

Wykorzystywanie przecinków do oddzielania wartości stwarza interesujący problem dotyczący
użycia ciągów znaków, które same zawierają przecinki, ponieważ — jeżeli przecinki nie zostaną
ujęte w pojedyncze apostrofy, będą one interpretowane jako separatory. Niemniej jednak, jak już
wspomniano, pojedyncze apostrofy ograniczają tablicę, a nie wartości tablicy.

Metoda, jaką stosuje się w PostgreSQL do rozwiązania tego problemu, polega na wykorzystywa-
niu cudzysłowów, w które ujmuje się stałe ciągu znakowego, podczas gdy pojedyncze apostrofy
byłyby zwyczajnie użyte na zewnątrz treści tablicy:

'{" wartość1", " wartość2, zawierająca przecinek"}'

Należy pamiętać, że aby tablice były poprawnie zinterpretowane przez PostgreSQL, nawiasy
klamrowe muszą być ujęte w pojedyncze apostrofy. Stałe tablicowe mogą być uważane za podob-
ne do specjalnego typu stałych łańcuchowych, które są interpretowane jako tablice, w zależności
od tego, gdzie są wykorzystywane (na przykład kiedy używane są do wprowadzania rekordów do
kolumny docelowej typu tablicowego). Jest tak, ponieważ — jeżeli stała w tym formacie nie jest
wykorzystywana w kontekście tablicowym, będzie ona zinterpretowana przez PostgreSQL jako
zwykła stała łańcuchowa (jako że jest ujęta w pojedyncze apostrofy), przypadkowo zawierająca
nawiasy klamrowe.

PostgreSQL obsługuje trzy oddzielne konwencje koercji typu (nazywane również rzutowaniem
typu lub jawnym rzutowaniem typu). Koercja typu jest dość brzydkim określeniem, odnoszącym
się do stosowanej w PostgreSQL metody zmiany typu danych dla wartości. W obrębie instrukcji
SQL daje to efekt końcowy jawnego tworzenia stałej dowolnego typu.

Ogólnie, w celu konwersji wartości zapisanej w stałej łańcuchowej na inny typ może zostać wyko-
rzystana dowolna z następujących trzech metod:

• type 'value'
• 'value'::type
• CAST ('value' AS type)

W przypadku stałych numerycznych, dla których chcemy przeprowadzić konwersję na ciąg zna-
ków, zachodzi konieczność użycia jednej z następujących form składni:

• value::type
• CAST (value AS type)

W składniach tych value reprezentuje stałą, której typ danych chcemy zmodyfikować, a type
— typ, na który chcemy zmienić typ value.

Należy pamiętać, że typ money nie jest zalecany, a jego konwersja — stosunkowo trudna.

Stałe nie są jedynymi wartościami danych, dla których można dokonać koercji typów. Konwersji
można poddawać kolumny zbiorów danych zwracanych przez zapytanie SQL poprzez wykorzy-
stanie ich identyfikatora w jednej z następujących form składni:

• identifier::type
• CAST (identifier AS type)

Należy pamiętać, że nie dla każdego typu danych można dokonać jego koercji na inny typ danych.
Na przykład, nie istnieje sensowny sposób przekształcenia ciągu znakowego abcd na binarny typ
bit. Niepoprawna konwersja danych spowoduje błąd systemu PostgreSQL. Często dokonywanymi
i poprawnymi są operacje konwersji z ciągu znakowego typu data/czas lub typu numerycznego na
tekst oraz z ciągów znakowych na wartości numeryczne.

Oprócz tych konwencji konwersji typów danych istnieją pewne funkcje, które można wywołać
w celu osiągnięcia zasadniczo takiego samego efektu, jak poprzez jawną operację konwersji za
pomocą dowolnej z poprzednio wspomnianych form. Funkcje te często noszą nazwę typu (tak jak
funkcja text()), podczas gdy inne są nazywane bardziej specyficznie (tak jak bitfro-
mint4()

). W przykładzie 3.30 przedstawiono taką funkcję, która przekształca liczbę całkowitą

1000 na ciąg znakowy typu text reprezentujący znaki wchodzące w skład liczby 1000.

Przykład 3.30. Wykorzystywanie funkcji konwersji typu

booktown=#

SELECT text(1000)

booktown-#

AS explicit_text;

explicit_text
---------------
1000
(1 row)

Z powodu sprzecznej semantyki rozpoznawanej przez analizator składni PostgreSQL format koer-
cji typu type 'value' może zostać wykorzystany jedynie do określenia typu danych pojedyn-
czej wartości (na przykład ujętej w pojedyncze apostrofy stałej ciągu znakowego). Inne dostępne
metody koercji typu ('value'::type, CAST('value' AS type) oraz funkcje konwersji
typu (tam, gdzie można je zastosować) mogą być użyte do określenia typu dowolnych wyrażeń.

Jest tak częściowo dlatego, że próba przekazania po typie danych wyrażenia zgrupowanego (na
przykład w nawiasach) spowoduje, że PostgreSQL będzie oczekiwał funkcji o takiej nazwie, jak
dostarczony typ danych (co często spowoduje błąd). Natomiast wszystkie inne metody są syntak-
tycznie poprawne dla zgrupowanych wyrażeń.

booktown=#

SELECT 1 + integer ('1' || '2') AS add_one_to_twelve;

ERROR: Function 'integer(text)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
booktown=#

SELECT 1 + ('1' || '2')::integer AS add_one_to_twelve;

add_one_to_twelve
-------------------
13
(1 row)

booktown=#

SELECT 1 + CAST('1' || '2' AS integer) AS add_on_to_twelve;

add_on_to_twelve
------------------
13
(1 row)

Ci, którzy już znają język SQL lub inne SZBD, prawdopodobnie posiadają również sporą wiedzę
dotyczącą wielu pojęć dotyczących relacyjnych baz danych przedstawionych w tym rozdziale.
Jednakże każdy SZBD na poziomie systemowym obsługuje tabele w inny sposób. W tym podroz-
dziale przyjrzymy się bliżej sposobowi implementacji tabel w PostgreSQL.

PostgreSQL definiuje we wszystkich tabelach szereg kolumn systemowych, które są zwykle niewi-
doczne dla użytkownika (na przykład nie zostaną one pokazane przez zapytania, chyba że zostanie to
wyraźnie zażądane). Kolumny te zawierają metadane dotyczące zawartości wierszy tabeli. Wiele
z nich zawiera dane, które mogą pomóc rozróżnić krotki (indywidualne reprezentacje wiersza) przy
pracy z blokami transakcji (więcej informacji na temat transakcji znajdziemy w rozdziale 7.).

W wyniku istnienia tych definiowanych przez system kolumn obok kolumn definiowanych przez
użytkownika każdy wprowadzony wiersz będzie posiadał wartości w każdej z kolumn przedsta-
wionych w tabeli 3.25.

Tabela 3.25. Kolumny systemowe

Kolumna

Opis

oid

(identyfikator obiektu)

Unikalny identyfikator obiektu-wiersza. PostgreSQL automatycznie dodaje
tę 4-bajtową liczbę do wszystkich wierszy. Nigdy nie jest ponownie używany
w ramach tej samej tabeli

tableoid
(identyfikator obiektu tabeli)

Identyfikator obiektu tabeli, która zawiera wiersz. Nazwa i identyfikator
obiektu tabeli są powiązane poprzez tabelę systemową pg_class

xmin

(minimum transakcji)

Identyfikator transakcji wstawienia krotki

cmin

(minimum polecenia)

Identyfikator polecenia, rozpoczynający się od 0, skojarzony z transakcją
wstawienia krotki

xmax

(maksimum transakcji) Identyfikator transakcji usuwania krotki. Ustawiany jest on na 0,

jeżeli krotka jest widoczna (nie została usunięta)

cmax

(maksimum polecenia) Identyfikator polecenia skojarzony z transakcją usuwania krotki. Tak jak

w przypadku xmax, ustawiany jest na 0, jeżeli krotka jest widoczna

ctid

(identyfikator krotki)

Identyfikator, który opisuje fizyczne położenie krotki w bazie danych. Citd

składa się z pary liczb: numeru bloku oraz indeksu krotki w ramach tego bloku

Jak powiedziano w podrozdziale zatytułowanym „Podstawowe informacje o tabelach”, baza da-
nych przechowuje dane w tabelach, a tabela składa się przynajmniej z jednej nazwanej kolumny.
Tabela nie zawsze musi zawierać wiersze danych. W każdym zapisanym wierszu, dla każdej ko-
lumny istnieje wartość danych (lub wartość NULL).

Jednym z problemów związanych z zarządzaniem tabelą może być rozróżnienie pomiędzy dwoma
wierszami, których wartości kolumn są identyczne. Bardzo użyteczną właściwością PostgreSQL jest
posiadanie przez każdy wiersz swojego własnego numeru identyfikatora obiektu (OID), który w ra-
mach tabeli jest unikalny. Innymi słowy, żadne dwa wiersze w tej samej tabeli nigdy nie powinny
posiadać takich samych identyfikatorów OID. Oznacza to, że nawet jeżeli tabela została zaprojekto-
wana w ten sposób, że dwa wiersze mogłyby być identyczne, to nadal istnieje programowa metoda
stwierdzenia różnicy pomiędzy nimi: jest nią OID. Technikę tę przedstawiono w przykładzie 3.31.

Przykład 3.31. Rozróżnianie wierszy za pomocą OID

testdb=#

SELECT * FROM my_list;

todos
----------------------------------
Correct redundancies in my list.
Correct redundancies in my list.
(2 rows)

testdb=#

SELECT *,oid FROM my_list;

todos | oid
----------------------------------+---------
Correct redundancies in my list. | 3391263
Correct redundancies in my list. | 3391264
(2 rows)

testdb=#

DELETE FROM my_list

testdb-#

WHERE oid = 3391264;

DELETE 1
testdb=#

SELECT *,oid FROM my_list;

todos | oid
----------------------------------+---------
Correct redundancies in my list. | 3391263
(1 row)

Sugerujemy, aby przed rozpoczęciem tworzenia dowolnych tabel poświęcić trochę czasu na zapla-
nowanie zamierzonych obiektów bazy danych, poprzez podjęcie decyzji dotyczących nazw, typów
i przeznaczenia wszystkich kolumn w każdej tabeli. Pomoże to uzyskać spójność ze strukturami
nazewnictwa tabel, co z kolei ułatwi czytanie i konstruowanie czytelnych zapytań i instrukcji.

Poza przeprowadzeniem w pewnym sensie semantycznych rozważań, które przedstawiliśmy (do-
tyczących nazw, typów i przeznaczenia) ważne jest, aby upewnić się, że przejrzyście zdefiniowali-
śmy związki każdej z tabel z innymi tabelami. Jest to ważny element projektowania tabel, pozwa-
lający uniknąć nadmiarowego przedstawiania dużych ilości danych, a z drugiej strony pominięcia
ważnych danych przez niezrozumienie potrzeb, które musi zaspokoić nasza implementacja.

Jako przykład rozważmy ponownie tabelę books bazy danych Book Town, pokazaną w tabeli
3.1. Tabela ta przechowuje dla każdej książki wewnętrzny numer identyfikacyjny, tytuł, numer
identyfikacyjny autora i numer identyfikacyjny tematu. Należy zauważyć, że zamiast zapisywać
nazwisko autora i tekstową reprezentację tematu książki, zapisuje się proste numery identyfikacyj-
ne. Numery te są wykorzystywane do tworzenia związków z dwoma innymi tabelami: authors
i subjects, których częściowa zawartość została pokazana w tabeli 3.26 i 3.27.

Tabela 3.26. Tabela authors

last_name

first_name

1809

Geisel

Theodor Seuss

1111

Denham

Ariel

15990

Bourgeois

Paulette

2031

Brown

Margaret Wise

25041

Margery Williams

Bianco

Alcott

Louisa May

115

Poe

Edgar Allen

Tabela 3.27. Tabela subjects

subject

location

Arts

Creativity St

Children's Books

Kids Ct

Classics

Academic Rd

Computers

Productivity Ave

Drama

Main St

Horror

Black Raven Dr

Science Fiction

Main St

Poprzez utrzymywanie danych dotyczących autora i tematu poza tabelą books, dane te są przecho-
wywane bardziej efektywnie. W przypadku, kiedy z konkretnym tematem trzeba powiązać wiele
książek, należy zapamiętać jedynie wartość pola subject_id, a nie całą informację związaną
z danym tematem. Dzięki temu prostsze staje się także utrzymanie danych skojarzonych z tematami
książek, takich jak położenie w magazynie. Takie dane mogą być aktualizowane w pojedynczej,
małej tabeli, bez konieczności aktualizacji wszystkich rekordów książek. Ta sama ogólna zasada
znajduje zastosowanie do tabeli authors i jej związku z tabelą books poprzez pole author_id.

Przemyślane planowanie może również pomóc w uniknięciu błędów przy wyborze właściwych
typów danych. Na przykład, w tabeli editions numery ISBN są związane z identyfikatorami
książek księgarni Book Town. Na pierwszy rzut oka mogłoby się wydawać, że numer ISBN mógł-
by być reprezentowany za pomocą kolumny typu integer. Przeoczenie projektowe w tym przy-
padku polegałoby nie tylko na tym, że numery ISBN czasami zawierają dane znakowe, ale rów-
nież na tym, że wartość typu integer gubiłaby wszystkie wiodące zera w numerze ISBN (na
przykład numer 0451160916 przyjąłby postać 451160916).

Z tych powodów w administrowaniu bazą danych nie można pominąć procesu dobrego projekto-
wania tabel.

Wyszukiwarka

Podobne podstrony:
Professional Linux Programming, R-04-t, PLP - Rozdział 4: Interfejsy PostgreSQL
Podstawy zarządzania wykład rozdział 05
2 Realizacja pracy licencjackiej rozdziałmetodologiczny (1)id 19659 ppt
Ekonomia rozdzial III
rozdzielczosc
kurs html rozdział II
Podstawy zarządzania wykład rozdział 14
7 Rozdzial5 Jak to dziala
Klimatyzacja Rozdzial5
Polityka gospodarcza Polski w pierwszych dekadach XXI wieku W Michna Rozdział XVII
Ir 1 (R 1) 127 142 Rozdział 09
Bulimia rozdział 5; część 2 program
05 rozdzial 04 nzig3du5fdy5tkt5 Nieznany (2)
PEDAGOGIKA SPOŁECZNA Pilch Lepalczyk skrót 3 pierwszych rozdziałów
Instrukcja 07 Symbole oraz parametry zaworów rozdzielających
04 Rozdział 03 Efektywne rozwiązywanie pewnych typów równań różniczkowych
Kurcz Język a myślenie rozdział 12
Ekonomia zerówka rozdział 8 strona 171
28 rozdzial 27 vmxgkzibmm3xcof4 Nieznany (2)

więcej podobnych podstron