PostgreSQL – Datatyper

I dette kapitel vil vi diskutere de datatyper, der anvendes i PostgreSQL. Når du opretter en tabel, skal du for hver kolonne angive en datatype, dvs, hvilken type data du ønsker at gemme i tabellens felter.

Dette giver flere fordele –

• Konsistens – Operationer mod kolonner med samme datatype giver konsistente resultater og er normalt de hurtigste.

• Validering – Korrekt brug af datatyper indebærer formatvalidering af data og afvisning af data uden for datatypens anvendelsesområde.

• Kompakthed – Da en kolonne kan lagre en enkelt værditype, lagres den på en kompakt måde.

• Performance – Korrekt brug af datatyper giver den mest effektive lagring af data. De lagrede værdier kan behandles hurtigt, hvilket forbedrer ydeevnen.

PostgreSQL understøtter et bredt sæt af datatyper. Desuden kan brugerne oprette deres egen brugerdefinerede datatype ved hjælp af kommandoen CREATE TYPE SQL. Der er forskellige kategorier af datatyper i PostgreSQL. De gennemgås nedenfor.

Numeriske typer

Numeriske typer består af to-byte-, fire-byte- og otte-byte-integriteter, fire-byte- og otte-byte-flydepunktstal og decimaltal med valgbar præcision. Følgende tabel indeholder en liste over de tilgængelige typer.

Navn	Lagringsstørrelse	Beskrivelse	Rækkevidde
smallint	2 bytes	small-range integer	-32768 til +32767
integer	4 bytes	typisk valg for integer	-2147483648 til +2147483647
bigint	8 bytes	stort heltal	-9223372036854775808 til 922337203685477575807
decimal	variabel	brugerspecificeret præcision,exact	op til 131072 cifre før decimalkommaet; op til 16383 cifre efter decimalpunktet
numerisk	variabel	brugerspecificeret præcision, nøjagtig	op til 131072 cifre før decimalpunktet; op til 16383 cifre efter decimalpunktet
real	4 bytes	variabel præcision,upræcis	6 decimale cifre præcision
dobbelt præcision	8 bytes	variabel præcision,upræcis	15 decimale cifre præcision
smallserial	2 bytes	small autoincrementing heltal	1 til 32767
seriel	4 bytes	autoinkremerende heltal	1 til 2147483647
bigserial	8 bytes	stort autoinkremerende heltal	1 til 922337203685854775807

Monetære typer

Penningtypen lagrer et valutabeløb med en fast brøkpræcision. Værdier af datatyperne numeric, int og bigint kan kastes til money. Det anbefales ikke at bruge Frivolpunkttal til at håndtere penge på grund af muligheden for afrundingsfejl.

Navn	Lagringsstørrelse	Beskrivelse	Rækkevidde
penge	8 bytes	8 bytes	valutabeløb	-92233720368547758.08 til +9223372036858547758.07

Tegentyper

Den nedenstående tabel indeholder en liste over de generelle tegentyper, der er tilgængelige i PostgreSQL.

S. Nr.	Navn & Beskrivelse
1	karakter varierende(n), varchar(n) variabel-længde med begrænsning
2	tegn(n), char(n) fast-længde, blank padded
3	text variabel ubegrænset længde

Binære datatyper

Datatypen bytea giver mulighed for lagring af binære strenge som i nedenstående tabel.

Navn	Lagringsstørrelse	Beskrivelse
bytea	1 eller 4 bytes plus den egentlige binære streng	variabel-længde binær streng

Dato-/tidstyper

PostgreSQL understøtter et komplet sæt af SQL-dato- og tidstyper, som vist i tabellen nedenfor. Datoer tælles i henhold til den gregorianske kalender. Her har alle typerne en opløsning på 1 mikrosekund / 14 cifre undtagen datatypen, hvis opløsning er dag.

Navn	Storage Size	Beskrivelse	Low Value	High Value
timestamp	8 bytes	både dato og klokkeslæt (ingen tidszone)	4713 BC	294276 AD
TIMESTAMPTZ	8 bytes	både dato og klokkeslæt, med tidszone	4713 BC	294276 AD
dato	4 bytes	dato (uden klokkeslæt)	4713 BC	5874897 AD
time	8 bytes	tidspunkt på dagen (ingen dato)	00:00:00	24:00:00:00
tid med tidszone	12 bytes	når kun dagstider, med tidszone	00:00:00+1459	24:00:00-1459
interval	12 bytes	tidsinterval	-178000000 år	178000000 år

Boolsk type

PostgreSQL indeholder standard SQL-typen Boolean. Den boolske datatype kan have tilstandene sand, falsk og en tredje tilstand, ukendt, som repræsenteres af SQL-værdien null.

Navn	Lagringsstørrelse	Beskrivelse
boolean	1 byte	tilstand af sand eller falsk

Enumereret type

Enumererede (enum) typer er datatyper, der omfatter en statisk, ordnet sæt af værdier. De svarer til de enum-typer, der understøttes i en række programmeringssprog.

I modsætning til andre typer skal enumererede typer oprettes ved hjælp af kommandoen CREATE TYPE. Denne type bruges til at gemme et statisk, ordnet sæt af værdier. F.eks. kompasretninger, dvs. NORD, SYD, ØST og VEST eller ugedage som vist nedenfor –

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

Enumerated kan, når de først er oprettet, bruges som alle andre typer.

Geometrisk type

Geometriske datatyper repræsenterer todimensionale rumlige objekter. Den mest grundlæggende type, punktet, danner grundlaget for alle de andre typer.

Navn	Storage Size	Representation	Beskrivelse
Punkt	16 bytes	Punkt på et plan	(x,y)
linje	32 bytes	Uendelig linje (ikke fuldt implementeret)	((x1,y1),(x2,y2))
lseg	32 bytes	Uendeligt linjesegment	((x1,y1),(x2,y2))
box	32 bytes	Rektangulær kasse	((x1,y1),(x2,y2))
sti	16+16n bytes	Sluttet sti (svarende til polygon)	((x1,y1),…)
sti	16+16n bytes	Åben sti
polygon	40+16n	Polygon (svarende til lukket sti)	((x1,y1),….)
cirkel	24 bytes	Cirkel	<(x,y),r> (midtpunkt og radius)

Netværksadressetype

PostgreSQL tilbyder datatyper til lagring af IPv4-, IPv6- og MAC-adresser. Det er bedre at bruge disse typer i stedet for almindelige teksttyper til at gemme netværksadresser, fordi disse typer tilbyder kontrol af indtastningsfejl og specialiserede operatører og funktioner.

Navn	Lagringsstørrelse	Beskrivelse
cidr	7 eller 19 bytes	IPv4- og IPv6-netværk
inet	7 eller 19 bytes	IPv4- og IPv6-værter og -netværk
macaddr	6 bytes	MAC-adresser

Bit String Type

Bit String Types bruges til at gemme bitmasker. De er enten 0 eller 1. Der findes to SQL-bittyper: bit(n) og bit varierende(n), hvor n er et positivt heltal.

Tekstsøgetype

Denne type understøtter fuldtekstsøgning, som er en aktivitet, der består i at søge i en samling af dokumenter i naturligt sprog for at finde dem, der passer bedst til en forespørgsel. Der er to datatyper til dette –

S. No.	Navn & Beskrivelse
1	tsvector Dette er en sorteret liste over forskellige ord, der er blevet normaliseret for at sammenlægge forskellige varianter af det samme ord, kaldet som “lexemer”.
2	tsquery Dette lagrer lexemer, der skal søges efter, og kombinerer dem med respekt for de boolske operatorer & (AND), | (OR), og ! (NOT). Parenteser kan bruges til at håndhæve gruppering af operatørerne.

UUID Type

Et UUID (Universally Unique Identifiers) skrives som en sekvens af hexadecimale cifre med små bogstaver i flere grupper adskilt af bindestreger, nærmere bestemt en gruppe på otte cifre, efterfulgt af tre grupper på fire cifre, efterfulgt af en gruppe på 12 cifre, i alt 32 cifre, der repræsenterer de 128 bit.

Et eksempel på et UUID er – 550e8400-e29b-41d4-a716-446655440000

XML Type

Den XML-datatatype kan bruges til at lagre XML-data. For at lagre XML-data skal du først oprette XML-værdier ved hjælp af funktionen xmlparse som følger –

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

Den json-datatatype kan bruges til at lagre JSON-data (JavaScript Object Notation). Sådanne data kan også lagres som tekst, men json-datatypen har den fordel, at den kontrollerer, at hver lagret værdi er en gyldig JSON-værdi. Der er også relaterede støttefunktioner til rådighed, som kan bruges direkte til at håndtere JSON-datatypen som følger

Eksempel	Eksempel Resultat
array_to_json(‘{{1,5},{99,100}}}’::int)	,]
row_to_json(row(1,’foo’))	{“f1”:1, “f2”: “foo”}

Array Type

PostgreSQL giver mulighed for at definere en kolonne i en tabel som et multidimensionalt array med variabel længde. Der kan oprettes arrays af enhver indbygget eller brugerdefineret basistype, enumtype eller sammensat type.

Deklaration af arrays

Array-typen kan deklareres som

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

eller ved at bruge nøgleordet “ARRAY” som

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

Insættelse af værdier

Array-værdier kan indsættes som en bogstavelig konstant ved at omslutte elementværdierne inden for parenteser og adskille dem med kommaer. Et eksempel er vist nedenfor –

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

Accessing Arrays

Et eksempel på adgang til Arrays er vist nedenfor. Kommandoen nedenfor vil udvælge de personer, hvis opsparing er større i andet kvartal end i fjerde kvartal.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Modificering af arrays

Et eksempel på ændring af arrays er som vist nedenfor.

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

eller ved hjælp af syntaksen for ARRAY-udtryk –

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

Søgning af arrays

Et eksempel på søgning af arrays er som vist nedenfor.

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

Hvis størrelsen af arrayet er kendt, kan den ovenfor angivne søgemetode anvendes. Ellers viser følgende eksempel, hvordan man søger, når størrelsen ikke er kendt.

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

Komposittyper

Denne type repræsenterer en liste over feltnavne og deres datatyper, dvs, strukturen af en række eller post i en tabel.

Deklaration af sammensatte typer

Det følgende eksempel viser, hvordan man deklarerer en sammensat type

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

Denne datatype kan bruges i oprettelsen af tabeller som nedenfor –

CREATE TABLE on_hand ( item inventory_item, count integer);

Input af sammensatte værdier

Sammensatte værdier kan indsættes som en bogstavelig konstant, idet feltværdierne omsluttes af parenteser og adskilles med kommaer. Et eksempel er vist nedenfor –

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

Dette er gyldigt for inventory_item defineret ovenfor. Nøgleordet ROW er faktisk valgfrit, så længe du har mere end ét felt i udtrykket.

Accessing Composite Types

For at få adgang til et felt i en sammensat kolonne skal du bruge en prik efterfulgt af feltnavnet, ligesom du vælger et felt fra et tabelnavn. Hvis du f.eks. vil vælge nogle underfelter fra vores on_hand-eksempeltabel, ville forespørgslen være som vist nedenfor –

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

Du kan endda også bruge tabelnavnet (f.eks. i en forespørgsel med flere tabeller), som her –

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

Rangtyper

Rangtyper repræsenterer datatyper, der bruger et område af data. Rækketyper kan være diskrete intervaller (f.eks. alle heltalsværdier fra 1 til 10) eller kontinuerlige intervaller (f.eks, ethvert tidspunkt mellem kl. 10.00 og kl. 11.00).

Den opbyggede-indbyggede typer af tilgængelige intervaller omfatter følgende intervaller –

• int4range – Intervallet af heltal

• int8range – Intervallet af bigint

• numrange – Intervallet af numerisk

• tsrange – Range of timestamp without time zone

• tstzrange – Range of timestamp with time zone

• daterange – Range of date

Der kan oprettes brugerdefinerede rangetyper for at gøre nye typer af intervaller tilgængelige, som f.eks. IP-adresseintervaller, der bruger inet-typen som base, eller float-intervaller, der bruger float-datatypen som base.

Rangtyper understøtter inklusive og eksklusive områdegrænser ved hjælp af henholdsvis tegnene og ( ). F.eks. repræsenterer ‘[4,9)’ alle hele tal fra og med 4 til og med 9, men ikke inklusive 9.

Objektidentifikationstyper

Objektidentifikationstyper (OID’er) bruges internt af PostgreSQL som primærnøgler til forskellige systemtabeller. Hvis WITH OIDS er angivet, eller konfigurationsvariablen default_with_oids er aktiveret, er det kun i sådanne tilfælde, at OID’er tilføjes til brugeroprettede tabeller. Den følgende tabel viser flere alias-typer. OID-alias-typerne har ingen egne operationer bortset fra specialiserede input- og outputrutiner.

Navn		Beskrivelse	Værdi Eksempel
oid	nogle	nogle	numerisk objekt-id	564182	564182
regproc	pg_proc	funktionsnavn	sum
regprocedure	pg_proc	funktion med argumenttyper	sum(int4)
regoper	pg_operator	operatornavn	+
regoperator	pg_operator	operator med argumenttyper	*(integer,heltal) eller -(NONE,heltal)
regclass	pg_class	relation name	pg_type
regtype	pg_type	pg_type	datatype navn	integer
regconfig	pg_ts_config	konfiguration til tekstsøgning	English
regdictionary	pg_ts_dict	text search dictionary	simple

Pseudotyper

Det PostgreSQL-typesystem indeholder en række specielle-formålsposter, der kollektivt kaldes pseudo-typer. En pseudotype kan ikke bruges som en kolonnedatatatype, men den kan bruges til at deklarere en funktions argument- eller resultattype.

Den nedenstående tabel indeholder en liste over de eksisterende pseudotyper.

S. Nr.	Navn & Beskrivelse
1	any Angiver, at en funktion accepterer en hvilken som helst inputdatatype.
2	anyelement Angiver, at en funktion accepterer en hvilken som helst datatype.
3	anyarray Angiver, at en funktion accepterer en hvilken som helst array-datatatype.
4	anynonarray Indikerer, at en funktion accepterer enhver datatype, der ikke er et array.
5	anyenum Indikerer, at en funktion accepterer enhver enum-datatatype.
6	anyrange Indikerer, at en funktion accepterer en hvilken som helst datatype af typen range.
7	cstring Indikerer, at en funktion accepterer eller returnerer en C-streng med nulterminering.
8	internal Angiver, at en funktion accepterer eller returnerer en serverintern datatype.
9	language_handler En proceduremæssig sprogkaldshåndtering er erklæret til at returnere language_handler.
10	fdw_handler En håndtering af fremmeddataindpakning er erklæret til at returnere fdw_handler.
11	record Identificerer en funktion, der returnerer en uspecificeret rækketype.
12	trigger En triggerfunktion er erklæret til at returnere trigger.
13	void Angiver, at en funktion ikke returnerer nogen værdi.