PostgreSQL – Tietotyyppi

Tässä luvussa käsitellään PostgreSQL:ssä käytettäviä tietotyyppejä. Taulukkoa luotaessa jokaiselle sarakkeelle määritetään tietotyyppi, esim, millaista tietoa haluat tallentaa taulukon kenttiin.

Tämä mahdollistaa useita etuja –

• Konsistenssi – Saman tietotyypin sarakkeisiin kohdistuvat operaatiot antavat johdonmukaisia tuloksia ja ovat yleensä nopeimpia.

• Validointi – Tietotyyppien asianmukainen käyttö merkitsee tietojen muodon validointia ja tietotyypin ulkopuolelle jäävien tietojen hylkäämistä.

• Kompaktius – Koska sarakkeessa voidaan tallentaa vain yhden tyyppistä arvoa, se tallennetaan kompaktisti.

• Suorituskyky – Tietotyyppien asianmukaisella käytöllä saadaan aikaan tehokkain datan tallennus. Tallennetut arvot voidaan käsitellä nopeasti, mikä parantaa suorituskykyä.

PostgreSQL tukee laajaa joukkoa tietotyyppejä. Lisäksi käyttäjät voivat luoda oman mukautetun tietotyypin CREATE TYPE SQL-komennolla. PostgreSQL:ssä on erilaisia tietotyyppien luokkia. Niitä käsitellään seuraavassa.

Numeeriset tyypit

Numeeriset tyypit koostuvat kahden tavun, neljän tavun ja kahdeksan tavun kokonaisluvuista, neljän tavun ja kahdeksan tavun liukuluvuista sekä valittavissa olevan tarkkuuden desimaaleista. Seuraavassa taulukossa luetellaan käytettävissä olevat tyypit.

Nimi	Tallennuskoko	Kuvaus	Väli
smallint	2 tavua	small-range integer	-32768 to +32767
integer	4 tavua	typical choice for integer	-2147483648 – +2147483647
bigint	8 tavua	suuri kokonaisluku	-9223372036854775808 – 9223372036854775807
desimaaliluku	muuttuja	käyttäjän määrittämä tarkkuus,exact	enintään 131072 numeroa ennen desimaalipistettä; enintään 16383 numeroa desimaalipisteen jälkeen
numeerinen	muuttuja	käyttäjän määrittelemä tarkkuus,tarkka	enintään 131072 numeroa ennen desimaalipistettä; enintään 16383 numeroa desimaalipisteen jälkeen
todellinen	4 tavua	muuttujatarkkuus,epätarkka	6 desimaalin tarkkuus
kaksoistarkkuus	8 tavua	muuttujatarkk,epätarkka	15 desimaalin tarkkuus
smallserial	2 tavua	pieni automaattinen inkrementaatio. kokonaisluku	1-32767
sarjaluku	4 tavua	autoinkrementoiva kokonaisluku	1 to 214748483647
bigserial	8 tavua	large autoincrementing integer	1 to 9223372036854775807

Monetaariset tyypit

Rahatyypillä tallennetaan valuutan rahamäärä kiinteällä tarkkuudella. Numeric-, int- ja bigint-tietotyyppien arvot voidaan heittää rahaksi. Liukulukujen käyttämistä rahan käsittelyyn ei suositella pyöristysvirheiden mahdollisuuden vuoksi.

Nimi	Tallennuskoko	Kuvaus	Väli
raha	8 tavua	valuuttamäärä	-92233720368547758.08 – +92233720368547758.07

Merkkityypit

Oheisessa taulukossa luetellaan PostgreSQL:ssä käytettävissä olevat yleiskäyttöiset merkkityypit.

S. No.	Nimi & Kuvaus
1	character varying(n), varchar(n) variable-length with limit
2	character(n), char(n) fixed-length, blank padded
3	teksti muuttuva rajoittamaton pituus

Binääridatatyypit

Datatatyyppi bytea sallii binääristen merkkijonojen tallentamisen alla olevan taulukon mukaisesti.

Nimi	Tallennuskoko	Kuvaus
bytea	1 tai 4 tavua sekä varsinainen binäärijono	muuttuja-length binary string

Date/Time Types

PostgreSQL tukee kaikkia SQL:n päivämäärä- ja aikatyyppejä, kuten alla olevasta taulukosta käy ilmi. Päivämäärät lasketaan gregoriaanisen kalenterin mukaan. Tässä kaikkien tyyppien resoluutio on 1 mikrosekunti / 14 numeroa lukuun ottamatta päivämäärätyyppiä, jonka resoluutio on päivä.

Nimi	Tallennuskoko	Kuvaus	Matalin arvo	Korkein arvo
Aikaleima	8 tavuja	kumpikin päivämäärä ja kellonaika (ei aikavyöhykettä)	4713 eKr	294276 AD
TIMESTAMPTZ	8 tavua	kumpikin päivämäärä ja kellonaika, aikavyöhykkeellä	4713 eKr	294276 AD
päivämäärä	4 tavua	päivämäärä (ei kellonaikaa)	4713 eKr	5874897 jKr
aika	8 tavua	päiväaika (ei päivämäärää)	00:00:00	24:00:00
aika aikavyöhykkeellä	12 tavua	vain kellonaika, aikavyöhykkeellä	00:00:00+1459	24:00:00-1459
interval	12 tavua	aikaväli	-178000000 vuotta	178000000 vuotta

Boolean-tyyppi

PostgreSQL tarjoaa vakiomuotoisen SQL:n tyypin Boolean. Boolean-tietotyypillä voi olla tilat true (tosi), false (epätosi) ja kolmas tila unknown (tuntematon), jota edustaa SQL:n null-arvo.

Nimi	Tallennuskoko	Kuvaus
boolean	1 tavu	tila true tai false

Luokiteltu tyyppi

Luokitellut (enum) tyypit ovat tietotyyppejä, jotka koostuvat staattisesta, järjestetty joukko arvoja. Ne vastaavat useissa ohjelmointikielissä tuettuja enum-tyyppejä.

Muista tyypeistä poiketen enumeroidut tyypit on luotava CREATE TYPE-komennolla. Tätä tyyppiä käytetään staattisen, järjestetyn arvojoukon tallentamiseen. Esimerkiksi kompassisuunnat eli POHJOIS, ETELÄ, ITÄ ja LÄNSI tai viikonpäivät alla olevan kuvan mukaisesti –

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

Luokiteltuja tyyppejä, kun ne on kerran luotu, voidaan käyttää kuten muitakin tyyppejä.

Geometrinen tyyppi

Geometriset tietotyypit edustavat kaksiulotteisia avaruudellisia kohteita. Perustavin tyyppi, piste, muodostaa perustan kaikille muille tyypeille.

Nimi	Tallennuskoko	Esitys	Kuvaus
piste	16 tavua	Piste tasossa	(x,y)
viiva	32 tavua	Ääretön viiva (ei täysin toteutettu)	((x1,y1),(x2,y2))
lseg	32 tavua	Jatkuva viivasegmentti	((x1,y1),(x2,y2))
laatikko	32 tavua	Suorakulmainen laatikko	((x1,y1),(x2,y2))
polku	16+16n tavua	Suljettu polku (samanlainen kuin monikulmio)	((x1,y1),…)
polku	16+16n tavua	avoin polku
polygon	40+16n	Polygon (samanlainen kuin suljettu polku)	((x1,y1),y1),y4481),y4481),y1),y1),y1),y1),y1),y1)…)
ympyrä	24 tavua	Ympyrä	<(x,y),r> (keskipiste ja säde)

Verkko-osoitetyyppi

PostgreSQL:ssä on tarjolla datatyyppejä, joilla voidaan tallentaa IPv4-, IPv6- ja MAC-osoitteita. On parempi käyttää näitä tyyppejä tavallisten tekstityyppien sijasta verkko-osoitteiden tallentamiseen, koska nämä tyypit tarjoavat syöttövirheiden tarkistuksen sekä erikoistuneita operaattoreita ja toimintoja.

Nimi	Tallennuskoko	Kuvaus
cidr	7 tai 19 tavua	IPv4- ja IPv6-verkot
inet	7 tai 19 tavua. tavua	IPv4- ja IPv6-isännät ja -verkot
macaddr	6 tavua	MAC-osoitteet

Bit-merkkijonotyyppi

Bit-merkkijonotyyppejä (Bit String Type

Bit String Types) käytetään bitti- eli bitti- eli bitti- eli bitti-merkkijonojen (Bit Masks) tallennukseen. Ne ovat joko 0 tai 1. SQL:ssä on kaksi bittityyppiä: bit(n) ja bit varying(n), jossa n on positiivinen kokonaisluku.

Tekstihakutyyppi

Tämä tyyppi tukee kokotekstihakua, joka on toimintaa, jossa luonnollisen kielen asiakirjojen kokoelmasta etsitään ne asiakirjat, jotka vastaavat parhaiten kyselyä. Tätä varten on kaksi tietotyyppiä –

S. No.	Nimi & Kuvaus
1	tsvektori Tämä on lajiteltu luettelo erillisistä sanoista, jotka on normalisoitu siten, että ne on yhdistetty saman sanan eri variantteihin, joita kutsutaan ”lekseemeiksi”.
2	tsquery Tässä tallennetaan etsittävät lekseemit ja yhdistetään ne kunnioittaen Boolen operaattoreita & (AND), | (OR) ja ! (NOT). Suluilla voidaan pakottaa operaattorit ryhmittymään.

UUID-tyyppi

UUID (Universally Unique Identifiers) kirjoitetaan sekvenssinä pienistä heksadesimaalisista numeroista useissa ryhmissä, jotka erotetaan toisistaan yhdysmerkeillä, tarkemmin sanottuna kahdeksan numeron ryhmä, jonka jälkeen seuraa kolme neljän numeron ryhmää, jonka jälkeen seuraa 12 numeron ryhmä, yhteensä 32 numeroa, jotka edustavat 128 bittiä.

Esimerkki UUID-tunnuksesta on – 550e8400-e29b-41d4-a716-446655440000

XML-tyyppi

XML-tietotyyppiä voidaan käyttää XML-tietojen tallentamiseen. XML-tietojen tallentamista varten on ensin luotava XML-arvot funktiolla xmlparse seuraavasti –

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

Json-tietotyyppiä voidaan käyttää JSON-tietojen (JavaScript Object Notation) tallentamiseen. Tällaiset tiedot voidaan tallentaa myös tekstinä, mutta json-tietotyypin etuna on, että jokainen tallennettu arvo tarkistetaan, että se on kelvollinen JSON-arvo. Käytettävissä on myös asiaan liittyviä tukifunktioita, joita voidaan käyttää suoraan JSON-tietotyypin käsittelyyn seuraavasti.

Esimerkki	Esimerkki Tulos
array_to_json(’{{1,5},{99,100}}’::int)	,]
row_to_json(row(1,’foo’))	{”f1”:1, ”f2”: ”foo”}

Array Type

PostgreSQL antaa mahdollisuuden määritellä taulukon sarake muuttuvan pituiseksi moniulotteiseksi arrayksi. Minkä tahansa sisäänrakennetun tai käyttäjän määrittelemän perustyypin, enum-tyypin tai yhdistelmätyypin arrayja voidaan luoda.

Maastojen ilmoittaminen

Maastotyyppi voidaan ilmoittaa muodossa

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

tai käyttämällä avainsanaa ”ARRAY” muodossa

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

Arvojen lisääminen

Maastojen arvot voidaan lisätä kirjaimellisina vakioina siten, että elementtien arvot suljetaan aaltosulkujen sisäpuolelle ja erotetaan toisistaan pilkuilla. Esimerkki on esitetty alla –

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

Arrayjen käyttäminen

Alhaalla on esitetty esimerkki Arrayjen käyttämisestä. Alla oleva komento valitsee henkilöt, joiden säästöt ovat suuremmat toisella vuosineljänneksellä kuin neljännellä vuosineljänneksellä.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Muokkausmatriisien muokkaaminen

Esimerkki matriisien muokkaamisesta on alla esitetty.

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

tai käyttämällä ARRAY-lausekkeen syntaksia –

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

Määritteiden hakeminen

Esimerkki määritteiden hakemisesta on alla esitetyn kaltainen.

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

Jos määritteen koko on tiedossa, voidaan käyttää edellä esitettyä hakutapaa. Muussa tapauksessa seuraavassa esimerkissä näytetään, miten haku tehdään, kun kokoa ei tunneta.

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

Yhdistelmätyypit

Tämä tyyppi edustaa luetteloa kenttien nimistä ja niiden tietotyypeistä, esim, taulukon rivin tai tietueen rakennetta.

Komposiittityyppien ilmoittaminen

Seuraavassa esimerkissä näytetään, miten komposiittityyppi ilmoitetaan

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

Tätä tietotyyppiä voidaan käyttää taulukoiden luomisessa alla esitetyllä tavalla –

CREATE TABLE on_hand ( item inventory_item, count integer);

Komposiittiarvojen syöttö

Komposiittiarvot voidaan syöttää kirjaimellisina vakioina, jolloin kentän arvot suljetaan sulkujen sisälle ja erotetaan toisistaan pilkulla. Alla on esimerkki –

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

Tämä pätee edellä määritellylle inventory_itemille. ROW-avainsana on itse asiassa valinnainen, kunhan lausekkeessa on useampi kuin yksi kenttä.

Käyttö yhdistetyille tyypeille

Käyttääksesi yhdistetyn sarakkeen kenttää käytä pilkkua, jota seuraa kentän nimi, aivan kuten valitsemalla kenttä taulukon nimestä. Jos haluat esimerkiksi valita joitakin alakenttiä on_hand-esimerkkitaulustamme, kysely olisi seuraavanlainen –

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

Voit käyttää myös taulukon nimeä (esimerkiksi multitable-kyselyssä), esimerkiksi näin –

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

Välialue-tyypit

Välialue-tyypit edustavat tietotyyppejä, jotka hyödyntävät data-aluetta. Range-tyyppi voi olla diskreetti alue (esim. kaikki kokonaislukuarvot 1-10) tai jatkuva alue (esim, mikä tahansa ajankohta kello 10:00 ja 11:00 välillä).

Luotu…in range -tyyppejä ovat muun muassa seuraavat alueet –

• int4range – Kokonaislukualue

• int8range – Bigint-alue

• numrange – Numeerinen alue

• tsrange –

• tsrange – Aikaleiman alue ilman aikavyöhykettä

• tstzrange – Aikaleiman alue aikavyöhykkeen kanssa

• daterange – Päivämäärän alue

Muokattuja vaihteluvälialuetyyppejä voidaan luoda uusien vaihteluvälialuetyyppien käyttöön, kuten IP-osoitealueet, joiden pohjana käytetään inet-tyyppiä, tai float-alueet, joiden pohjana käytetään float-tietotyyppiä.

Aluetyypit tukevat inklusiivisia ja eksklusiivisia aluerajoja käyttämällä vastaavasti merkkejä ja ( ). Esimerkiksi ’[4,9)’ edustaa kaikkia kokonaislukuja, jotka alkavat numerosta 4 ja päättyvät numeroon 9, mutta eivät sisällä sitä.

Objektin tunnistetyypit

Objektin tunnistetyyppejä (OID) käytetään PostgreSQL:ssä sisäisesti eri järjestelmätaulujen ensisijaisina avaimina. Jos WITH OIDS on määritetty tai default_with_oids -määritysmuuttuja on käytössä, vain silloin OID:t lisätään tällaisissa tapauksissa käyttäjän luomiin taulukoihin. Seuraavassa taulukossa on lueteltu useita alias-tyyppejä. OID-alias-tyypeillä ei ole omia operaatioita lukuun ottamatta erikoistuneita tulo- ja lähtörutiineja.

Nimi		Kuvaus	Arvo Esimerkki
oid	any	numeerinen objektin tunniste	564182
regproc	pg_proc	funktion nimi	sum
regprocedure	pg_proc	funktio argumenttityypeillä	sum(int4)
regoperator	pg_operator	operaattorin nimi	+
regoperator	pg_operator	operaattori argumenttityypeillä	*(integer,integer) tai -(NONE,kokonaisluku)
regclass	pg_class	relation name	pg_type
regtype	pg_type	tietotyypin nimi	integer
regconfig	pg_ts_config	tekstihakukonfiguraatio	englanti
regdictionary	pg_ts_dict	tekstihakusanakirja	simple

Pseudotyypit

PostgreSQL:n tyyppijärjestelmä sisältää joukon erikoisluonteisia-tarkoitukseen tarkoitettuja merkintöjä, joita kutsutaan yhteisesti pseudotyypeiksi. Pseudotyyppiä ei voi käyttää sarakkeen tietotyyppinä, mutta sitä voidaan käyttää funktion argumentti- tai tulostyypin ilmoittamiseen.

Alla olevassa taulukossa on lueteltu olemassa olevat pseudotyypit.

S. Nro.	Nimi & Kuvaus
1	any Merkitsee, että funktio hyväksyy minkä tahansa syötetietotyypin.
2	anyelement Merkitsee, että funktio hyväksyy minkä tahansa tietotyypin.
3	anyarray Merkitsee, että funktio hyväksyy minkä tahansa array-tietotyypin.
4	anynonarray Merkitsee, että funktio hyväksyy minkä tahansa muun kuin array-tietotyypin.
5	anyenum Merkitsee, että funktio hyväksyy minkä tahansa enum-tietotyypin.
6	anyrange Merkitsee, että funktio hyväksyy minkä tahansa range-tietotyypin.
7	cstring Merkitsee, että funktio hyväksyy nollaterminoidun C-merkkijonon tai palauttaa sen.
8	sisäinen Merkitsee, että funktio hyväksyy tai palauttaa palvelimen sisäisen tietotyypin.
9	language_handler Proseduraalisen kielen kutsun käsittelijä ilmoitetaan palauttamaan language_handler.
10	fdw_handler Vieraan datan käärekäsittelijä julistetaan palauttamaan fdw_handler.
11	record Tunnistetaan funktio, joka palauttaa määrittelemättömän rivityypin.
12	trigger Liipaisutoiminto julistetaan palauttamaan trigger.
13	void Merkitsee, että funktio ei palauta mitään arvoa.