PostgreSQL – Tipo di dati

In questo capitolo, parleremo dei tipi di dati usati in PostgreSQL. Mentre si crea una tabella, per ogni colonna, si specifica un tipo di dati, cioè, che tipo di dati vuoi memorizzare nei campi della tabella.

Questo permette diversi benefici –

• Consistenza – Le operazioni contro colonne dello stesso tipo di dati danno risultati coerenti e sono solitamente le più veloci.

• Validazione – L’uso corretto dei tipi di dati implica la validazione del formato dei dati e il rifiuto dei dati al di fuori dell’ambito del tipo di dati.

• Compattezza – Poiché una colonna può memorizzare un solo tipo di valore, viene memorizzata in modo compatto.

• Performance – L’uso corretto dei tipi di dati fornisce la memorizzazione più efficiente dei dati. I valori memorizzati possono essere elaborati rapidamente, il che migliora le prestazioni.

PostgreSQL supporta un ampio set di tipi di dati. Inoltre, gli utenti possono creare il proprio tipo di dati personalizzato usando il comando CREATE TYPE SQL. Ci sono diverse categorie di tipi di dati in PostgreSQL. Sono discussi qui sotto.

Tipi numerici

I tipi numerici consistono in interi a due, quattro e otto byte, numeri in virgola mobile a quattro e otto byte e decimali a precisione selezionabile. La seguente tabella elenca i tipi disponibili.

Nome	Dimensione memoria	Descrizione	Range
smallint	2 bytes	small-range integer	-32768 a +32767
integer	4 byte	scelta tipica per integer	-2147483648 a +2147483647
bigint	8 byte	grande gamma di numeri interi	-9223372036854775808 a 9223372036854775807
decimale	variabile	precisione specificata dall’utente,esatta	fino a 131072 cifre prima del punto decimale; fino a 16383 cifre dopo il punto decimale
numerico	variabile	precisione specificata dall’utente, esatto	fino a 131072 cifre prima del punto decimale; fino a 16383 cifre dopo il punto decimale
reale	4 byte	precisione variabile, inesatta	6 cifre decimali precisione
double precision	8 byte	precisione variabile,inesatto	15 cifre decimali precisione
smallserial	2 byte	piccolo autoincrementante integer	1 a 32767
serial	4 byte	autoincremento integer	1 a 2147483647
bigserial	8 byte	grande intero autoincrementante	1 a 9223372036854775807

Tipi monetari

Il tipo money memorizza un importo in valuta con una precisione frazionaria fissa. I valori dei tipi di dati numerici, int e bigint possono essere convertiti in denaro. L’uso di numeri in virgola mobile non è raccomandato per gestire il denaro a causa del potenziale di errori di arrotondamento.

Nome	Dimensione dello stoccaggio	Descrizione	Range
denaro	8 byte	importo in valuta	-92233720368547758.08 a +92233720368547758.07

Tipi di carattere

La tabella seguente elenca i tipi di carattere di uso generale disponibili in PostgreSQL.

S. No.	Nome & Descrizione
1	carattere variabile(n), varchar(n) lunghezza variabile con limite
2	carattere(n), char(n) lunghezza fissa, blank padded
3	text variabile lunghezza illimitata

Tipi di dati binari

Il tipo di dati bytea permette la memorizzazione di stringhe binarie come nella tabella seguente.

Nome	Dimensione di memorizzazione	Descrizione
bytea	1 o 4 byte più la stringa binaria effettiva	stringa binaria di lunghezza variabilelunghezza stringa binaria

Tipi di data/ora

PostgreSQL supporta un set completo di tipi di data e ora SQL, come mostrato nella tabella sottostante. Le date sono contate secondo il calendario gregoriano. Qui, tutti i tipi hanno una risoluzione di 1 microsecondo / 14 cifre tranne il tipo di data, la cui risoluzione è il giorno.

Nome	Dimensione memoria	Descrizione	Valore basso	Valore alto
timestamp	8 bytes	sia la data che l’ora (senza fuso orario)	4713 BC	294276 AD
TIMESTAMPTZ	8 bytes	sia data che ora, con fuso orario	4713 BC	294276 AD
data	4 byte	data (senza ora del giorno)	4713 BC	5874897 AD
time	8 byte	ora del giorno (senza data)	00:00:00	24:00:00
ora con fuso orario	12 byte	solo ora del giorno, con fuso orario	00:00:00+1459	24:00:00-1459
intervallo	12 byte	intervallo temporale	-178000000 anni	178000000 anni

tipo booleano

PostgreSQL fornisce il tipo standard SQL Boolean. Il tipo di dati booleano può avere gli stati vero, falso e un terzo stato, sconosciuto, che è rappresentato dal valore nullo SQL.

Nome	Dimensione dello stoccaggio	Descrizione
booleano	1 byte	stato di vero o falso

Tipo enumerato

I tipi enumerati (enum) sono tipi di dati che comprendono un ordinato di valori. Sono equivalenti ai tipi enum supportati in un certo numero di linguaggi di programmazione.

A differenza di altri tipi, i tipi enumerati devono essere creati usando il comando CREATE TYPE. Questo tipo è usato per memorizzare un insieme statico e ordinato di valori. Per esempio le direzioni della bussola, cioè NORD, SUD, EST e OVEST o i giorni della settimana come mostrato sotto –

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

Enumerated, una volta creato, può essere usato come qualsiasi altro tipo.

Tipo Geometrico

I tipi di dati geometrici rappresentano oggetti spaziali bidimensionali. Il tipo più fondamentale, il punto, costituisce la base per tutti gli altri tipi.

Nome	Dimensione dello spazio	Rappresentazione	Descrizione
punto	16 byte	Punto su un piano	(x,y)
linea	32 byte	Linea infinita (non completamente implementata)	((x1,y1),(x2,y2))
lseg	32 byte	Segmento di linea finito	((x1,y1),(x2,y2))
box	32 byte	Rectangular box	((x1,y1),(x2,y2))
percorso	16+16n byte	Percorso chiuso (simile al poligono)	((x1,y1),…)
percorso	16+16n byte	percorso aperto
poligono	40+16n	Poligono (simile al percorso chiuso)	((x1,y1),.)
circle	24 byte	Circle	<(x,y),r> (punto centrale e raggio)

Tipo di indirizzo di rete

PostgreSQL offre tipi di dati per memorizzare indirizzi IPv4, IPv6 e MAC. È meglio usare questi tipi invece dei tipi di testo semplice per memorizzare gli indirizzi di rete, perché questi tipi offrono il controllo degli errori di input e operatori e funzioni specializzati.

Nome	Dimensione memoria	Descrizione
cidr	7 o 19 byte	Reti IPv4 e IPv6
inet	7 o 19 bytes	ospiti e reti IPv4 e IPv6
macaddr	6 bytes	indirizzi MAC

Bit String Type

Bit String Types sono usati per memorizzare maschere di bit. Sono 0 o 1. Ci sono due tipi di bit SQL: bit(n) e bit variabile(n), dove n è un intero positivo.

Tipo di ricerca testuale

Questo tipo supporta la ricerca full text, che è l’attività di ricerca attraverso una collezione di documenti in linguaggio naturale per individuare quelli che meglio corrispondono a una query. Ci sono due tipi di dati per questo –

S. No.	Nome & Descrizione
1	tsvector Questa è una lista ordinata di parole distinte che sono state normalizzate per unire diverse varianti della stessa parola, chiamate “lessemi”.
2	tsquery Conserva i lessemi che devono essere ricercati e li combina rispettando gli operatori booleani & (AND), | (OR) e ! (NOT). Le parentesi possono essere usate per rinforzare il raggruppamento degli operatori.

Tipo UUID

Un UUID (Universally Unique Identifiers) è scritto come una sequenza di cifre esadecimali minuscole, in diversi gruppi separati da trattini, in particolare un gruppo di otto cifre, seguito da tre gruppi di quattro cifre, seguito da un gruppo di 12 cifre, per un totale di 32 cifre che rappresentano i 128 bit.

Un esempio di UUID è – 550e8400-e29b-41d4-a716-446655440000

Tipo XML

Il tipo di dati XML può essere usato per memorizzare dati XML. Per memorizzare dati XML, prima devi creare valori XML usando la funzione xmlparse come segue –

XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title> <topics>...</topics></tutorial>')XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Tipo JSON

Il tipo di dati json può essere usato per memorizzare dati JSON (JavaScript Object Notation). Tali dati possono anche essere memorizzati come testo, ma il tipo di dati json ha il vantaggio di controllare che ogni valore memorizzato sia un valore JSON valido. Ci sono anche le relative funzioni di supporto disponibili, che possono essere utilizzate direttamente per gestire il tipo di dati JSON come segue.

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

Tipo di array

PostgreSQL dà la possibilità di definire una colonna di una tabella come un array multidimensionale di lunghezza variabile. Si possono creare array di qualsiasi tipo base incorporato o definito dall’utente, tipo enum o tipo composito.

Dichiarazione degli array

Il tipo di array può essere dichiarato come

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

o usando la parola chiave “ARRAY” come

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

Inserimento dei valori

I valori degli array possono essere inseriti come una costante letterale, racchiudendo i valori degli elementi tra parentesi graffe e separandoli con virgole. Un esempio è mostrato sotto –

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

Accedere agli array

Un esempio per accedere agli array è mostrato sotto. Il comando dato sotto selezionerà le persone i cui risparmi sono più nel secondo trimestre che nel quarto trimestre.

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Modificare gli array

Un esempio di modifica degli array è mostrato sotto.

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

o usando la sintassi dell’espressione ARRAY –

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

Cercare gli array

Un esempio di ricerca degli array è mostrato qui sotto.

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

Se la dimensione dell’array è nota, il metodo di ricerca dato sopra può essere usato. Altrimenti, l’esempio seguente mostra come cercare quando la dimensione non è nota.

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

Tipi composti

Questo tipo rappresenta una lista di nomi di campi e i loro tipi di dati, cioè, struttura di una riga o di un record di una tabella.

Dichiarazione di tipi compositi

L’esempio seguente mostra come dichiarare un tipo composito

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

Questo tipo di dati può essere usato nella creazione di tabelle come segue –

CREATE TABLE on_hand ( item inventory_item, count integer);

Ingresso valore composito

I valori compositi possono essere inseriti come una costante letterale, racchiudendo i valori di campo tra parentesi e separandoli con virgole. Un esempio è mostrato qui sotto –

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

Questo è valido per inventory_item definito sopra. La parola chiave ROW è in realtà facoltativa finché hai più di un campo nell’espressione.

Accedere ai tipi compositi

Per accedere a un campo di una colonna composita, usa un punto seguito dal nome del campo, proprio come per selezionare un campo dal nome della tabella. Per esempio, per selezionare alcuni sottocampi dalla nostra tabella di esempio on_hand, la query sarebbe come mostrato di seguito –

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

Puoi anche usare il nome della tabella (per esempio in una query multitable), come questa –

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

Tipi di intervallo

I tipi di intervallo rappresentano tipi di dati che utilizzano un intervallo di dati. I tipi di intervallo possono essere intervalli discreti (per esempio, tutti i valori interi da 1 a 10) o continui (per esempio qualsiasi punto nel tempo tra le 10:00 e le 11:00).

I tipi di intervallo costruitiin range disponibili includono i seguenti range –

• int4range – Range di interi

• int8range – Range di bigint

• numrange – Range di numeri

• tsrange – Range di timestamp senza fuso orario

• tstzrange – Range di timestamp con fuso orario

• daterange – Range di data

I tipi di range personalizzati possono essere creati per rendere disponibili nuovi tipi di range, come gli intervalli di indirizzi IP usando il tipo inet come base, o gli intervalli di float usando il tipo di dati float come base.

I tipi di intervallo supportano confini di intervallo inclusivi ed esclusivi usando rispettivamente i caratteri e ( ). Per esempio ‘[4,9)’ rappresenta tutti gli interi che partono da e includono 4 fino a ma non includono 9.

Tipi di identificatori di oggetti

Gli identificatori di oggetti (OID) sono usati internamente da PostgreSQL come chiavi primarie per varie tabelle di sistema. Se WITH OIDS è specificato o la variabile di configurazione default_with_oids è abilitata, solo allora, in questi casi gli OID sono aggiunti alle tabelle create dall’utente. La seguente tabella elenca diversi tipi di alias. I tipi di alias OID non hanno operazioni proprie tranne che per le routine specializzate di input e output.

Nome		Descrizione	Valore Esempio
oid	any	numerico identificatore dell’oggetto	564182
regproc	pg_proc	nome funzione	sum
regprocedura	pg_proc	funzione con tipi di argomenti	sum(int4)
regoper	pg_operator	nome dell’operatore	+
regoperator	pg_operator	operatore con tipi di argomento	*(integer,intero) o -(NESSUNO,integer)
regclass	pg_class	nome della relazione	pg_type
regtype	pg_type	nome del tipo di dati	integrale
regconfig	pg_ts_config	configurazione ricerca testo	Inglese
regdictionary	pg_ts_dict	dizionario ricerca testo	simple

Pseudo Tipi

Il sistema di tipi PostgreSQL contiene una serie di vocispeciali che sono chiamati collettivamente pseudo-tipi. Uno pseudo-tipo non può essere usato come tipo di dati di una colonna, ma può essere usato per dichiarare il tipo di argomento o risultato di una funzione.

La tabella seguente elenca gli pseudo-tipi esistenti.

S. No.	Nome & Descrizione
1	any Indica che una funzione accetta qualsiasi tipo di dati in ingresso.
2	anyelement Indica che una funzione accetta qualsiasi tipo di dati.
3	anyarray Indica che una funzione accetta qualsiasi tipo di dati array.
4	anynonarray Indica che una funzione accetta qualsiasi tipo di dati non-array.
5	anyenum Indica che una funzione accetta qualsiasi tipo di dati enum.
6	anyrange Indica che una funzione accetta qualsiasi tipo di dati range.
7	cstring Indica che una funzione accetta o ritorna una stringa C con terminazione nulla.
8	internal Indica che una funzione accetta o restituisce un tipo di dati interno al server.
9	language_handler Un gestore di chiamate in linguaggio procedurale è dichiarato per restituire language_handler.
10	fdw_handler Un gestore di wrapper di dati stranieri è dichiarato per restituire fdw_handler.
11	record Identifica una funzione che restituisce un tipo di riga non specificato.
12	trigger È dichiarata una funzione trigger che restituisce trigger.
13	void Indica che una funzione non restituisce alcun valore.