PostgreSQL – Datatype

In dit hoofdstuk gaan we het hebben over de datatypes die in PostgreSQL gebruikt worden. Bij het maken van een tabel specificeert u voor elke kolom een gegevenstype, d.w.z,

Dit biedt verschillende voordelen –

• Consistentie – Bewerkingen tegen kolommen van hetzelfde gegevenstype geven consistente resultaten en zijn meestal het snelst.

• Validatie – Een juist gebruik van gegevenstypen impliceert formaatvalidatie van gegevens en verwerping van gegevens die buiten het gegevenstype vallen.

• Compactheid – Aangezien een kolom slechts één type waarde kan opslaan, wordt deze op compacte wijze opgeslagen.

• Prestaties – Een juist gebruik van gegevenstypen leidt tot de meest efficiënte opslag van gegevens. De opgeslagen waarden kunnen snel worden verwerkt, wat de prestaties ten goede komt.

PostgreSQL ondersteunt een brede reeks gegevenstypen. Bovendien kunnen gebruikers hun eigen aangepaste gegevenstype maken met het CREATE TYPE SQL-commando. Er zijn verschillende categorieën van datatypes in PostgreSQL. Ze worden hieronder besproken.

Numerieke Types

Numerieke types bestaan uit twee-byte, vier-byte, en acht-byte gehele getallen, vier-byte en acht-byte floating-point getallen, en selecteerbare-precisie decimalen. In de volgende tabel staan de beschikbare typen.

Name	Storage Size	Description	Range
smallint	2 bytes	small-bereik integer	-32768 tot +32767
integer	4 bytes	typische keuze voor integer	-2147483648 tot +2147483647
bigint	8 bytes	groot bereik integer	-9223372036854775808 tot 9223372036854775807
decimaal	variabel	door gebruiker gespecificeerde precisie,exact	tot 131072 cijfers vóór de decimale punt; tot 16383 cijfers na de komma
numeriek	variabel	door gebruiker opgegeven precisie, exact	tot 131072 cijfers voor de komma; tot 16383 cijfers achter de komma
echt	4 bytes	variabele-precisie,inexact	6 decimale cijfers precisie
dubbele precisie	8 bytes	variabele-precisie,inexact	15 decimale cijfers precisie
smallserial	2 bytes	klein auto-incrementing integer	1 tot 32767
serial	4 bytes	autoincrementing integer	1 tot 2147483647
bigserial	8 bytes	groot auto-incrementing integer	1 tot 9223372036854775807

Monetary Types

Het money type slaat een valuta bedrag op met een vaste fractionele precisie. Waarden van de gegevenstypen numeriek, int, en bigint kunnen naar geld worden geconverteerd. Het gebruik van Floating point getallen wordt niet aanbevolen om met geld om te gaan vanwege de kans op afrondingsfouten.

Name	Storage Size	Description	Range
money	8 bytes	currency amount	-92233720368547758.08 tot +92233720368547758.07

Character Types

De onderstaande tabel geeft een overzicht van de algemene karakter types die beschikbaar zijn in PostgreSQL.

S. No.	Naam & Beschrijving
1	karakter variërend(n), varchar(n) variabele-lengte met limiet
2	karakter(n), char(n) vaste-lengte, blank padded
3	tekst variabele onbeperkte lengte

Binaire Datatypes

Het bytea datatype staat opslag van binaire strings toe zoals in de tabel hieronder.

Naam	Opslaggrootte	Beschrijving
bytea	1 of 4 bytes plus de eigenlijke binaire string	variabele-lengte binaire string

Datum/Tijd Types

PostgreSQL ondersteunt een volledige set van SQL datum en tijd types, zoals weergegeven in onderstaande tabel. Datums worden geteld volgens de Gregoriaanse kalender. Hier hebben alle types een resolutie van 1 microseconde / 14 cijfers, behalve het date type, waarvan de resolutie dag is.

Naam	Grootte	Beschrijving	Lage Waarde	Hoge Waarde
timestamp	8 bytes	zowel datum als tijd (geen tijdzone)	4713 BC	294276 AD
TIMESTAMPTZ	8 bytes	zowel datum als tijd, met tijdzone	4713 BC	294276 AD
date	4 bytes	date (geen tijd van de dag)	4713 BC	5874897 AD
tijd	8 bytes	tijd van de dag (geen datum)	00:00:00	24:00:00
tijd met tijdzone	12 bytes	alleen tijdstippen van de dag, met tijdzone	00:00:00+1459	24:00:00-1459
interval	12 bytes	tijdsinterval	-178000000 jaar	178000000 jaar

Boolean Type

PostgreSQL levert het standaard SQL type Boolean. Het Boolean datatype kan de toestanden true, false, en een derde toestand, unknown, die wordt vertegenwoordigd door de SQL null waarde hebben.

Name	Storage Size	Description
boolean	1 byte	toestand van waar of onwaar

Enumerated Type

Enumerated (enum) types zijn gegevenstypen die bestaan uit een statische, geordende reeks waarden. Zij zijn equivalent aan de enum-typen die in een aantal programmeertalen worden ondersteund.

In tegenstelling tot andere typen moeten Enumerated Types worden gemaakt met het commando CREATE TYPE. Dit type wordt gebruikt om een statische, geordende reeks waarden op te slaan. Bijvoorbeeld kompasrichtingen, d.w.z. NOORDEN, ZUIDEN, OOSTEN en WESTEN of dagen van de week zoals hieronder getoond –

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

Enumerated, eenmaal aangemaakt, kunnen net als alle andere types worden gebruikt.

Geometrisch Type

Geometrische gegevenstypen vertegenwoordigen tweedimensionale ruimtelijke objecten. Het meest fundamentele type, de punt, vormt de basis voor alle andere types.

Name	Storage Size	Representation	Description
point	16 bytes	Point op een vlak	(x,y)
lijn	32 bytes	oneindige lijn (niet volledig geïmplementeerd)	((x1,y1),(x2,y2))
lseg	32 bytes	Eindig lijnstuk	((x1,y1),(x2,y2))
box	32 bytes	Rechthoekige box	((x1,y1),(x2,y2))
pad	16+16n bytes	Gesloten pad (vergelijkbaar met veelhoek)	((x1,y1),…)
pad	16+16n bytes	Open pad
polygoon	40+16n	Polygoon (vergelijkbaar met gesloten pad)	((x1,y1),….)
cirkel	24 bytes	cirkel	<(x,y),r> (middelpunt en straal)

Network Address Type

PostgreSQL biedt datatypes om IPv4-, IPv6-, en MAC-adressen op te slaan. Het is beter om deze types te gebruiken in plaats van platte tekst types om netwerk adressen op te slaan, omdat deze types een invoer fout controle en gespecialiseerde operatoren en functies bieden.

Name	Storage Size	Description
cidr	7 of 19 bytes	IPv4 en IPv6 netwerken
inet	7 of 19 bytes	IPv4 en IPv6 hosts en netwerken
macaddr	6 bytes	MAC-adressen

Bit String Type

Bit String Types worden gebruikt om bitmaskers op te slaan. Ze zijn 0 of 1. Er zijn twee SQL-bit-typen: bit(n) en bit varying(n), waarbij n een positief geheel getal is.

Text Search Type

Dit type ondersteunt het zoeken in de volledige tekst, d.w.z. het doorzoeken van een verzameling natuurlijke-taaldocumenten om de documenten te vinden die het best met een zoekopdracht overeenkomen. Er zijn twee gegevenstypen voor deze –

S. No.	Name & Description
1	tsvector Dit is een gesorteerde lijst van afzonderlijke woorden die zijn genormaliseerd om verschillende varianten van hetzelfde woord, “lexemen” genoemd, samen te voegen.
2	tsquery Dit slaat lexemen op waarnaar moet worden gezocht, en combineert ze met inachtneming van de Booleaanse operatoren & (AND), | (OR), en ! (NOT). Parentheses kunnen worden gebruikt om groepering van de operatoren af te dwingen.

UUID Type

Een UUID (Universally Unique Identifiers) wordt geschreven als een opeenvolging van hexadecimale cijfers in kleine letters, in verschillende groepen gescheiden door koppeltekens, met name een groep van acht cijfers, gevolgd door drie groepen van vier cijfers, gevolgd door een groep van 12 cijfers, voor een totaal van 32 cijfers die de 128 bits vertegenwoordigen.

Een voorbeeld van een UUID is – 550e8400-e29b-41d4-a716-446655440000

XML Type

Het XML-gegevenstype kan worden gebruikt om XML-gegevens op te slaan. Om XML-gegevens op te slaan, moet u eerst XML-waarden maken met de functie xmlparse als volgt –

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

Het gegevenstype json kan worden gebruikt om JSON-gegevens (JavaScript Object Notation) op te slaan. Dergelijke gegevens kunnen ook als tekst worden opgeslagen, maar het datatype json heeft het voordeel dat wordt gecontroleerd of elke opgeslagen waarde een geldige JSON-waarde is. Er zijn ook verwante ondersteuningsfuncties beschikbaar, die rechtstreeks kunnen worden gebruikt om het JSON gegevenstype als volgt te verwerken.

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

Array Type

PostgreSQL geeft de mogelijkheid om een kolom van een tabel te definiëren als een multidimensionale array met variabele lengte. Arrays van elk ingebouwd of door de gebruiker gedefinieerd basistype, enum type, of samengesteld type kunnen worden aangemaakt.

Aangave van arrays

Het type array kan worden gedeclareerd als

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

of met behulp van het trefwoord “ARRAY” als

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

Waarden invoegen

Arraywaarden kunnen als een letterlijke constante worden ingevoegd, waarbij de elementwaarden tussen accolades worden gezet en door komma’s van elkaar worden gescheiden. Een voorbeeld wordt hieronder getoond –

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

Toegang tot Arrays

Een voorbeeld voor toegang tot Arrays wordt hieronder getoond. Het onderstaande commando zal de personen selecteren wiens spaargeld meer is in het tweede kwartaal dan in het vierde.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Wijzigen van Arrays

Een voorbeeld van het wijzigen van arrays wordt hieronder getoond.

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

of met behulp van de ARRAY expressie syntaxis –

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

Zoeken in arrays

Een voorbeeld van het zoeken in arrays is zoals hieronder getoond.

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

Als de grootte van de array bekend is, kan de hierboven gegeven zoekmethode gebruikt worden. Het volgende voorbeeld laat zien hoe u kunt zoeken als de grootte niet bekend is.

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

Composite Types

Dit type staat voor een lijst met veldnamen en hun gegevenstypen, d.w.z, structuur van een rij of record van een tabel.

Aangifte van samengestelde Types

Het volgende voorbeeld toont hoe een samengesteld type moet worden aangegeven

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

Dit gegevenstype kan worden gebruikt in de onderstaande tabellen maken –

CREATE TABLE on_hand ( item inventory_item, count integer);

Invoer van samengestelde waarden

Samengestelde waarden kunnen worden ingevoegd als een letterlijke constante, waarbij de veldwaarden tussen haakjes worden gezet en van elkaar worden gescheiden door komma’s. Hieronder ziet u een voorbeeld –

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

Dit is geldig voor het hierboven gedefinieerde inventory_item. Het sleutelwoord ROW is eigenlijk optioneel, zolang u meer dan één veld in de expressie hebt.

Toegang tot samengestelde typen

Om toegang te krijgen tot een veld van een samengestelde kolom, gebruikt u een punt gevolgd door de veldnaam, net zoals u een veld uit een tabelnaam selecteert. Om bijvoorbeeld enkele subvelden uit onze on_hand voorbeeldtabel te selecteren, zou de query er als volgt uitzien –

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

U kunt zelfs de tabelnaam gebruiken (bijvoorbeeld in een multitable query), zoals deze –

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

Bereiktypes

Bereiktypes vertegenwoordigen gegevenstypen die een bereik van gegevens gebruiken. Bereiktypes kunnen discrete bereiken zijn (bijv. alle gehele getallen van 1 tot 10) of continue bereiken (bijv, elk punt in de tijd tussen 10.00 uur en 11.00 uur).

De ingebouwdein-bereiktypen zijn onder meer de volgende bereiken –

• int4range – bereik van integer

• int8range – bereik van bigint

• numrange – bereik van numeriek

• tsrange – tijdsaanduiding zonder tijdzone. Tijdstempelbereik zonder tijdzone

• tstzrange – Tijdstempelbereik met tijdzone

• daterange – Datumbereik

Er kunnen aangepaste bereiktypen worden gemaakt om nieuwe soorten bereiken beschikbaar te maken, zoals IP-adresbereiken die het inet-type als basis gebruiken, of floatbereiken die het gegevenstype float als basis gebruiken.

Bereiktypen ondersteunen inclusieve en exclusieve bereikgrenzen met respectievelijk de tekens en ( ). Bijvoorbeeld ‘[4,9)’ staat voor alle gehele getallen vanaf en inclusief 4 tot en met maar exclusief 9.

Object Identifier Types

Object identifiers (OIDs) worden intern door PostgreSQL gebruikt als primaire sleutels voor diverse systeemtabellen. Als WITH OIDS is gespecificeerd of default_with_oids configuratie variabele is ingeschakeld, dan worden alleen in die gevallen OIDs toegevoegd aan door de gebruiker gecreëerde tabellen. In de volgende tabel staan verschillende alias types. De OID alias-types hebben geen eigen operaties, behalve gespecialiseerde invoer- en uitvoerroutines.

Naam		Beschrijving	Waarde Voorbeeld
oid	any	numeriek objectidentifier	564182
regproc	pg_proc	functienaam	sum
regprocedure	pg_proc	functie met argumenttypen	sum(int4)
regoper	pg_operator	operatornaam	+
regoperator	pg_operator	operator met argumenttypes	*(integer,geheel getal) of -(NONE,integer)
regclass	pg_class	relation name	pg_type
regtype	pg_type	naam gegevenstype	integer
regconfig	pg_ts_config	text search configuration	Engels
regdictionary	pg_ts_dict	text search dictionary	simple

Pseudo Types

Het PostgreSQL typesysteem bevat een aantal speciaal-doel-ingangen die collectief pseudo-types worden genoemd. Een pseudotype kan niet worden gebruikt als een kolomdatatype, maar kan wel worden gebruikt om het argument- of resultaattype van een functie aan te geven.

De onderstaande tabel geeft een overzicht van de bestaande pseudotypen.

S. No.	Name & Description
1	any Geeft aan dat een functie elk ingevoerd gegevenstype accepteert.
2	anyelement Geeft aan dat een functie een willekeurig gegevenstype accepteert.
3	anyarray Geeft aan dat een functie een willekeurig array-gegevenstype accepteert.
4	anynonarray Geeft aan dat een functie elk niet-array gegevenstype accepteert.
5	anyenum Geeft aan dat een functie elk enum gegevenstype accepteert.
6	anyrange Geeft aan dat een functie een willekeurig range-gegevenstype accepteert.
7	cstring Geeft aan dat een functie een C-string met null-terminatie accepteert of retourneert.
8	internal Geeft aan dat een functie een server-intern gegevenstype accepteert of retourneert.
9	language_handler Een procedurele taal call handler wordt gedeclareerd om language_handler terug te geven.
10	fdw_handler Een foreign-data wrapper handler wordt gedeclareerd om fdw_handler terug te geven.
11	record Geeft een functie aan die een niet gespecificeerd type rij terugzendt.
12	trigger Een trigger functie wordt gedeclareerd om trigger terug te geven.
13	void Geeft aan dat een functie geen waarde teruggeeft.