PostgreSQL offre un ricco set di tipi di dati nativi per gli utenti. Gli utenti possono aggiungere nuovi tipi con l'aiuto del comando CREATE TYPE. Inoltre rende le query più semplici e leggibili.
Tipi di dati in PostgreSQL
PostgreSQL supporta i seguenti tipi di dati:
- Tipi di testo
- Tipi numerici
- Date e orari
- XML
- JSON
- Booleano
- Bit
- Dati binari
- Rete
- Array
- Crea il tuo tipo di dati
- Booleano
- Temporale
- UUID
- Vettore
- JSON
- Tipi di dati speciali per memorizzare un indirizzo di rete e dati geometrici.
Analizziamo in dettaglio i tipi di dati PostgreSQL
- Tipi di dati carattere
- Tipi di dati numerici
- Tipi di dati binari
- Tipo di indirizzo di rete
- Tipo di ricerca del testo
- Tipi di dati data / ora
- Tipo booleano
- Tipi di dati geometrici
- Tipi enumerati
- Tipo di intervallo
- Tipo di UUID
- Tipo XML
- Tipo JSON
- Pseudo-tipi
Tipi di dati carattere
PostgreSQL supporta i tipi di dati carattere per la memorizzazione di valori di testo. PostgreSQL crea tipi di dati di caratteri dalle stesse strutture interne. PostgreSQL offre tre tipi di dati carattere: CHAR (n), VARCHAR (n) e TEXT.
| Nome | Descrizione |
|---|---|
| varchar (n) | Consente di dichiarare la lunghezza variabile con un limite |
| Char (n) | Lunghezza fissa, imbottitura in bianco |
| Testo | L'utente può utilizzare questo tipo di dati per dichiarare una variabile con lunghezza illimitata |
Tipi di dati numerici
PostgreSQL supporta due distinti tipi di numeri:
- Interi
- Numeri in virgola mobile
| Nome | Dimensioni del negozio | Gamma |
|---|---|---|
| smallint | 2 byte | Da -32768 a +32767 |
| numero intero | 4 byte | Da -2147483648 a +2147483647 |
| bigint | 8 byte | -9223372036854775808 a 9223372036854775807 |
| decimale | variabile | Se lo hai dichiarato come il tipo di dati decimale varia da 131072 cifre prima della virgola decimale a 16383 cifre dopo la virgola decimale |
| numerico | variabile | Se lo dichiari come numero, puoi includere un numero fino a 131072 cifre prima del punto decimale fino a 16383 cifre dopo il punto decimale |
| vero | 4 byte | 6 cifre decimali di precisione |
| Doppio | 8 byte | Precisione di 15 cifre decimali |
Tipi di dati binari
Una stringa binaria è una sequenza di ottetti o byte. I tipi di dati binari Postgres sono divisi in due modi.
- Le stringhe binarie consentono di memorizzare quote di valore zero
- Ottetti non stampabili
Le stringhe di caratteri non consentono zero ottetti e non consentono inoltre altri valori e sequenze di ottetti non validi secondo le regole di codifica del set di caratteri del database.
| Nome | Dimensioni di archiviazione | Descrizione |
|---|---|---|
| Byte | Da 1 a 4 byte più la dimensione della stringa binaria | Stringa binaria a lunghezza variabile |
Tipo di indirizzo di rete
Molte applicazioni memorizzano informazioni di rete come l'indirizzo IP degli utenti o
sensori. PostgreSQL ha tre tipi nativi che ti aiutano a ottimizzare i dati di rete.
| Nome | Taglia | Descrizione |
|---|---|---|
| Cedro | 7 o 19 byes | Reti IPV4 e IPv6 |
| Inet | 7 o 19 byte | Host e reti IPV4 e IPV5 |
| macaddr | 6 byte | Indirizzi MAC |
L'utilizzo dei tipi di indirizzi di rete presenta i seguenti vantaggi
- Risparmio di spazio di archiviazione
- Controllo degli errori di input
- Funziona come la ricerca di dati per sottorete
Tipo di ricerca del testo
PostgreSQL fornisce due tipi di dati progettati per supportare la ricerca full-text. La ricerca full-text esegue la ricerca in una raccolta di documenti in linguaggio naturale per cercare quelli che corrispondono meglio a una query.
- Ricerca di testo Tsvector I tipi di variabili PostgreSQL rappresentano un documento in una forma ottimizzata per la ricerca di testo
- La ricerca di testo del tipo di query memorizza le parole chiave che devono essere cercate
Tipi di dati data / ora
Il timestamp di PostgreSQL offre una precisione in microsecondi anziché in secondi. Inoltre, hai anche la possibilità di memorizzare con o senza fuso orario. PostgreSQL convertirà il timestamp con il fuso orario in UTC al momento dell'input e lo memorizzerà.
L'inserimento di data e ora è accettato in vari formati, incluso Postgres tradizionale, ISO 8601. Compatibile con SQL ecc.
PostgreSQL supporta l'ordinamento Giorno / Mese / Anno. I formati supportati sono DMY, MDY, YMD
Tipi di dati temporali
| Nome | Taglia | Gamma | Risoluzione |
|---|---|---|---|
| Timestamp senza fuso orario | 8 byte | Dal 4713 a.C. al 294276 d.C. | 1 microsecondo / 14 cifre |
| Timestamp con fuso orario | 8 byte | Dal 4713 a.C. al 294276 d.C. | 1 microsecondo / 14 cifre |
| Data | 4 byte | Dal 4713 a.C. al 294276 d.C. | Un giorno |
| Tempo senza fuso orario | 8 byte | Dalle 00:00:00 alle 24:00:00 | 1 microsecondo / 14 cifre |
| Ora con fuso orario | 12 byte | 00:00:00 + 1459 per 24: 00: 00-1459 | 1 microsecondo / 14 cifre |
| Intervallo | 12 byte | Da -178000000 a 178000000 anni | 1 microsecondo / 14 cifre |
Esempi:
| Ingresso | Descrizione |
|---|---|
| 2025-09-07 | ISO 8601, 7 settembre con qualsiasi stile di data (formato consigliato) |
| 7 settembre 2025 | 7 settembre con qualsiasi stile di data |
| 7/9/2025 | 7 settembre con MDY, 9 luglio con DMY |
| 9/7/25 | 7 settembre 2025, con MDY |
| 2025-settembre-7 | 7 settembre con qualsiasi stile di data |
| 7 settembre 2018 | 7 settembre con qualsiasi stile di data |
| 7-set-25 | 7 settembre 2025, con YMD |
| 20250907 | ISO 8601,7 set 20225 in qualsiasi modalità |
| 2025.250 | anno e giorno dell'anno, in questo caso, 7 settembre 2025 |
| J25250 | Appuntamento giuliano |
Ora / ora con input del fuso orario
| Ingresso | Descrizione |
|---|---|
| 11: 19: 38.507 11:19:38 11:19 111938 | ISO 8601 |
| 11:19 | Uguale a 11:19 |
| 23:19 | uguale a 23:19 |
| 23: 19-3 23: 19-03: 00 231900-03 | ISO 8601, uguale alle 23:19 EST |
| 23:19 EST | fuso orario specificato come EST, uguale alle 23:19 EST |
Tipo booleano
Un tipo di dati booleano può contenere
- Vero
- Falso
- nullo
valori.
Si utilizza un bool o booleano parola chiave per dichiarare una colonna con il tipo di dati booleano.
Quando inserisci valori in una colonna booleana, Postgre converte valori come
- sì
- y
- 1
- t
- vero
in 1.
Mentre i valori come
- No
- N
- 0
- F
- Falso
vengono convertiti in 0
Durante la selezione dei dati, i valori vengono nuovamente convertiti in sì, vero, y, ecc.
Tipi di dati geometrici
I tipi di dati geometrici rappresentano oggetti spaziali bidimensionali. Aiutano a eseguire operazioni come rotazioni, ridimensionamento, traslazione, ecc.
| Nome | Dimensioni di archiviazione | Rappresentazione | Descrizione |
|---|---|---|---|
| Punto | 16 byte | Punto su un aereo | (x, y) |
| Linea | 32 byte | Linea infinita | ((xl.yl). (x2.y2)) |
| Lseg | 32 byte | Segmento di linea finita | ((xl.yl). (x2.y2)) |
| Scatola | 32 byte | Scatola rettangolare | ((xl.yl). (x2.y2)) |
| Sentiero | 16n + 16n byte | Chiudi e apri percorso | ((xl.yl), ...) |
| Poligono | 40 + 16n byte | Poligono | [(xl.yl)….] |
| Cerchio | 24 byte | Cerchio | <(xy) .r> (punto centrale e raggio) |
Tipi enumerati
Il tipo di dati PostgreSQL enumerato è utile per rappresentare informazioni che cambiano raramente come il codice paese o l'ID filiale. Il tipo di dati Enumerated è rappresentato in una tabella con chiavi esterne per garantire l'integrità dei dati.
Esempio:
Il colore dei capelli è abbastanza statico in un database demografico
CREATE TYPE hair_color AS ENUM('brown','black','red','grey','blond')
Tipo di intervallo
Molte applicazioni aziendali richiedono dati in intervalli. In genere, vengono definite due colonne (esempio: data di inizio, data di fine) per gestire gli intervalli. Questo è sia inefficiente che difficile da mantenere.
Postgre ha costruito i tipi di intervallo come segue
- int4range - Visualizza l'intervallo di numeri interi
- int8range - Visualizza l'intervallo di bigint
- numrange: mostra l'intervallo numerico
- tstrange - Ti aiuta a visualizzare il timestamp senza fuso orario
- strano: consente di visualizzare il timestamp con il fuso orario
- intervallo di date: intervallo di date
Tipo di UUID
Identificativi univoci universali (UUID) è una quantità di 128 bit generata da un algoritmo. È molto improbabile che lo stesso identificatore venga generato da un'altra persona nel mondo utilizzando lo stesso algoritmo. Ecco perché per i sistemi distribuiti, questi identificatori sono la scelta ideale in quanto offrono unicità all'interno di un singolo database. Un UUID viene scritto come un gruppo di cifre esadecimali minuscole, con vari gruppi separati da trattini.
PostgreSQL ha un tipo di dati UUID nativo che consuma 16 byte di spazio di archiviazione ... UUID è un tipo di dati ideale per le chiavi primarie.
Esempio:
d5f28c97-b962-43be-9cf8-ca1632182e8e
Postgre accetta anche forme alternative di input UUID come tutte le maiuscole, senza trattini, parentesi graffe, ecc.
Tipo XML
PostgreSQL consente di memorizzare dati XML in un tipo di dati, ma non è altro che un'estensione di un tipo di dati di testo. Ma il vantaggio è che controlla che l'XML di input sia ben formato.
Esempio:
XMLPARSE (DOCUMENT '') Data Type …
Tipo JSON
Per memorizzare i dati JSON PostgreSQL offre 2 tipi di dati
- JSON
- JSONB
| json | Jsonb |
|---|---|
| Una semplice estensione di un tipo di dati di testo con convalida JSON | Una rappresentazione binaria dei dati JSON |
| L'inserimento è veloce ma il recupero dei dati relativamente lento. | L'inserimento è lento ma seleziona (il recupero dei dati è veloce) |
| Salva i dati immessi esattamente nel modo in cui include gli spazi bianchi. | Supporta l'indicizzazione. Può ottimizzare lo spazio bianco per velocizzare il recupero. |
| Rielaborazione al recupero dei dati | Nessuna rielaborazione richiesta per il recupero dei dati |
Il tipo di dati JSON più utilizzato utilizzava noi jsonb a meno che non vi sia una necessità specifica di utilizzare il tipo di dati JSON.
Esempio:
CREATE TABLE employee (id integer NOT NULL,age integer NOT NULL,data jsonb);
INSERT INTO employee VALUES (1, 35, '{"name": "Tom Price", "tags": ["Motivated", "SelfLearner"], "onboareded": true}');
Pseudo-tipi
PostgreSQL ha molte voci speciali chiamate pseudo-tipi. Non puoi usare pseudo-tipi come tipi di colonna PostgreSQL. Sono usati per dichiarare o l'argomento della funzione o il tipo restituito.
Ciascuno degli pseudo-tipi disponibili è utile in situazioni in cui i documenti di comportamento di una funzione non corrispondono semplicemente a prendere o restituire un valore di un tipo di dati SQL specifico.
| Nome | Descrizione |
|---|---|
| Qualunque | La funzione accetta tutti i tipi di dati di input. |
| Un array | La funzione accetta qualsiasi tipo di dati array. |
| Qualsiasi elemento | La funzione accetta qualsiasi tipo di dati. |
| Qualsiasi enumerazione | La funzione accetta qualsiasi tipo di dati enum. |
| Nonarray | La funzione accetta qualsiasi tipo di dati non array. |
| Cstring | La funzione accetta o restituisce una stringa C con terminazione null. |
| Interno | La funzione interna accetta o restituisce il tipo di dati interno al server. |
| Language_handler | Viene dichiarato per restituire il gestore della lingua. |
| Disco | Trova una funzione che restituisca un tipo di riga non specificato. |
| Trigger | Una funzione trigger viene utilizzata per restituire trigger. |
È importante che l'utente che utilizza questa funzione si assicuri che la funzione si comporti in modo sicuro quando uno pseudo-tipo viene utilizzato come tipo di argomento.
Best practice per l'utilizzo dei tipi di dati
- Utilizzare il tipo di dati "testo" a meno che non si desideri limitare l'input
- Non usare mai "char".
- I numeri interi usano "int". Usa bigint solo quando hai numeri davvero grandi
- Usa "numerico" quasi sempre
- Usa float in PostgreSQL se hai un'origine dati IEEE 754
Sommario
- PostgreSQL offre un ricco set di tipi di dati nativi per gli utenti
- PostgreSQL supporta i tipi di dati carattere per la memorizzazione di valori di testo
- PostgreSQL supporta due distinti tipi di numeri: 1. numeri interi, 2. numeri in virgola mobile
- Una stringa binaria è una sequenza di byte o ottetti
- PostgreSQL ha il tipo di indirizzo di rete per aiutarti a ottimizzare l'archiviazione dei dati di rete
- Ricerca di testo Le strutture di dati di PostgreSQL sono progettate per supportare la ricerca di testo completo
- I tipi di dati Data / Ora PSQL consentono informazioni su data e ora in vari formati
- I tipi di campo Boolean Postgres possono contenere tre valori 1. Vero 2. Falso 3. Null
- I tipi di dati geometrici PostgreSQL rappresentano oggetti spaziali bidimensionali
- I tipi di dati enumerati in PostgreSQL sono utili per rappresentare informazioni che cambiano raramente come il codice del paese o l'ID di filiale
- Identificativi univoci universali (UUID) è una quantità di 128 bit generata da un algoritmo
- PostgreSQL ha molte voci speciali chiamate pseudo-tipi
- È consigliabile utilizzare il tipo di dati "testo" a meno che non si desideri limitare l'input