Andmebaasid (sessioonõpe) 2023/24 kevad

Andmetüübid

Vaatame näidet, kus meil on tarvis hoida keldris mahla ja kartuleid. On selge, et mahla hoiame pudelites või purgis ja kartuleid korvis või kastides. Näite varal võime ütelda, et hoiustamise meetod oleneb, mida soovitakse talletada.

Andmetüübid on olulised ka programmeerimiskeelte puhul. Arvuti hoiab mälus andmeid kahendsüsteemis ehk ühtede ja nullide jadana. Näiteks "hello" kahendsüsteemis on 0110100001100101011011000110110001101111.
Teadmata, mis andmetüüpi on mälus olevad ühed ja nullid, võime ülaltoodud jada tõlkida hoopis kümnendsüsteemis olevaks arvuks 448378203247.
Eeltoodud segaduse lahendab vastava andmetüübi sidumine salvestatud andmetega.

Meil on kogutud andmed grupi inimeste kohta, mis sisaldab inimese ees- ja perekonnanime, inimese pikkust ja kaalu.
Inimese nimi koosneb tähtedest, aga pikkus ja kaal numbritest. Andmebaasi salvestades on tarvis vahet teha, mis tüüpi andmeid hoitakse.

Andmetüüpe on erinevad, aga väga üldiselt jagunevad andmetüübid tekstilisteks, kuhu saaks panna ülal näites toodud inimeste ees- ja perekonnanimed, arvulised, kuhu saaks panna ülal toodud näites inimeste pikkused ja kaalud ning ajalised, milles saaks näiteks salvestada sünniaega.

Andmetüübid võivad erinevatel andmebaaside juhtimissüsteemidel - ABJS (ingl Database Management System DBMS) erineda, seetõttu tuleb enne uue andmebaasisüsteemiga tööle hakates tutvuda kindlasti antud andmebaasisüsteemi dokumentatsiooniga.

Tekstilised andmetüübid

Tekstilist andmetüüpi kasutame kui on tarvis talletada teksti. Tekst võib koosneda ühest tähest (sümbolist) või tervest raamatust (palju sümboleid).

Andmetüübi valimisel teksti talletamiseks tuleb analüüsida, kas teksti pikkus (mida tabelisse salvestada) on teada või teksti pikkus ei ole teada. Näiteks isiku nime puhul võime teha üldistuse, et üle saja sümboli vaja ei ole.
Samas, kui tahame tabelisse salvestada näiteks veebilehelt kogutud vabatekstina sisestatud tagasisidet, siis võib-olla mõistlik mitte piirata tabelis oleva andmetüübi pikkusega salvestatava teksti pikkust.

PostgreSQL pakub tekstiliste andmete hoidmiseks peamiselt kolme andmetüüpi:

1. varchar(n) - tekst kuni n sümbolit.

Kasutatakse, kui oskame prognoosida atribuudi võimalikku maksimaalset pikkust. Näiteks eesnimi, perenimi, asula, pealkiri jms, aga ka siis kui tunnus sisaldab nii arve kui teksti (näiteks autonumber) või kui tunnust ei käsitleta kui arvu (näiteks isikukood, telefoninumber jms). Näiteks kui soovime varchar andmetüübiga defineerida Eestis olevate inimeste eesnimesid, siis võiks eeltööna välja selgitada, kui pikk on kõige pikem Eestis registreeritud eesnimi (Populaarsed eesnimed). Võib eeldada, et 50 sümbolit on piisav.
Seega võib Eestis eesnimede tabelisse salvestamisel defineerida andmetüübi varchar(50).
Määrates selliselt andmetüübi saame salvestada nimi, mis koosneb ühest kuni viiekümnest sümbolist.
Etteruttavalt võib märkida, et andmetüüpi on hiljem võimalik muuta.

1. char(n) - fikseeritud pikkusega tekst n sümbolit.

Sobib olukorras, kus meil on olemit iseloomustamas mõni tekstiga kirjeldav omadus, mille pikkus on fikseeritud. Näiteks olemi riik puhul võib olla üheks omaduseks riigi kood. Eesti puhul on koodiks EE.\\ Antud juhul on kõik koodid täpselt kahe sümboli pikkused ja sellise näite puhul oleks õigeks andmetüübiks char(2).
varchar(n) ja char(n) erinevus seisneb eelkõige selles, et varchar(n) täidab mälus kuni n ühikut ruumi. Näiteks varchar(25) väljale salvestades kümnest sümbolist koosneva sõne, hõivatakse mälus 10 ühikut. Char(25) seevastu, isegi kui char(25) väljale salvestada kümnest sümbolist koosnev sõne, siis hõivatakse mälus ikka 25 ühikut.

1. text - pikemad tekstid, mille pikkus pole teada.

Tekst tüüpi andmetüüpi peaks kasutama ainult siis, kui varchar või char andmetüüpe ei ole võimalik kasutada.
Kuna text andmetüüp on mõeldud hoidma sellise pikkusega tekstilist infot, mille pikkust me ei suuda ette prognoosida, siis on andmebaasisüsteemi poolt text andmetüübi kasutamine väga ebaefektiivne, kuna text andmetüüp võtab arvutis palju ressursse (mälu ja salvestuspinda).
Text andmetüüp kasutamine võib tulla kaalumisele näiteks juhul, kui meil on rakendus, mis kogub klientidelt tagasisidet ja me ei ole kuidagi piiranud sisestatava tagasiside pikkust.
Samas, kui me oleme rakenduse tasemel piiranud sisestatavate sümbolite arvu näiteks 1000, siis tasub kasutada text andmetüübi asemel varchar(1000) kuna nüüd teame, et maksimaalne sümbolite arv on 1000 tähemärki.

Arvulised andmetüübid

Arvulisi andmetüüpe kasutatakse arvulisel kujul andmete hoidmiseks.
Levinumad arvulised andmetüübid PostgreSQL andmebaasis on:

1. integer Täisarvude hoidmiseks.

Sõltuvalt sellest, kui suuri arve on vaja salvestada kasutatakse kas int2 (mahutab 2 baiti ehk siis saab salvestada väärusi -32768 kuni +32767, int4 või int8. Integer suuruse valikul tuleb analüüsida, mis on võimalik suurim väärtus, mida tuleb salvestada. Näiteks isiku vanuse salvestamiseks piisab smallint (2 baiti) suurusest. Samas, kui tuleks kogu maailma rahvastiku arvu salvestada, siis on vaja võtta kasutusele bigint suurus.
Osad andmebaasisüsteemid lubavad defineerida integer andmetüübi suurust ainult positiivses skaalas. Sellisel juhul saab näiteks smallint (2 baiti) suuruses andmetüübis salvestada arve 0 kuni 65535. Sellisel juhul lisatakse integer andmetüübi defineerimisel võti UNSIGNED.

1. numeric(kogupikkus, komatagune pikkus) Kümnendmurdude hoidmiseks (täpne andmetüüp).

Kasutatakse enamasti tuhandusega seotud väärtuste hoidmiseks. Soovides salvestada arve, mille täisarvuline osa on neli sümbolit ja komakohtasid on kaks, siis tuleb defineerida andmetüüp NUMERIC(6,2). Defineerides andmetüübi NUMERIC(6,2) on minimaalne arv, mida antud andmetüübiga saab salvestada, -9999,99 ja maksimaalne arv 9999,99. Numeric andmetüüpi võib defineerida ka kujul NUMERIC(4), sellisel juhul on komakohtade arv 0.
Samuti on võimalik formaat numeric, kus ei defineerita ei kogupikkust ega komakohtade arvu. Sellisel juhul saab selles väljas hoida iga arvu, mis jääb numeric andmetüübi lubatud väärtuse piiridesse. Matemaatilised operatsioonid selle andmetüübiga on aeglased.

1. real ja double precision Ratsionaalarvude hoidmiseks.

NB! SQL maailmas kasutatakse pigem andmetüübi nimesid float4 real asemel ja float8 double precision asemel. Võrreldes numeric andmetüübiga võimaldab hoida suuremaid arve ja matemaatilised operatsioonid on kiiremad. Samas on ebatäpsem võrreldes numeric andmetüübiga. Real e float4 tagab vähemalt kuue kohalise täpsuse ja double precision e float8 tagab vähemalt 15 kohalise täpsuse.

1. serial Automaatselt suurenev (autoincrement) positiivne täisarv

Mõeldud eelkõige kunstvõtmete (id) jaoks.

Korduvalt on mainitud numeric, real ja double precision andmetüüpide puhul täpsust.
Vaatame kuidas käitub arvu 1.2345678919 erinevate andmetüüpide korral.

Andmetüüp	Tulemus
real (float4)	1.2345679
double precision (float8)	1.234567891234568
numeric	1.23456789123456789

Kahendväärtused

1. boolean - andmetele, mida saab tõlgendada ainult kahes olekus maht 1 bait.

Näiteks, kui küsime nõustumist tingimustega, siis tähistame nõustub = true ja ei nõustu = false. Samas ei sobi boolean andmetüübiks olukorras, kus on väärtustel konkreetne tähendus. Näiteks sugu mees ja naine on samuti kahendväärtused, kuid neid pole otstarbekas määratleda booleani kaudu, sest see tekitaks päringutes palju segadust - alati peab meeles pidama, mis sugu on false ja mis true.
Üldiselt on nii, et kui saame omadust kirjeldada järgnevate väljenditega, siis on boolean andmetüüp õige valik: õige/vale, jah/ei, sees/väljas, 1/0, must/valge, luban/ei luba jne.

1. bit(n) - bittide jada fikseeritud pikkusega n.

Biti väärtuseks võib olla kas 0 või 1, näiteks bit(3) lubab kirjutada veergu 101. Kõige sagedamini kasutatakse bit(1) ja seda samas tähenduses kui booleani.

Date / Time

Kuupäevade ja kellaaja märkimiseks on eraldi andmetüübid.
Võib küsida, et miks ei võiks kasutada kuupäeva hoidmiseks mõnda teksti hoidmiseks mõeldud andmetüüpi või mõnda numbrite hoidmiseks mõeldud andmetüüpi? Tehniliselt ei piira miski tekstina salvestada kuupäeva kujul "2021-12-29". Samuti võib proovida mahutada kuupäeva ka numbrina. Näiteks integer andmetüübiga väljale kujul 20211229 või loome kolm eraldi integer välja - aasta, kuu ja päev.

Iga andmetüübiga on seotud andmebaasi juhtimisüsteemiga kaasa tulevaid funktsioonid. Näiteks agregeeriv funktsioon avg(), mis arvutab aritmeetilise keskmise on mõeldud arvuliste andmetüüpidega opereerimisel. Funktsioon upper() on mõeldud tekstiliste andmetüüpidega tegelemiseks. Omaette funktsioonid on ka kuupäevade ja ajaga seotud andmetüüpidega. Seega on rangelt soovitatav kuupäevade ja ajaga seotud andmete talletamiseks kasutada date, time, timestamp ja interval andmetüüpe.

Nimetus	Maht	Min	Max	! Näide
date	4 baiti	4713 BC	5874897 AD	1922-02-24
time	4 baiti	00:00:00	24:00:00	23:03 või 23:03:01
timestamp	8 baiti	4713 BC	5874897 AD	1990-02-27 23:03:01
interval[väljad]	16 baiti	-178000000 aastat	178000000 aastat	1 year 2 months 3 days 4 hours 5 minutes 6 seconds

Vaata ka, kuidas tehted ajatüüpi andmetega muudavad andmetüüpi: https://www.postgresql.org/docs/15/functions-datetime.html

Kokkuvõte

Nimetus	Maht	Kirjeldus	Ulatus
smallint	2 baiti	Väikesed täisarvud	-32768 kuni +32767
integer	4 baiti	Keskmised täisarvud	-2147483648 kuni +2147483647
bigint	8 baiti	Suured täisarvud	-9223372036854775808 kuni +9223372036854775807
numeric	muutuv	Kasutaja poolt defineeritav. Täpne	Kuni 131072 numbrit enne komakohta ja kuni 16383 numbrit peale komakohta
real	4 baiti	Muudetava täpsusega. Ebatäpne	Täpsus vähemalt 6 numbrit
double precision	8 baiti	Muudetava täpsusega. Ebatäpne	Täpsus vähemalt 15 numbrit
smallserial	2 baiti	Unikaalne olemi ID	1 kuni 32767
serial	4 baiti	Unikaalne olemi ID	1 kuni 2147483647
bigserial	8 baiti	Unikaalne olemi ID	1 kuni 9223372036854775807
boolean	1 bait	True või False	1 või 0
bit(n)	n biti	jah või ei	kuni n sümbolit 1 või 0
varchar(n)	n-sümbolit	eesnimed	kuni n sümbolit
char(n)	n-sümbolit	koodid	fikseeritud n sümbolit
text	ei ole määratud	pikad tekstid, mille pikkus pole teada	ei ole määratud

Andmetüübid dokumentatsioon PostgreSQL lehel

AVA TEST

Päringulause (algus)

Päringulause (jätk)