PostgreSQL数据类型

In questa sezione, discuteremo i tipi di dati di PostgreSQL, che sono impostati per ogni campo quando creiamo una tabella.

Vantaggi dell'impostazione dei tipi di dati:

PostgreSQL offre una vasta gamma di tipi di dati. Gli utenti possono utilizzare il comando CREATE TYPE per creare nuovi tipi di dati nel database. PostgreSQL ha molti tipi di dati, e ora li esamineremo più da vicino.

Tipi numerici

I tipi numerici sono composti da interi a 2 byte, 4 byte o 8 byte, numeri a virgola mobile a 4 byte o 8 byte e decimali con precisione opzionale.

La tabella sottostante elenca i tipi numerici disponibili.

Tipi monetari

Il tipo money memorizza importi monetari con precisione decimale fissa.

I valori di numeric, int e bigint possono essere convertiti in money, non si raccomanda l'uso di numeri a virgola mobile per gestire i tipi monetari, poiché c'è la possibilità di errori di arrotondamento.

Tipi di carattere

La tabella sottostante elenca i tipi di carattere supportati da PostgreSQL:

Tipi di data/ora

La tabella sottostante elenca i tipi di data e ora supportati da PostgreSQL.

Tipo booleano

PostgreSQL supporta il tipo di dati booleano standard.

Il tipo boolean ha due stati: "true" (vero) o "false" (falso), e un terzo stato "unknown" (sconosciuto) rappresentato da NULL.

Tipi enumerati

Il tipo enumerato è un tipo di dati che contiene un insieme ordinato di valori statici.

I tipi enumerati in PostgreSQL sono simili ai tipi enum del linguaggio C.

A differenza degli altri tipi, i tipi enumerati devono essere creati utilizzando il comando CREATE TYPE.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

La creazione di alcuni giorni della settimana è illustrata di seguito:

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

Come altri tipi, una volta creato, il tipo enumerato può essere utilizzato per definizioni di tabelle e funzioni.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
　　　　name text,
　　　　current_mood mood
);
INSERT INTO person VALUES ('Moe', 'felice');
SELECT * FROM person WHERE current_mood = 'felice';
　name | current_mood　
------+--------------
　Moe | felice
(1 riga)

Tipi geometrici

Il tipo di dati geometrici rappresenta oggetti piani bidimensionali.

La tabella sottostante elenca i tipi geometrici supportati da PostgreSQL.

Il tipo più fondamentale: il punto. È la base per altri tipi.

Tipi di dati di indirizzo di rete

PostgreSQL fornisce tipi di dati per memorizzare indirizzi IPv4, IPv6 e MAC.

Memorizzare indirizzi di rete con questi tipi di dati è meglio che con i tipi di dati di testo puro, perché questi tipi di dati forniscono controlli sugli errori di input e operazioni speciali e funzioni.

Quando si ordinano i tipi di dati inet o cidr, gli indirizzi IPv4 sono sempre davanti agli indirizzi IPv6, inclusi quelli encapsulati o mappati su indirizzi IPv6, come ::10.2.3.4 o ::ffff:10.4.3.2.

Tipo di stringa bit

La stringa bit è una sequenza di stringhe di 1 e 0. Possono essere utilizzate per memorizzare e visualizzare maschere bit. Abbiamo due tipi SQL bit: bit(n) e bit varying(n), dove n è un intero positivo.

I dati di tipo bit devono corrispondere esattamente alla lunghezza n, cercare di memorizzare dati più corti o più lunghi è errato. Il tipo di dati bit varying è il tipo a lunghezza variabile massima n; le stringhe più lunghe vengono rifiutate. Scrivere un bit senza lunghezza è equivalente a bit(1), un bit varying senza lunghezza significa che non c'è limitazione di lunghezza.

Tipo di ricerca testuale

La ricerca full-text è trovare documenti in una raccolta di linguaggio naturale che corrispondono a una query di ricerca.

PostgreSQL fornisce due tipi di dati per supportare la ricerca full-text:

Tipo UUID

Il tipo di dati uuid viene utilizzato per memorizzare l'identificatore universale unico (UUID) definito da RFC 4122, ISO/IEF 9834-8:2005 e standard correlati. (Alcuni sistemi considerano questo tipo di dati come identificatore universale univoco, o GUID.) Questo identificatore è un identificatore di 128 bit generato da un algoritmo, che lo rende impossibile che sia uguale a un altro identificatore generato in modo diverso con lo stesso algoritmo. Pertanto, per i sistemi distribuiti, questo tipo di identificatore fornisce una garanzia di unicità migliore rispetto alle sequenze, poiché le sequenze possono garantire l'unicità solo in un singolo database.

L'UUID viene scritto come una sequenza di numeri esadecimali minuscoli, divisi da punti, specialmente in gruppi di 8 numeri+3 gruppi di 4 numeri+un gruppo di 12 numeri, in totale 32 numeri che rappresentano 128 bit, un esempio standard di UUID è il seguente:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

Tipo XML

Il tipo di dati xml può essere utilizzato per memorizzare dati XML. Il vantaggio di memorizzare dati XML nel tipo di dati testo è che può controllare la buona struttura degli input, e supporta anche la verifica della sicurezza del tipo di funzione. Per utilizzare questo tipo di dati, è necessario utilizzare configure --with-libxml durante la compilazione.

Il tipo xml può memorizzare documenti ben formati definiti dallo standard XML, nonché da XML standard XMLDecl? contenuto Il frammento definito come "contenuto", in sostanza, questo significa che il frammento di contenuto può avere più elementi di livello superiore o nodi di testo. L'espressione xmlvalue IS DOCUMENT può essere utilizzata per determinare se un valore XML specifico è un frammento di contenuto completo o meno.

Creare un valore XML

Usare la funzione xmlparse: per generare valori di tipo XML dai dati di testo:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manuale</title><chapter>...</chapter></book>')
XMLPARSE (CONTENTS 'abc<foo>bar</foo><bar>foo</bar>')

Tipo JSON

Il tipo di dati JSON può essere utilizzato per memorizzare dati JSON (JavaScript Object Notation), tali dati possono anche essere memorizzati come testo, ma il tipo di dati JSON è più vantaggioso per controllare se ogni valore memorizzato è un valore JSON disponibile.

Inoltre ci sono funzioni correlate per gestire i dati JSON:}

Tipo di array

PostgreSQL permette di definire campi come array multidimensionali di lunghezza variabile.

Il tipo dell'array può essere qualsiasi tipo di base o tipo definito dall'utente, tipo enumerativo o tipo complesso.

Dichiarazione dell'array

Quando si crea una tabella, possiamo dichiarare array nel modo seguente:

CREATE TABLE sal_emp (
　　　　name　　　　　　　　　　　text,
　　　　pay_by_quarter　integer[],
　　　　schedule　　　　　　　text[][],
);

pay_by_quarter è un array unidimensionale di interi, schedule è un array bidimensionale di testo.

Possiamo anche utilizzare la parola chiave "ARRAY", come segue:

CREATE TABLE sal_emp (
　　　name text,
　　　pay_by_quarter integer ARRAY[4],
　　　schedule text[][],
);

Valori di inserimento

I valori di inserimento devono essere utilizzati tra parentesi graffe {}, gli elementi devono essere separati da virgole all'interno di {}:

INSERT INTO sal_emp
　　　　VALUES ('Bill',
　　　　'{10000, 10000, 10000, 10000}',
　　　　'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
　　　　VALUES ('Carol',
　　　　'{20000, 25000, 25000, 25000}',
　　　　'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Accesso all'array

Ora possiamo eseguire alcune query su questa tabella.

Prima di tutto, mostriamo come accedere a un elemento dell'array. Questa query recupera i nomi dei dipendenti con variazione del salario nel secondo trimestre:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
　name
-------
　Carol
(1 riga)

Il numero dell'indice dell'array è scritto tra parentesi quadre.

Modifica dell'array

Possiamo modificare i valori dell'array:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
　　　　WHERE name = 'Carol';

或者使用 ARRAY 构造器语法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
　　　　WHERE name = 'Carol';

数组中检索

要搜索一个数组中的数值，你必须检查该数组的每一个值。

比如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[2] = 10000 OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[3] = 10000 OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[4] = 10000;

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

或者，可以使用 generate_subscripts 函数。例如：

SELECT * FROM
　　　(SELECT pay_by_quarter,
　　　　　　　　　　　generate_subscripts(pay_by_quarter, 1) AS s
　　　　　　FROM sal_emp) AS foo
　WHERE pay_by_quarter[s] = 10000;

复合类型

复合类型表示一行或者一条记录的结构； 它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL 允许像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。

声明复合类型

下面是两个定义复合类型的简单实例：

CREATE TYPE complex AS (
　　　　r　　　　　　　double precision,
　　　　i　　　　　　　double precision
);
CREATE TYPE inventory_item AS (
　　　　name　　　　　　　　　　　text,
　　　　supplier_id　　　　　integer,
　　　　price　　　　　　　　　　　numeric
);

语法类似于 CREATE TABLE，只是这里只可以声明字段名字和类型。

定义了类型，我们就可以用它创建表：

CREATE TABLE on_hand (
　　　　item inventory_item
　　　　count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Input di valore di tipo complesso

Per scrivere valori di tipo complesso come costanti di testo, avvolgi i valori dei campi tra parentesi quadre e separali con virgole. Puoi mettere virgolette intorno a qualsiasi valore di campo, se il valore stesso contiene virgole o parentesi, devi metterle tra virgolette.

Il formato generale dei costanti di tipo complesso è il seguente:

'(val1, val2, ...)'

Un esempio è:

'("fuzzy dice",42,1.99)'

Accesso a tipo complesso

Per accedere a un dominio di campo di tipo complesso, scriviamo un punto e il nome del dominio, molto simile a selezionare un campo da un nome di tabella. In realtà, perché è così simile a selezionare un campo da un nome di tabella, spesso dobbiamo usare parentesi quadre per evitare la confusione dell'analizzatore. Ad esempio, potresti dover selezionare alcuni sottodomini dal tavolo di esempio on_hand, come segue:

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

In questo modo non funzionerà perché secondo la sintassi SQL, item è selezionato da un nome di tabella, non da un nome di campo. Dovrai scrivere come segue:

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

O se hai bisogno anche di utilizzare il nome della tabella (ad esempio, in una query multi-tabella), allora scrivi così:

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

Ora, l'oggetto tra parentesi quadre viene correttamente解析为一个 riferimento al campo item, quindi è possibile selezionare sottodominio.

Tipi di intervallo

I tipi di intervallo rappresentano i valori di un tipo di elemento all'interno di un intervallo.

Ad esempio, l'intervallo di timestamp potrebbe essere utilizzato per rappresentare il periodo di prenotazione di una sala riunioni.

I tipi di intervallo integrati in PostgreSQL sono:

• int4range — intervallo di integer

• int8range — intervallo di bigint

• numrange — intervallo numerico

• tsrange — intervallo di timestamp senza fuso orario

• tstzrange — intervallo di timestamp con fuso orario

• daterange — intervallo di date

Inoltre, puoi definire il tuo tipo di intervallo personalizzato.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
　　　　(1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
-- Include
SELECT int4range(10, 20) @> 3;
-- Overlap
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- Estrazione del limite superiore
SELECT upper(int8range(15, 25));
-- Calcolo dell'intersezione
SELECT int4range(10, 20) * int4range(15, 25);
-- Se l'intervallo è vuoto
SELECT isempty(numrange(1, 5));

L'input dei valori di intervallo deve seguire il seguente formato:

(limite inferiore, limite superiore)
(limite inferiore, limite superiore]
[limite inferiore, limite superiore)
[limite inferiore, limite superiore]
Vuoto

Le parentesi tonde o quadre mostrano se i limiti inferiore e superiore sono inclusi o esclusi. Notare che la forma finale è vuoto, che rappresenta un intervallo vuoto (un intervallo che non contiene valori).

-- Include 3, non include 7 e include tutti i punti tra i due
SELECT '[3,7)'::int4range;
-- Non include 3 e 7, ma include tutti i punti tra i due
SELECT '(3,7)'::int4range;
-- Include solo il valore singolo 4
SELECT '[4,4]'::int4range;
-- Non include il punto (normalizzato come 'vuoto')
SELECT '[4,4)'::int4range;

Tipo di identificatore di oggetto

PostgreSQL utilizza internamente l'identificatore di oggetto (OID) come chiave primaria per vari tabelle di sistema.

Inoltre, il sistema non aggiunge un campo OID di sistema alle tabelle create dall'utente (a meno che non venga specificato WITH OIDS o il parametro di configurazione default_with_oids sia impostato su attivato). Il tipo OID rappresenta un identificatore di oggetto. Inoltre, OID ha diversi alias: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig e regdictionary.

Pseudo-tipo

Il sistema di tipi di PostgreSQL contiene una serie di voci a uso speciale, chiamate pseudo-tipi. I pseudo-tipi non possono essere utilizzati come tipo di dati dei campi, ma possono essere utilizzati per dichiarare il tipo di argomento o risultato di una funzione. I pseudo-tipi sono molto utili quando una funzione non accetta e restituisce semplicemente un tipo di dati SQL.

La tabella seguente elenca tutti i tipi pseudo: