ARCHIV

PostgreSQL (4) - Datové typy, vytvoření tabulek

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ý | Články autora | přečteno 35511×

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é

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.

Celočíselné typy (integer)

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.

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.

Reaálné typy

... 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...

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é binární typy

Ř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ů.

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

Logické hodnoty

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.

Bitové řetězce

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.

Datum a čas

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.

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'.

Modifikátory

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.

Vytvoření tabulky

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.

Závěrem

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.

Verze pro tisk

Související články

Předchozí Celou kategorii (seriál) Další

PostgreSQL (1) - Historie a pohledy jinam
PostgreSQL (2) - Proč PgSQL, data a relace
PostgreSQL (3) - Instalace, základní administrace
PostgreSQL (5) - Další datové typy a práce s časem i binarními řetězci
PostgreSQL (6) - Uložení, aktualizace a mazání dat.
PostgreSQL (7) - Výběr dat z databáze
PostgreSQL (8) - SELECT II.
PostgreSQL (9) – SELECT III
PostgreSQL (10) - SELECT IV
PostgreSQL (11) - Výběr pomocí vzorků
PostgreSQL 12 - urychlení výběrů
PostgreSQL (13) - Na co se zapomnělo
PostgreSQL (14) - omezení dat (Constraints)
PostgreSQL (15) - Transakce
PostgreSQL (16) - Zamykání
PostgreSQL (17) - Datový typ pole
PostgreSQL (18) - Datový typ pole II
PostgreSQL (19) - Vlastní datové typy
PostgreSQL (20) - Vlastní datové typy II
PostgreSQL (21) - Spojování dotazů
PostgreSQL (22) - Poddotazy
PostgreSQL (23) - Optimalizujeme rychlost
PostgreSQL (24) - Views (Pohledy)
PostgreSQL (25) - Administrace skupin a uživatelů
PostgreSQL (26) - Rozšiřujeme funkčnost

Předchozí Celou kategorii (seriál) Další

DISKUZE Chybky 23.11.2004 21:07 MaReK Olšavský Re: Chybky 24.11.2004 14:40 Jan Houštěk Schema 24.11.2004 16:08 Karel Benák Re: Schema 27.11.2004 08:51 MaReK Olšavský Prosim o radu INFORMATION SCHEMA (metadata) 20.9.2006 23:28 Tomk   Re: Prosim o radu INFORMATION SCHEMA (metadata) 20.9.2006 23:31 Aleš Hakl     Re: Prosim o radu INFORMATION SCHEMA (metadata) 24.9.2006 16:27 Tomk       Re: Prosim o radu INFORMATION SCHEMA (metadata) 24.9.2006 16:51 Tomk Zobrazit označené Zobrazit vše Příspívat do diskuze mohou pouze registrovaní uživatelé.