PostgreSQL – Tipuri de date

În acest capitol, vom discuta despre tipurile de date utilizate în PostgreSQL. În timpul creării tabelei, pentru fiecare coloană, se specifică un tip de date, de ex, ce tip de date doriți să stocați în câmpurile tabelei.

Acest lucru permite mai multe beneficii –

• Consistență – Operațiile împotriva coloanelor de același tip de date dau rezultate consistente și sunt de obicei cele mai rapide.

• Validare – Utilizarea corectă a tipurilor de date implică validarea formatului datelor și respingerea datelor în afara domeniului de aplicare a tipului de date.

• Compactitate – Deoarece o coloană poate stoca un singur tip de valoare, aceasta este stocată într-un mod compact.

• Performanță – Utilizarea corectă a tipurilor de date oferă cea mai eficientă stocare a datelor. Valorile stocate pot fi procesate rapid, ceea ce sporește performanța.

PostgreSQL suportă un set larg de tipuri de date. În plus, utilizatorii își pot crea propriul tip de date personalizat folosind comanda CREATE TYPE SQL. Există diferite categorii de tipuri de date în PostgreSQL. Acestea sunt discutate mai jos.

Tipuri numerice

Tipurile numerice constau în numere întregi de doi, patru și opt octeți, numere cu virgulă mobilă de patru și opt octeți și zecimale cu precizie selectabilă. Tabelul următor enumeră tipurile disponibile.

Nume	Dimensiunea de stocare	Descriere	Regim
smallint	2 octeți	small-.range integer	-32768 to +32767
integer	4 bytes	alegere tipică pentru integer	-.2147483648 până la +2147483647
bigint	8 octeți	întreg cu interval mare de valori	-9223372036854775808 până la 9223372036854775807
decimal	variabilă	precizia specificată de utilizator,exactă	până la 131072 de cifre înainte de virgulă; până la 16383 de cifre după virgulă
numeric	variabilă	precizie specificată de utilizator,exactă	până la 131072 de cifre înainte de virgulă; până la 16383 de cifre după virgulă
real	4 octeți	precizie variabilă,inexactă	6 cifre zecimale precizie
precizie dublă	8 octeți	precizie variabilă,inexactă	15 cifre zecimale precizie
smallserial	2 octeți	small autoincrementare	small autoincrementare integer	1 până la 32767
serial	4 octeți	autoincrementare integer	1 până la 2147483647
bigserial	8 octeți	întreg mare cu autoincrementare	1 până la 9223372036854775807

Tipuri monetare

Tipul monetar stochează o sumă în valută cu o precizie fracționară fixă. Valorile din tipurile de date numeric, int și bigint pot fi transformate în bani. Utilizarea numerelor cu virgulă flotantă nu este recomandată pentru a gestiona banii din cauza potențialului de erori de rotunjire.

Name	Storage Size	Description	Range
money	8 bytes	currency amount	-92233720368547758.08 până la +92233720368547758.07

Tipuri de caractere

Tabelul de mai jos enumeră tipurile de caractere de uz general disponibile în PostgreSQL.

S. Nr.	Nume & Descriere
1	caracter variind(n), varchar(n) lungime variabilă cu limită
2	caracter(n), char(n) lungime fixă, blank padded
3	text lungime variabilă nelimitată

Tipuri de date binare

Tipul de date bytea permite stocarea de șiruri de caractere binare, conform tabelului de mai jos.

Nume	Dimensiunea de stocare	Descriere
bytea	1 sau 4 octeți plus șirul binar propriu-zis	variabilă-lungime șir binar

Tipuri de dată/ora

PostgreSQL acceptă un set complet de tipuri de date și ore SQL, așa cum se arată în tabelul de mai jos. Datele sunt numărate în conformitate cu calendarul gregorian. Aici, toate tipurile au rezoluția de 1 microsecundă / 14 cifre, cu excepția tipului de dată, a cărui rezoluție este ziua.

.

Name	Storage Size	Description	Low Value	High Value
timestamp	8 bytes	atât data cât și ora (fără fus orar)	4713 BC	294276 AD
TIMESTAMPTZ	8 bytes	atât data cât și ora, cu fus orar	4713 î.Hr.	294276 d.Hr.
data	4 octeți	data (fără ora din zi)	4713 î.Hr.	5874897 d.Hr.
time	8 octeți	ora zilei (fără dată)	00:00:00	24:00:00:00
ora cu fus orar	12 octeți	oră doar a zilei, cu fus orar	00:00:00+1459	24:00:00-1459
interval	12 octeți	interval de timp	-178000000 ani	178000000 ani

Tip boolean

PostgreSQL oferă tipul standard SQL Boolean. Tipul de date Boolean poate avea stările true, false și o a treia stare, necunoscută, care este reprezentată de valoarea SQL null.

.

Name	Storage Size	Description
boolean	1 byte	stare de adevărat sau fals

Tipuri enumerative

Tipurile enumerative (enum) sunt tipuri de date care cuprind o statică, ordonat de valori. Ele sunt echivalente cu tipurile enum acceptate într-un număr de limbaje de programare.

În mod diferit de alte tipuri, tipurile enumerative trebuie să fie create cu ajutorul comenzii CREATE TYPE. Acest tip este utilizat pentru a stoca un set static, ordonat de valori. De exemplu, direcțiile busolei, adică NORTH, SOUTH, EAST și WEST sau zilele săptămânii, așa cum se arată mai jos –

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

Enumerated, odată creat, poate fi utilizat ca orice alt tip.

Tipuri geometrice

Tipurile de date geometrice reprezintă obiecte spațiale bidimensionale. Cel mai fundamental tip, punctul, constituie baza pentru toate celelalte tipuri.

Nume	Dimensiunea de stocare	Reprezentare	Descriere
punct	16 octeți	Punct pe un plan	(x,y)
linie	32 octeți	Linie infinită (neimplementată complet)	((x1,y1),(x2,y2))
lseg	32 octeți	Segment de linie finită	((x1,y1),(x2,y2))
box	32 octeți	Căsuță dreptunghiulară	((x1,y1),(x2,y2))
Calea	16+16n octeți	Calea închisă (similar cu poligonul)	((x1,y1),…)
Calea	16+16n octeți	Calea deschisă
poligon	40+16n	Poligon (similar cu calea închisă)	((x1,y1),….)
circuit	24 octeți	Circuit	<(x,y),r> (punct central și rază)

Tip de adresă de rețea

PostgreSQL oferă tipuri de date pentru a stoca adrese IPv4, IPv6 și MAC. Este mai bine să folosiți aceste tipuri în locul tipurilor de text simplu pentru a stoca adresele de rețea, deoarece aceste tipuri oferă verificarea erorilor de intrare și operatori și funcții specializate.

Nume	Dimensiunea de stocare	Descriere
cidr	7 sau 19 octeți	Rețele IPv4 și IPv6
inet	7 sau 19 octeți	Rețele și gazde IPv4 și IPv6
macaddr	6 octeți	Adrese MAC

Tip de șir de biți

Tipurile de șir de biți sunt utilizate pentru a stoca măști de biți. Ele sunt fie 0, fie 1. Există două tipuri de biți SQL: bit(n) și bit varying(n), unde n este un număr întreg pozitiv.

Tip de căutare text

Acest tip suportă căutarea textului complet, care este activitatea de căutare într-o colecție de documente în limbaj natural pentru a le localiza pe cele care corespund cel mai bine unei interogări. Există două tipuri de date pentru acest tip –

S. No.	Nume & Descriere
1	tsvector Este o listă ordonată de cuvinte distincte care au fost normalizate pentru a uni diferite variante ale aceluiași cuvânt, numite „lexeme”.
2	tsquery Aceasta stochează lexemele care urmează să fie căutate și le combină onorând operatorii booleeni & (AND), | (OR), și ! (NOT). Parantezele pot fi folosite pentru a impune gruparea operatorilor.

UUID Type

Un UUID (Universally Unique Identifiers) este scris ca o secvență de cifre hexazecimale minuscule, în mai multe grupe separate prin cratimă, mai exact un grup de opt cifre, urmat de trei grupe de patru cifre, urmate de un grup de 12 cifre, pentru un total de 32 de cifre care reprezintă cei 128 de biți.

Un exemplu de UUID este – 550e8400-e29b-41d4-a716-446655440000

Tip XML

Tipul de date XML poate fi utilizat pentru a stoca date XML. Pentru a stoca date XML, mai întâi trebuie să creați valori XML utilizând funcția xmlparse după cum urmează –

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

Tipul de date json poate fi utilizat pentru a stoca date JSON (JavaScript Object Notation). Astfel de date pot fi stocate și sub formă de text, dar tipul de date json are avantajul de a verifica dacă fiecare valoare stocată este o valoare JSON validă. Există, de asemenea, funcții de asistență conexe disponibile, care pot fi utilizate direct pentru a gestiona tipul de date JSON, după cum urmează.

Exemplu	Exemplu Rezultat
array_to_json(‘{{1,5}},{99,100}}’::int)	,]
row_to_json(row(1,’foo’))	{„f1”:1, „f2”: „foo”}

Tip de matrice

PostgreSQL oferă posibilitatea de a defini o coloană a unei tabele ca o matrice multidimensională de lungime variabilă. Pot fi create array-uri de orice tip de bază încorporat sau definit de utilizator, tip enum sau tip compozit.

Declararea array-urilor

Tipul de array poate fi declarat ca

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

sau prin utilizarea cuvântului cheie „ARRAY” ca

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

Inserarea valorilor

Valorile array-urilor pot fi inserate ca o constantă literală, încadrând valorile elementelor între paranteze curbe și separându-le prin virgule. Un exemplu este prezentat mai jos –

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

Accesarea Array-urilor

Un exemplu de accesare a Array-urilor este prezentat mai jos. Comanda dată mai jos va selecta persoanele ale căror economii sunt mai mari în trimestrul al doilea decât în trimestrul al patrulea.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Modificarea Array-urilor

Un exemplu de modificare a Array-urilor este cel prezentat mai jos.

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

sau folosind sintaxa expresiei ARRAY –

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

Cercetarea în array-uri

Un exemplu de căutare în array-uri este cel prezentat mai jos.

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

Dacă dimensiunea array-ului este cunoscută, se poate folosi metoda de căutare prezentată mai sus. În caz contrar, exemplul de mai jos arată modul de căutare atunci când dimensiunea nu este cunoscută.

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

Tipuri compuse

Acest tip reprezintă o listă de nume de câmpuri și tipurile de date ale acestora, de ex, structura unui rând sau a unei înregistrări dintr-un tabel.

Declararea tipurilor compuse

Exemplul următor arată cum se declară un tip compozit

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

Acest tip de date poate fi utilizat în tabelele de creare, după cum urmează –

CREATE TABLE on_hand ( item inventory_item, count integer);

Composite Value Input

Valorile compuse pot fi inserate ca o constantă literală, încadrând valorile câmpului între paranteze și separându-le prin virgule. Un exemplu este prezentat mai jos –

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

Acest lucru este valabil pentru inventory_item definit mai sus. Cuvântul cheie ROW este de fapt opțional atâta timp cât aveți mai mult de un câmp în expresie.

Accesarea tipurilor compuse

Pentru a accesa un câmp al unei coloane compuse, folosiți un punct urmat de numele câmpului, la fel ca la selectarea unui câmp din numele unei tabele. De exemplu, pentru a selecta câteva subcâmpuri din tabelul nostru de exemplu on_hand, interogarea ar fi așa cum se arată mai jos –

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

Puteți utiliza chiar și numele tabelului (de exemplu, într-o interogare multitable), astfel –

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

Tipuri de interval

Tipurile de interval reprezintă tipuri de date care utilizează un interval de date. Tipurile de interval pot fi intervale discrete (de exemplu, toate valorile întregi de la 1 la 10) sau intervale continue (de ex, orice punct în timp între 10:00am și 11:00am).

Amplasamentul construit-în tipurile de intervale disponibile includ următoarele intervale –

• int4range – Interval de numere întregi

• int8range – Interval de bigint

• numrange – Interval de numere

• tsrange – Interval de numere numerice

• tsrange – Range of timestamp without time zone

• tstzrange – Range of timestamp with time zone

• daterange – Range of date

Se pot crea tipuri de intervale personalizate pentru a face disponibile noi tipuri de intervale, cum ar fi intervale de adrese IP care utilizează tipul inet ca bază sau intervale float care utilizează tipul de date float ca bază.

Tipurile de intervale suportă limite de intervale inclusive și exclusive folosind caracterele și, respectiv, ( ). De exemplu, ‘[4,9)’ reprezintă toate numerele întregi începând de la 4 și incluzând 4 până la 9, dar fără a include 9.

Tipuri de identificatori de obiect

Identificatorii de obiect (OID) sunt utilizați în mod intern de PostgreSQL ca chei primare pentru diverse tabele de sistem. Dacă este specificat WITH OIDS sau dacă este activată variabila de configurare default_with_oids, numai atunci, în astfel de cazuri, OID-urile sunt adăugate la tabelele create de utilizator. Tabelul următor enumeră mai multe tipuri de alias-uri. Tipurile de alias OID nu au operații proprii, cu excepția rutinelor specializate de intrare și ieșire.

.

.

.

Nume		Descriere	Valoare Exemplu
oid	any	numeric identificator obiect	564182
regproc	pg_proc	numele funcției	sum
regprocedură	pg_proc	funcție cu tipuri de argumente	sum(int4)
regoperator	pg_operator	nume operator	+
regoperator	pg_operator	operator cu tipuri de argumente	*(integer,integer) sau -(NONE,integer)
regclass	pg_class	relation name	pg_type
regtype	pg_type	data type name	integer
regconfig	pg_ts_config	regconfig	pg_ts_config	configurare căutare text	engleză
regdictionary	pg_ts_dict	dicționar căutare text	simplu

Tipuri pseudo

Sistemul de tipuri PostgreSQL conține un număr de tipuri speciale-intrări cu scop special care sunt denumite colectiv pseudo-tipuri. Un pseudotip nu poate fi folosit ca tip de date de coloană, dar poate fi folosit pentru a declara tipul de argument sau de rezultat al unei funcții.

Tabelul de mai jos enumeră pseudotipurile existente.

S. Nr.	Nume & Descriere
1	any Indică faptul că o funcție acceptă orice tip de date de intrare.
2	anyelement Indică faptul că o funcție acceptă orice tip de date.
3	anyarray Indică faptul că o funcție acceptă orice tip de date de tip array.
4	anynonarray Indică faptul că o funcție acceptă orice tip de date de tip non-array.
5	anyenum Indică faptul că o funcție acceptă orice tip de date de tip enum.
6	anyrange Indică faptul că o funcție acceptă orice tip de date de tip range.
7	cstring Indică faptul că o funcție acceptă sau returnează un șir de caractere C cu terminație nulă.
8	internal Indică faptul că o funcție acceptă sau returnează un tip de date intern al serverului.
9	language_handler Se declară că un gestionar de apel de limbaj procedural returnează language_handler.
10	fdw_handler Un gestionar de înveliș de date străine este declarat pentru a returna fdw_handler.
11	record Identifică o funcție care returnează un tip de rând nespecificat.
12	trigger O funcție de declanșare este declarată pentru a returna trigger.
13	void Indică faptul că o funcție nu returnează nicio valoare.

.