PostgreSQL – datové typy

V této kapitole se budeme zabývat datovými typy používanými v PostgreSQL. Při vytváření tabulky se pro každý sloupec zadává datový typ, tj, jaký druh dat chcete v polích tabulky uložit.

To umožňuje několik výhod –

• Konzistence – Operace proti sloupcům stejného datového typu poskytují konzistentní výsledky a jsou obvykle nejrychlejší.

• Validace – Správné použití datových typů předpokládá validaci formátu dat a odmítnutí dat mimo rozsah datového typu.

• Kompaktnost – Protože sloupec může uchovávat jediný typ hodnoty, je uložen kompaktním způsobem.

• Výkon – Správné použití datových typů poskytuje nejefektivnější uložení dat. Uložené hodnoty lze rychle zpracovat, což zvyšuje výkon.

PostgreSQL podporuje širokou sadu datových typů. Kromě toho si uživatelé mohou vytvořit vlastní datový typ pomocí příkazu CREATE TYPE SQL. V PostgreSQL existují různé kategorie datových typů. Jsou popsány níže.

Číslicové typy

Číslicové typy se skládají z dvoubajtových, čtyřbajtových a osmibajtových celých čísel, čtyřbajtových a osmibajtových čísel s pohyblivou řádovou čárkou a desetinných čísel s volitelnou přesností. Následující tabulka uvádí dostupné typy.

Název	Velikost paměti	Popis	Rozsah
smallint	2 bajty	small-range integer	-32768 to +32767
integer	4 bytes	typická volba pro integer	-2147483648 až +2147483647
velké číslo	8 bajtů	velký rozsah celého čísla	-9223372036854775808 až 9223372036854775807
decimální	proměnná	přesnost určená uživatelem,přesná	až 131072 číslic před desetinnou čárkou; až 16383 číslic za desetinnou čárkou
číselný	proměnný	přesnost specifikovaná uživatelem,přesný	až 131072 číslic před desetinnou čárkou; až 16383 číslic za desetinnou čárkou
skutečná	4 bajty	přesnost proměnná,nepřesná	6 desetinných číslic přesnost
dvojitá přesnost	8 bajtů	přesnost proměnná,nepřesný	přesnost na 15 desetinných míst
malosériový	2 bajty	malý autokrementační integer	1 až 32767
serial	4 bytes	autoincrementing integer	1 do 2147483647
bigserial	8 bajtů	velké autoinkrementující celé číslo	1 do 9223372036854775807

Peněžní typy

Peněžní typ ukládá částku v měně s pevnou přesností na zlomek. Hodnoty datových typů numeric, int a bigint lze převést na peníze. Používání čísel s pohyblivou řádovou čárkou se pro práci s penězi nedoporučuje kvůli možnosti vzniku chyb při zaokrouhlování.

Název	Velikost úložiště	Popis	Rozsah
peníze	8 bajtů	měnová částka	-92233720368547758.08 až +92233720368547758.07

Znakové typy

V níže uvedené tabulce jsou uvedeny univerzální znakové typy dostupné v PostgreSQL.

S. Č.	Název & Popis
1	znak se liší(n), varchar(n) proměnná délka s omezením
2	znak(n), char(n) pevná délka, prázdný vyplněný
3	text proměnná neomezená délka

Binární datové typy

Datový typ bytea umožňuje ukládat binární řetězce podle níže uvedené tabulky.

Název	Velikost úložiště	Popis
bytea	1 nebo 4 bajty plus vlastní binární řetězec	proměnná-.délka binárního řetězce

Typy data/času

PostgreSQL podporuje celou sadu typů data a času SQL, jak je uvedeno v následující tabulce. Data jsou počítána podle gregoriánského kalendáře. Zde mají všechny typy rozlišení 1 mikrosekunda / 14 číslic s výjimkou typu datum, jehož rozlišení je den.

Název	Velikost úložiště	Popis	Nízká hodnota	Vysoká hodnota
časové razítko	8 bajty	jak datum, tak čas (bez časového pásma)	4713 BC	294276 AD
TIMESTAMPTZ	8 bajtů	jak datum, tak čas, s časovým pásmem	4713 př. n. l.	294276 n. l.
datum	4 bajty	datum (bez denní doby)	4713 př. n. l.	5874897 n. l.
čas	8 bajtů	čas dne (bez data)	00:00:00	24:00:00
čas s časovým pásmem	12 bajtů	pouze denní čas s časovým pásmem	00:00:00+1459	24:00:00-1459
interval	12 bajtů	časový interval	-178000000 let	178000000 let

Boolean Typ

PostgreSQL poskytuje standardní SQL typ Boolean. Datový typ Boolean může mít stavy true, false a třetí stav, neznámý, který je reprezentován hodnotou SQL null.

.

Název	Velikost úložiště	Popis
boolean	1 byte	stav true nebo false

Výčtový typ

Výčtové (enum) typy jsou datové typy, které obsahují statické, uspořádanou sadu hodnot. Jsou ekvivalentem výčtových typů podporovaných v řadě programovacích jazyků.

Na rozdíl od ostatních typů je třeba výčtové typy vytvořit pomocí příkazu CREATE TYPE. Tento typ slouží k uložení statické, uspořádané množiny hodnot. Například směry kompasu, tj. sever, jih, východ a západ, nebo dny v týdnu, jak je znázorněno níže –

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Enumerated lze po vytvoření používat stejně jako ostatní typy.

Geometrický typ

Geometrické datové typy představují dvourozměrné prostorové objekty. Nejzákladnější typ, bod, tvoří základ pro všechny ostatní typy.

Název	Velikost úložiště	Prezentace	Popis
bod	16 bajtů	Bod v rovině	(x,y)
linie	32 bajtů	Konečná linie (není plně implementována)	((x1,y1),(x2,y2))
lseg	32 bajtů	Konečná úsečka	((x1,y1),(x2,y2))
box	32 bajtů	Obdélníkový box	((x1,y1),(x2,y2))
cesta	16+16n bajtů	Zavřená cesta (podobně jako polygon)	((x1,y1),…)
cesta	16+16n bajtů	Otevřená cesta
polygon	40+16n	Polygon (podobný uzavřené cestě)	((x1,y1),….)
kruh	24 bajtů	Kruh	<(x,y),r> (středový bod a poloměr)

Typ síťové adresy

PostgreSQL nabízí datové typy pro ukládání IPv4, IPv6 a MAC adres. Pro ukládání síťových adres je lepší používat tyto typy místo prostých textových typů, protože tyto typy nabízejí kontrolu vstupních chyb a specializované operátory a funkce.

Název	Velikost úložiště	Popis
cidr	7 nebo 19 bajtů	Sítě IPv4 a IPv6
inet	7 nebo 19. bajtů	Hostitelé a sítě IPv4 a IPv6
macaddr	6 bajtů	MAC adresy

Typ bitového řetězce

Typy bitových řetězců se používají k ukládání bitových masek. Jejich hodnota je buď 0, nebo 1. Existují dva bitové typy SQL: bit(n) a bit varying(n), kde n je celé kladné číslo.

Typ textového vyhledávání

Tento typ podporuje fulltextové vyhledávání, což je činnost spočívající v prohledávání kolekce dokumentů v přirozeném jazyce s cílem najít ty, které nejlépe odpovídají dotazu. Existují pro něj dva datové typy –

S. No.	Název & Popis
1	tsvector Jedná se o setříděný seznam různých slov, která byla normalizována tak, aby sloučila různé varianty téhož slova, nazývané jako „lexémy“.
2	tsquery Ukládá lexémy, které mají být vyhledány, a spojuje je s respektováním booleovských operátorů & (AND), | (OR) a ! (NOT). K vynucení seskupení operátorů lze použít závorky.

Typ UUID

UUID (Universally Unique Identifiers) se zapisuje jako posloupnost malých hexadecimálních číslic v několika skupinách oddělených pomlčkami, konkrétně skupina osmi číslic, po níž následují tři skupiny čtyř číslic, po nichž následuje skupina 12 číslic, celkem 32 číslic představujících 128 bitů.

Příklad UUID je – 550e8400-e29b-41d4-a716-446655440000

Typ XML

Datový typ XML lze použít k ukládání dat XML. Pro uložení dat XML je třeba nejprve vytvořit hodnoty XML pomocí funkce xmlparse následujícím způsobem –

XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title> <topics>...</topics></tutorial>')XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

JSON Type

Datový typ json lze použít pro uložení dat JSON (JavaScript Object Notation). Taková data lze uložit také jako text, ale datový typ json má tu výhodu, že u každé uložené hodnoty se kontroluje, zda se jedná o platnou hodnotu JSON. K dispozici jsou také související podpůrné funkce, které lze použít přímo ke zpracování datového typu JSON, a to následovně.

Příklad	Příklad Výsledek
array_to_json(‚{{1,5},{99,100}}‘:	,]
row_to_json(row(1,’foo‘))	{„f1“:1, „f2“: „foo“}

Typ pole

PostgreSQL dává možnost definovat sloupec tabulky jako vícerozměrné pole proměnné délky. Lze vytvořit pole libovolného vestavěného nebo uživatelsky definovaného základního typu, výčtového typu nebo složeného typu.

Deklarace polí

Typ pole lze deklarovat jako

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer, scheme text);

nebo pomocí klíčového slova „ARRAY“ jako

CREATE TABLE monthly_savings ( name text, saving_per_quarter integer ARRAY, scheme text);

Vkládání hodnot

Hodnoty pole lze vložit jako doslovnou konstantu, přičemž hodnoty prvků uzavřete do složených závorek a oddělíte je čárkami. Příklad je uveden níže –

INSERT INTO monthly_savings VALUES ('Manisha', '{20000, 14600, 23500, 13250}', '{{"FD", "MF"}, {"FD", "Property"}}'); 

Přístup k polím

Příklad pro přístup k polím je uveden níže. Níže uvedený příkaz vybere osoby, jejichž úspory jsou ve druhém čtvrtletí vyšší než ve čtvrtém čtvrtletí.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Modifikace polí

Příklad modifikace polí je uveden níže.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'WHERE name = 'Manisha';

nebo pomocí syntaxe výrazu ARRAY –

UPDATE monthly_savings SET saving_per_quarter = ARRAYWHERE name = 'Manisha';

Vyhledávání polí

Příklad vyhledávání v polích je podle následujícího obrázku.

SELECT * FROM monthly_savings WHERE saving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000;

Je-li známa velikost pole, lze použít výše uvedený způsob vyhledávání. V opačném případě následující příklad ukazuje, jak vyhledávat, když velikost není známa.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Složené typy

Tento typ představuje seznam názvů polí a jejich datových typů, tj, strukturu řádku nebo záznamu tabulky.

Deklarace složených typů

Následující příklad ukazuje, jak deklarovat složený typ

CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric);

Tento datový typ lze použít při vytváření tabulek následujícím způsobem –

CREATE TABLE on_hand ( item inventory_item, count integer);

Vložení složené hodnoty

Složené hodnoty lze vložit jako doslovnou konstantu, přičemž hodnoty polí se uzavřou do závorek a oddělí čárkami. Příklad je uveden níže –

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Toto platí pro výše definovanou položku inventory_item. Klíčové slovo ROW je ve skutečnosti nepovinné, pokud máte ve výrazu více než jedno pole.

Přístup ke složeným typům

Pro přístup k poli složeného sloupce použijte tečku následovanou názvem pole, podobně jako při výběru pole z názvu tabulky. Například pro výběr některých podpolí z naší příkladové tabulky on_hand by dotaz vypadal následovně –

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Můžete dokonce použít i název tabulky (například ve vícenásobném dotazu), například takto –

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Rozsahové typy

Rozsahové typy představují datové typy, které používají rozsah dat. Typy rozsahů mohou být diskrétní rozsahy (např. všechny celočíselné hodnoty 1 až 10) nebo spojité rozsahy (např, jakýkoli časový bod mezi 10:00 a 11:00).

Vestavěný-zabudované typy rozsahů, které jsou k dispozici, zahrnují následující rozsahy –

• int4range – rozsah celých čísel

• int8range – rozsah bigint

• numrange – rozsah číselných

• tsrange – Rozsah časového razítka bez časové zóny

• tstzrange – Rozsah časového razítka s časovou zónou

• daterange – Rozsah data

Lze vytvořit vlastní typy rozsahů a zpřístupnit tak nové typy rozsahů, například rozsahy IP adres, které používají jako základ typ inet, nebo rozsahy float, které používají jako základ datový typ float.

Typy rozsahů podporují inkluzivní a exkluzivní hranice rozsahů pomocí znaků a ( ). Například ‚[4,9)‘ představuje všechna celá čísla počínaje číslem 4 včetně až po číslo 9.

Typy identifikátorů objektů

Identifikátory objektů (OID) používá PostgreSQL interně jako primární klíče pro různé systémové tabulky. Pokud je zadáno WITH OIDS nebo je povolena konfigurační proměnná default_with_oids, teprve pak se v takových případech OID přidávají do uživatelem vytvořených tabulek. Následující tabulka uvádí několik typů aliasů. Typy aliasů OID nemají žádné vlastní operace s výjimkou specializovaných vstupních a výstupních rutin.

Název		Popis	Hodnota Příklad
oidní	jakýkoli	číselný identifikátor objektu	564182
regproc	pg_proc	název funkce	součet
regprocedura	pg_proc	funkce s typy argumentů	sum(int4)
regoper	pg_operator	název operátoru	+
regoperátor	pg_operátor	operátor s typy argumentů	*(integer,integer) nebo -(NONE,integer)
regclass	pg_class	relation name	pg_type
regtype	pg_type	název datového typu	integer
regconfig	pg_ts_config	konfigurace textového vyhledávání	anglický jazyk
regdictionary	pg_ts_dict	slovník textového vyhledávání	jednoduchý

Pseudotypy

Typový systém PostgreSQL obsahuje řadu speciál-účelových položek, které se souhrnně nazývají pseudotypy. Pseudotyp nelze použít jako datový typ sloupce, ale lze jej použít k deklaraci typu argumentu nebo výsledku funkce.

V níže uvedené tabulce je uveden seznam existujících pseudotypů.

S. Č.	Název & Popis
1	any Určuje, že funkce přijímá libovolný vstupní datový typ.
2	anyelement Určuje, že funkce akceptuje libovolný datový typ.
3	anyarray Určuje, že funkce akceptuje libovolný datový typ pole.
4	anynonarray Určuje, že funkce akceptuje libovolný datový typ, který není pole.
5	anyenum Určuje, že funkce akceptuje libovolný datový typ enum.
6	anyrange Označuje, že funkce přijímá libovolný datový typ range.
7	cstring Označuje, že funkce přijímá nebo vrací řetězec C ukončený nulou.
8	interní Určuje, že funkce přijímá nebo vrací datový typ interního serveru.
9	language_handler Procedurální obsluha volání jazyka je deklarována tak, že vrací language_handler.
10	fdw_handler Obsluha cizích datových obalů je deklarována tak, že vrací fdw_handler.
11	record Identifikuje funkci vracející neurčený typ řádku.
12	trigger Funkce trigger je deklarována tak, že vrací trigger.
13	void Označuje, že funkce nevrací žádnou hodnotu.

.