V dnešním díle se podíváme na vytvoření databázových tabulek a na typy proměných, které nám PostgreSQL poskytuje.
23.11.2004 13:00 | MaReK Olšavský | přečteno 35541×
V minulém díle jsem psal o instalaci a prvovytvoření uživatele a databáze pomocí příkazů creteuser a createdb. Pokud si to nepamatujete, podívejte se, protože v tomto pokračování budete potřebovat jak uživatele, tak databázi. Nejdříve napíšu něco o typech (proměnných), které můžete použít. Předpokládám, že není třeba se dnes již zabývat PgSQL před verzí 7, protože tam jsou některé typy trochu jinak.
Proměnné můžeme rozdělit do několika skupin a to celočíselné, v plovoucí desetinné čárce (reálné), časovodatumové a znakové. Pro znalce MySQL mám jednu smutnou zprávu, PgSQL nezná výčtový typ enum (můžete jej ale definovat jako vlastní typ), ale má jiné typy, třeba geometrické, síťové i binární a s autoinkrementačními se zachází jinak, trochu logičtěji, alespoň podle mého názoru. Při definici tabulky můžeme každý typ ještě nějakým způsobem dále modifikovat, třeba zakážeme nevložení hodnoty, či předepíšeme defaultní hodnotu. Občas (většinou) se nám budou tato nastavení překrývat.
Základní typ proměnné, používá se například (a většinou) pro vyjádření pozice, výšky, hmotnosti, ... Tento typ máte k dispozici v několika délkách a volba je jen na Vás, respektive na potřebách Vaší aplikace.
Jméno | Velikost (v bytech) | rozsah |
smallint | 2 byty | -32768 .. +32767 |
integer | 4 byty | -2147483648 .. +2147483647 |
bigint | 8 bytů | -9223372036854775808 .. 9223372036854775807 |
serial | 4 byty | -2147483648 .. +2147483647 |
bigserial | 8 bytů | -9223372036854775808 .. 9223372036854775807 |
Tak toto jsou základní celočíselné typy. Už z názvů je vidět, které lze používat jako autoinkrementační identifikátory. Jsou to serial a bigserial. Troufám si tvrdit, že pro většinu (web)aplikací bude stačit typ serial.
... neboli typy s plovoucí desetinnou čárkou. Nejčastěji používaný typ z číselných. Používá třeba k vyjádření peněz...
Jméno | Velikost (v bytech) | rozsah |
decimal | proměnlivá | (skoro) neomezený |
numeric | proměnlivá | (skoro) neomezený |
real | 4 byty | přesnost na 6 desetinných míst |
double precision | 8 bytů | přesnost na 15 desetinných míst |
money | 4 byty | -21 474 836,48 .. +21 474 836,47 |
Snad mi ti, kteří koukali dokumentaci prominou, že jsem typ money přiřadil k reálným typům, přestože v původní dokumntaci je vyjmut mimo. Je to typ, kdy máte k dispozici napevno 2 desetinná místa. Jeho rozsah ale limituje použití. Pokud budete třeba psát webshop, je málo pravděpodobné, že dojdete na jeho hranice u objednávky a cen zboží, ale kdyby se jednalo třeba o databázi pro makléře, nebo pojišťovny, neřku-li banky, budete na hranici velmi rychle. Potom vám poslouží dobře dva typy, které popíšu dále.
Typy decimal
a numeric
jsou ekvivalentní, umožňující Vám nastavit délku do 1000 číslic a libovolnou přesnost. Jejich nevíhoda je, že pro řazení, porovnávání a další operace jsou o něco pomalejší. Při nastavení můžete mít jedině nenulovou délku a nulový, nebo kladný počet desetinných míst. Definice čísla je tedy v případě celých čísel DECIMAL(početČíslic)
a v případě desetinných DECIMAL(početČíslic, zTohoDesetinných)
.
Řetězcové typy slouží k uložení textů od délky 1 znaku až po snad neomezeno. Obvykle se první dva typy používají do délky 255 znaků, třetí jmenovaný pro neomezené texty a poslední pro binární data. Je to ale spíše otázka zvyku a discipliny, jako asi většina lidí jsem přešel na PgSQL z MySQL, takže používám typy ekvivalentně. Při neudání délky u char se implicitně počítá s jedním znakem a zavádí se speciální typ pro jména s délkou 64 znaků.
Jméno | Použití |
char(n); character(n) | řetězec s pevnou délkou |
varchar(n); character varying(n) | řetězec s proměnnou délkou, maximálně n znaků |
text | řetězec s proměnnou, neomezenou délkou |
char | Jednoznaková proměnná |
name | Proměnná pro jména, pevná délka 64 znaků |
bytea | binární řetězec bez omezení délky |
blob | binární data |
binary large object | binární data jako výše |
Character(n)
a name
mají pevnou délku řetězce a v praxi se to realizuje tak, že proměnné tohoto typu jsou zprava doplněny mezerami až do délky podle definice. Pokud Vás nic nenutí (třeba quota) používat řetězce s proměnnou délkou, je rychlejší používat proměnné s variabilní délkou. Věřte, nebo ne, ale server s nimi pracuje o poznání rychleji.
Pokud budete v svém produktu (lhostejno, či se jedná o PHP, nebo třeba Javu, Python) pracovat s kódováním UTF-8, které nemá všechny znaky s pevnou délkou a nebudete měnit kódování na db serveru z defaultního ISO-8859-1, doporučuji si nechat nějakou rezervu. Já jí dávám na 25%, takže pokud v formuláři mám pole s délkou 100 znaků, je tato položka ukládána do políčka dimenzovaného na 125 znaků. Zatím mi to kupodivu funguje.
Bytea
použijete, když potřebujete uložit binární řetězce, tj. obsaující normální i netisknutelné znaky, jejichž interpretace je závislá na lokálních nastaveních. Protože práce s těmito daty je na samostatnou kapitolku, podíváme se na ní příště.
Bytea
Vám může posloužit k uložení binárních dat, třeba obrázků, do PostgreSQL. Někdy později si můžeme ukázat, jak se to dá zrealizovat v praxi, ale nepovažuji to za moc čistý způsob. Lepší je uložit do PqSQL jen jméno soubotu a mít jej uložený přímo na HDD, protože si nepřiměřeně nezatěžujete server a nezpomalujete jej. Nároky na prostor jsou 4
Tento typ se používá pro přepínače, kde stačí pouze hodnoty Ano a Ne, typ se jmenuje boolean
, pro uložení ano můžeme použít hodnoty TRUE, 't', 'yes', 'y', '1' a pro uložení ne máme k dispozici varianty FALSE, 'f', 'false', 'no', 'n' a '0' (to je nula). Vyjádřeno v diskové kapacitě nám tento typ spotřebuje vždy 1 byte. Pokud si o data řekneme příkazem SELECT
, vrací hodnoty 't' pro ano a 'f' pro ne, ale chování můžeme ovlivnit pomocí příkazu CASE
.
Pokud budete potřebovat uložit přepínačů poněkudsi více, doporučuji použít tento typ. Bitové řetězce obsahují jen hodnoty 1 (jedna) a 0 (nula). Při ukládání hodnot tohoto typu je třeba kus opatrnosti, protože byste si třeba mohli špatně definovat administrátorká práva. Pokud máte pevnou délku bitového řetězce, a zadáte jej kratší, bude zprava doplněn nulami. Pokud se pokusíte uložit řetězec, který obsahuje i něco jiného, než nuly a jedničky, povede to k chybě.
Existují 4 varianty, jak tento typ při vytváření tabulky definovat, jsou to BIT
pro 1 bitovou proměnnou, BIT(n)
pro nbitový řetězec, BIT VARYING(n)
pro bitový řetězec o délce maximálně n a BIT VARYING
pro libovolnou délku.
Zase platí co již bylo řečeno u řetězců, co má pevnou délku, je zpracováno rychleji.
Tento typ můžete samozřejmě ukládat jako řetězec, ale přišli byste o spoustu možností při práci s ním.
Jméno | Použití | Délka | Rozsah | Přesnost |
timestamp (p) [without time zone] | Datum i čas bez časové zóny | 8 bytů | 4 713 př.n.l. .. 1 465 001 n.l. | 1 mikrosekunda/14 číslic |
timestamp (p) with time zone | Datum i čas | 8 bytů | 4713 př.n.l. .. 1 465 001 n.l. | 1 mikrosekunda/14 číslic |
interval | Interval mezi dvěma časy | 12 bytů | -178 000 000 .. 178 000 000 let | 1 mikrosekunda |
date | datum | 4 byty | 4 713 př.n.l. .. 32 767 n.l. | 1 den |
time (p) [without time zone] | čas bez zóny | 8 bytů | 00:00:00 - 23:59:59 | 1 mikrosekunda |
time (p) with time zone | čas se zónou | 12 bytů | 00:00:00+12 .. 23:59:59-12 | 1 mikrosekunda |
U položek time
, timestamp
a interval
můžete parametrem p zadat s jakou přesností potřebujete pracovat, největší mošná přesnost je na mikrosekundy, ale pokud náhodou neukládáte časy na kolo u monopostů F1, nebo IRL, budete stěží potřebovat přesnost větší, než na milisekundy. Časová zóna se udává jako relativní vůči UTC, tj. Grewinchskému času, který je navíc neměnný (nerozlišuje letní a zimní). Formát datumu i času si můžete upravit na evropské zvyklosti pomocí SET date TO 'US', nebo SET date TO 'European', anebo SET date TO 'NonEuropean' s variantou, jak chcete, například 'year before month'.
Data v tabulce můžeme ovlivnit zadáním modifikátorů k té které konkrétní položce. Pomocí NOT NULL
můžeme vynutit zadání hodnoty, to znamená, že když se pokusíme při ukládání dat do tabulky nevložit tuto proměnnou, uložení neproběhne, ale naopak nám server ohlásí chybu, dalším důležitým modifikátorem je DEFAULT
, kterým předdefinováváme hodnotu, která bude použita při nezadání proměnná. Tyto dvě nastavení se překrývají a přesto je ve většině definic vidět něco ve stylu visible BOOLEAN NOT NULL DEFAULT 'FALSE'
. Lidé znající MySQL budou muset oželet modifikátor UNSIGNED
, ale nepovažuji to za nevýhodu, protože pomocí typu NUMERIC
jsme schopni vykrýt takřka jakékoliv hodnoty.
Od této kapitoly dále budu pracovat nad reálným příkladem půjčovny DVD/videokazet, pro kterou v současnosti připravuji aplikaci, která pojede přes WWW rozhraní s možností rezervace DVD/VC od zákazníků přes web, protože zde se ukáží i další věci, jako jsou triggery a indexace.
Tato kapitolka ukáže, jak vytvoříte vlastní tabulky v již vytvožené databázi. Bude to jeden jednoduchý příklad, které napoví vše. Možná i proto bude tato kapitolka extrémě krátká, protože nejlepší je, když si co nejvíce odzkoušíte s.a.m.i.
Pro vytvoření tabulky musíte být buď v terminálu PostgeSQL s Vaší databází, kam se dostanete pomocí psql dbname
, mém konkrétním případě pomocí psql pujcovna
. Pokud jste zapoměli, jaké máte vytvořeny databáze na Vašem serveru, použijte psql -l a server Vám je sám prozradí.
Tabulku vytvoříme příkazem CREATE TABLE jménoTabulky (proměná typ modifikátory [, proměná typ modifikátory]);
, takže například tabulku návštěvníků uděláme pomocí:
CREATE TABLE users( id bigserial NOT NULL, login char(50), firstname char(125), surename char(125), pwd char(32) );Jak prosté milý Watsone dalo by se říci.
Stejným způsobem můžete definovat tabulky další, další a tak dále, až se utabulkujete. Zvolte si jednotný styl pojmenovávání tabulek a proměnných v nich. Osvědčilo se mi třeba prefixovat tabulky patřící k jedné aplikace, protože třeba na webu můžete mít vícero "aplikací", ale ten u koho máte hosting Vám poskytne jen jednu databázi.
Zkuste si sami teď vytvořit tabulku, kde budete mít data pro jednotlivé DVD, videokazety a výpůjčky. Odzkoušíte si tak návrh databázové struktury i jednotlivých.
Dneska jsme se koukli na to jaké jsou základní typy proměnných PostgreSQL, do příštího týdne mi tu zbyly nějaké specifičtější typy, jako pole, geometrické typy, ... , podíváme se podrobněji na práci s datumem/časem a binátními řetězci. Pokusím se tam vejít i vkládání dat na server.