PostgreSQL - Tipo di dati
In questo capitolo, discuteremo dei tipi di dati utilizzati in PostgreSQL. Durante la creazione della tabella, per ogni colonna, specifichi un tipo di dati, cioè che tipo di dati vuoi memorizzare nei campi della tabella.
Ciò consente diversi vantaggi:
Consistency - Le operazioni su colonne dello stesso tipo di dati danno risultati coerenti e di solito sono le più veloci.
Validation - L'uso corretto dei tipi di dati implica la convalida del formato dei dati e il rifiuto dei dati al di fuori dell'ambito del tipo di dati.
Compactness - Poiché una colonna può memorizzare un singolo tipo di valore, viene memorizzata in modo compatto.
Performance- L'uso corretto dei tipi di dati offre la memorizzazione più efficiente dei dati. I valori memorizzati possono essere elaborati rapidamente, il che migliora le prestazioni.
PostgreSQL supporta un'ampia gamma di tipi di dati. Inoltre, gli utenti possono creare il proprio tipo di dati personalizzato utilizzando il comando SQL CREATE TYPE . Esistono diverse categorie di tipi di dati in PostgreSQL. Sono discussi di seguito.
Tipi numerici
I tipi numerici sono costituiti da numeri interi a due, quattro e otto byte, numeri a virgola mobile a quattro e otto byte e decimali con precisione selezionabile. La tabella seguente elenca i tipi disponibili.
| Nome | Dimensioni di archiviazione | Descrizione | Gamma |
|---|---|---|---|
| smallint | 2 byte | numero intero di piccole dimensioni | Da -32768 a +32767 |
| numero intero | 4 byte | scelta tipica per intero | Da -2147483648 a +2147483647 |
| bigint | 8 byte | numero intero di ampia portata | -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, esatta | fino a 131072 cifre prima del punto decimale; fino a 16383 cifre dopo il punto decimale |
| vero | 4 byte | precisione variabile, inesatta | 6 cifre decimali di precisione |
| doppia precisione | 8 byte | precisione variabile, inesatta | Precisione di 15 cifre decimali |
| smallserial | 2 byte | piccolo intero autoincrementante | Da 1 a 32767 |
| seriale | 4 byte | numero intero autoincrementante | 1 al 2147483647 |
| bigserial | 8 byte | grande intero autoincrementante | 1 fino a 9223372036854775807 |
Tipi monetari
Il tipo di denaro memorizza un importo in valuta con una precisione frazionaria fissa. I valori dei tipi di dati numerico, int e bigint possono essere convertiti in denaro . L'utilizzo di numeri in virgola mobile non è consigliato per gestire il denaro a causa della possibilità di errori di arrotondamento.
| Nome | Dimensioni di archiviazione | Descrizione | Gamma |
|---|---|---|---|
| i soldi | 8 byte | importo in valuta | -92233720368547758.08 a +92233720368547758.07 |
Tipi di carattere
La tabella riportata di seguito elenca i tipi di caratteri generici disponibili in PostgreSQL.
| S. No. | Nome e descrizione |
|---|---|
| 1 | character varying(n), varchar(n) lunghezza variabile con limite |
| 2 | character(n), char(n) lunghezza fissa, imbottitura in bianco |
| 3 | text lunghezza illimitata variabile |
Tipi di dati binari
Il tipo di dati bytea consente la memorizzazione di stringhe binarie come nella tabella riportata di seguito.
| Nome | Dimensioni di archiviazione | Descrizione |
|---|---|---|
| bytea | 1 o 4 byte più la stringa binaria effettiva | stringa binaria di lunghezza variabile |
Tipi di data / ora
PostgreSQL supporta un set completo di tipi di data e ora SQL, come mostrato nella tabella seguente. Le date vengono contate secondo il calendario gregoriano. Qui, tutti i tipi hanno una risoluzione di1 microsecond / 14 digits tranne date tipo, la cui risoluzione è day.
| Nome | Dimensioni di archiviazione | Descrizione | Basso valore | Alto valore |
|---|---|---|---|---|
| timestamp [(p)] [senza fuso orario] | 8 byte | sia la data che l'ora (nessun fuso orario) | 4713 a.C. | 294276 d.C. |
| TIMESTAMPTZ | 8 byte | sia la data che l'ora, con il fuso orario | 4713 a.C. | 294276 d.C. |
| Data | 4 byte | data (nessuna ora del giorno) | 4713 a.C. | 5874897 d.C. |
| ora [(p)] [senza fuso orario] | 8 byte | ora del giorno (nessuna data) | 00:00:00 | 24:00:00 |
| ora [(p)] con fuso orario | 12 byte | solo ore del giorno, con fuso orario | 00: 00: 00 + 1459 | 24: 00: 00-1459 |
| intervallo [campi] [(p)] | 12 byte | Intervallo di tempo | -178000000 anni | 178000000 anni |
Tipo booleano
PostgreSQL fornisce il tipo SQL standard booleano. Il tipo di dati booleano può avere gli stati true , false e un terzo stato, unknown , rappresentato dal valore null SQL.
| Nome | Dimensioni di archiviazione | Descrizione |
|---|---|---|
| booleano | 1 byte | stato di vero o falso |
Tipo enumerato
I tipi enumerati (enum) sono tipi di dati che comprendono un insieme di valori statico e ordinato. Sono equivalenti ai tipi enum supportati in numerosi linguaggi di programmazione.
A differenza di altri tipi, i tipi enumerati devono essere creati utilizzando il comando CREATE TYPE. Questo tipo viene utilizzato per memorizzare un insieme di valori statico e ordinato. Ad esempio le direzioni della bussola, ovvero, NORD, SUD, EST e OVEST o giorni della settimana come mostrato di seguito -
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');Enumerato, una volta creato, può essere utilizzato 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 | Dimensioni di archiviazione | Rappresentazione | Descrizione |
|---|---|---|---|
| punto | 16 byte | Punto su un aereo | (x, y) |
| linea | 32 byte | Linea infinita (non completamente implementata) | ((x1, y1), (x2, y2)) |
| lseg | 32 byte | Segmento di linea finita | ((x1, y1), (x2, y2)) |
| scatola | 32 byte | Scatola rettangolare | ((x1, y1), (x2, y2)) |
| sentiero | 16 + 16n byte | Percorso chiuso (simile al poligono) | ((x1, y1), ...) |
| sentiero | 16 + 16n byte | Percorso aperto | [(x1, y1), ...] |
| poligono | 40 + 16n | Poligono (simile al percorso chiuso) | ((x1, y1), ...) |
| cerchio | 24 byte | Cerchio | <(x, y), r> (punto centrale e raggio) |
Tipo di indirizzo di rete
PostgreSQL offre tipi di dati per memorizzare indirizzi IPv4, IPv6 e MAC. È preferibile utilizzare questi tipi invece dei tipi di testo normale per memorizzare gli indirizzi di rete, poiché questi tipi offrono il controllo degli errori di input e operatori e funzioni specializzati.
| Nome | Dimensioni di archiviazione | Descrizione |
|---|---|---|
| cidr | 7 o 19 byte | Reti IPv4 e IPv6 |
| inet | 7 o 19 byte | Host e reti IPv4 e IPv6 |
| macaddr | 6 byte | Indirizzi MAC |
Tipo di stringa di bit
I tipi di stringa di bit vengono utilizzati per memorizzare le maschere di bit. Possono essere 0 o 1. Esistono due tipi di bit SQL:bit(n) e bit varying(n), dove n è un numero intero positivo.
Tipo di ricerca del testo
Questo tipo supporta la ricerca full text, ovvero l'attività di ricerca in una raccolta di documenti in linguaggio naturale per individuare quelli che corrispondono meglio a una query. Ci sono due tipi di dati per questo:
| S. No. | Nome e descrizione |
|---|---|
| 1 | tsvector Questo è un elenco ordinato di parole distinte che sono state normalizzate per unire diverse varianti della stessa parola, chiamate "lessemi". |
| 2 | tsquery Memorizza i lessemi da cercare e li combina rispettando gli operatori booleani & (AND), | (OR) e! (NON). Le parentesi possono essere utilizzate per imporre 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 utilizzato per memorizzare i dati XML. Per memorizzare i dati XML, prima devi creare valori XML utilizzando 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 utilizzato per memorizzare i dati JSON (JavaScript Object Notation). Tali dati possono anche essere archiviati come testo , ma il tipo di dati json ha il vantaggio di verificare che ogni valore memorizzato sia un valore JSON valido. Sono disponibili anche funzioni di supporto correlate, che possono essere utilizzate direttamente per gestire il tipo di dati JSON come segue.
| Esempio | Risultato di esempio |
|---|---|
| array_to_json ('{{1,5}, {99,100}}' :: int []) | [[1,5], [99,100]] |
| row_to_json (row (1, 'foo')) | {"f1": 1, "f2": "foo"} |
Tipo di array
PostgreSQL offre l'opportunità di definire una colonna di una tabella come un array multidimensionale di lunghezza variabile. È possibile creare array di qualsiasi tipo base predefinito o definito dall'utente, tipo enum o tipo composto.
Dichiarazione di array
Il tipo di array può essere dichiarato come
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer[],
scheme text[][]
);o utilizzando la parola chiave "ARRAY" come
CREATE TABLE monthly_savings (
name text,
saving_per_quarter integer ARRAY[4],
scheme text[][]
);Inserimento di valori
I valori degli array possono essere inseriti come costanti letterali, racchiudendo i valori degli elementi tra parentesi graffe e separandoli con virgole. Di seguito è mostrato un esempio:
INSERT INTO monthly_savings
VALUES (‘Manisha’,
‘{20000, 14600, 23500, 13250}’,
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);Accesso agli array
Di seguito è mostrato un esempio per l'accesso agli array. Il comando dato di seguito selezionerà le persone i cui risparmi sono maggiori nel secondo trimestre rispetto al quarto trimestre.
SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];Modifica di array
Un esempio di modifica degli array è mostrato di seguito.
UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';o utilizzando la sintassi dell'espressione ARRAY -
UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';Ricerca di array
Un esempio di ricerca negli array è mostrato di seguito.
SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;Se la dimensione dell'array è nota, è possibile utilizzare il metodo di ricerca indicato sopra. Altrimenti, il seguente esempio mostra come eseguire la ricerca quando la dimensione non è nota.
SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);Tipi compositi
Questo tipo rappresenta un elenco di nomi di campo e dei relativi tipi di dati, ovvero la struttura di una riga o un record di una tabella.
Dichiarazione dei tipi compositi
L'esempio seguente mostra come dichiarare un tipo composto
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);Questo tipo di dati può essere utilizzato nelle tabelle di creazione come di seguito:
CREATE TABLE on_hand (
item inventory_item,
count integer
);Input di valore composito
I valori composti possono essere inseriti come costante letterale, racchiudendo i valori del campo tra parentesi e separandoli con virgole. Di seguito è mostrato un esempio:
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);Questo è valido per inventario_item definito sopra. La parola chiave ROW è in realtà facoltativa fintanto che hai più di un campo nell'espressione.
Accesso ai tipi compositi
Per accedere a un campo di una colonna composta, utilizzare un punto seguito dal nome del campo, proprio come selezionare un campo dal nome di una tabella. Ad esempio, per selezionare alcuni sottocampi dalla nostra tabella di esempio on_hand, la query sarà come mostrato di seguito:
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;Puoi anche usare anche il nome della tabella (ad esempio in una query multitabile), in questo modo:
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;Tipi di intervallo
I tipi di intervallo rappresentano i tipi di dati che utilizzano un intervallo di dati. Il tipo di intervallo può essere intervalli discreti (ad esempio, tutti i valori interi da 1 a 10) o intervalli continui (ad esempio, qualsiasi momento tra le 10:00 e le 11:00).
I tipi di gamma incorporati disponibili includono le seguenti gamme:
int4range - Intervallo di numeri interi
int8range - Gamma di bigint
numrange - Intervallo numerico
tsrange - Intervallo di timestamp senza fuso orario
tstzrange - Intervallo di timestamp con fuso orario
daterange - Intervallo di date
È possibile creare tipi di intervallo personalizzati per rendere disponibili nuovi tipi di intervalli, come intervalli di indirizzi IP che utilizzano il tipo inet come base o intervalli float utilizzando il tipo di dati float come base.
I tipi di intervallo supportano limiti di intervallo inclusivi ed esclusivi utilizzando rispettivamente i caratteri [] e (). Ad esempio "[4,9)" rappresenta tutti i numeri interi a partire da e compreso 4 fino a 9 ma escluso.
Tipi di identificatori di oggetti
Gli identificatori di oggetto (OID) vengono utilizzati internamente da PostgreSQL come chiavi primarie per varie tabelle di sistema. Se viene specificato WITH OIDS o la variabile di configurazione default_with_oids è abilitata, solo allora, in questi casi, gli OID vengono aggiunti alle tabelle create dall'utente. La tabella seguente elenca diversi tipi di alias. I tipi di alias OID non hanno operazioni proprie tranne che per routine di input e output specializzate.
| Nome | Riferimenti | Descrizione | Esempio di valore |
|---|---|---|---|
| oid | qualunque | identificatore numerico di oggetto | 564182 |
| regproc | pg_proc | nome della funzione | somma |
| regprocedure | pg_proc | funzione con tipi di argomenti | somma (int4) |
| regoper | pg_operator | nome dell'operatore | + |
| registratore | pg_operator | operatore con tipi di argomenti | * (intero, intero) o - (NESSUNO, intero) |
| regclass | pg_class | nome della relazione | pg_type |
| regtype | pg_type | nome del tipo di dati | numero intero |
| regconfig | pg_ts_config | configurazione della ricerca di testo | Inglese |
| regdictionary | pg_ts_dict | dizionario di ricerca di testo | semplice |
Pseudo tipi
Il sistema di tipi PostgreSQL contiene una serie di voci speciali che vengono chiamate collettivamente pseudo-tipi. Uno pseudo-tipo non può essere utilizzato come tipo di dati di colonna, ma può essere utilizzato per dichiarare l'argomento o il tipo di risultato di una funzione.
La tabella riportata di seguito elenca gli pseudo-tipi esistenti.
| S. No. | Nome e descrizione |
|---|---|
| 1 | any Indica che una funzione accetta qualsiasi tipo di dati di input. |
| 2 | anyelement Indica che una funzione accetta qualsiasi tipo di dati. |
| 3 | anyarray Indica che una funzione accetta qualsiasi tipo di dati di matrice. |
| 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 di intervallo. |
| 7 | cstring Indica che una funzione accetta o restituisce una stringa C con terminazione null. |
| 8 | internal Indica che una funzione accetta o restituisce un tipo di dati interno al server. |
| 9 | language_handler Viene dichiarato un gestore di chiamate di linguaggio procedurale per restituire language_handler. |
| 10 | fdw_handler Un gestore wrapper di dati esterni viene dichiarato per restituire fdw_handler. |
| 11 | record Identifica una funzione che restituisce un tipo di riga non specificato. |
| 12 | trigger Una funzione trigger viene dichiarata per restituire trigger. |
| 13 | void Indica che una funzione non restituisce alcun valore. |