Tipos de Dados do PostgreSQL

Nesta seção, discutiremos os tipos de dados do PostgreSQL, que são definidos para cada campo ao criar uma tabela.

Os benefícios de definir o tipo de dados:

PostgreSQL oferece uma variedade rica de tipos de dados. Os usuários podem usar o comando CREATE TYPE para criar novos tipos de dados no banco de dados. Existem muitos tipos de dados no PostgreSQL, e vamos explicar detalhadamente a seguir.

Tipos de dados numéricos

Os tipos de dados numéricos são compostos por 2 bytes,4 ou 8 inteiro em bytes e 4 ou 8 Número de ponto flutuante de bytes e número decimal opcional de precisão

A tabela a seguir lista os tipos de dados numéricos disponíveis.

Tipo monetário

O tipo money armazena quantias monetárias com precisão fixa.

Os valores dos tipos numeric, int e bigint podem ser convertidos para money, não é recomendável usar números de ponto flutuante para lidar com tipos monetários, pois há a possibilidade de erro de arredondamento.

Tipos de caractere

A tabela a seguir lista os tipos de caractere suportados pelo PostgreSQL:

Data/Tipos de dados de tempo

A tabela a seguir lista os tipos de dados de data e hora suportados pelo PostgreSQL.

Tipo booleano

PostgreSQL suporta o tipo de dados booleano padrão.

boolean tem dois estados: "true" (verdadeiro) ou "false" (falso), e um terceiro estado "unknown" (desconhecido), representado por NULL.

Tipos de enum

Os tipos de enum são um tipo de dados que contém um conjunto ordenado de valores estáticos e valores.

Os tipos de enum no PostgreSQL são semelhantes aos tipos enum do linguagem C.

Diferente dos outros tipos, os tipos de enum precisam ser criados usando o comando CREATE TYPE.

CREATE TYPE mood AS ENUM ('triste', 'bem', 'feliz');

Criação de dias da semana, conforme mostrado a seguir:

CREATE TYPE semana AS ENUM ('Seg', 'Ter', 'Qua', 'Qui', 'Sex', 'Sáb', 'Dom');

Como outros tipos, os tipos de enum podem ser usados em definições de tabelas e funções após sua criação.

CREATE TYPE mood AS ENUM ('triste', 'bem', 'feliz');
CREATE TABLE person (
　　　　nome text,
　　　　current_mood mood
);
INSERT INTO person VALUES ('Moe', 'feliz');
SELECT　*　FROM person WHERE current_mood = 'feliz';
　nome | current_mood　
------+--------------
　Moe | feliz
(1　row)

Tipos geométricos

Os tipos de dados geométricos representam objetos planos bidimensionais.

A tabela a seguir lista os tipos de dados geométricos suportados pelo PostgreSQL.

O tipo mais básico: ponto. É a base para outros tipos.

Tipos de dados de endereço de rede

PostgreSQL oferece tipos de dados para armazenar IPv4 、IPv6 、MAC Address data type.

É melhor usar esses tipos de dados para armazenar endereços de rede do que usar tipos de texto puros, porque esses tipos fornecem verificação de erros de entrada e operações e funções especiais.

Ao ordenar dados do tipo inet ou cidr, o IPv4 O endereço sempre vem antes do IPv6 Antes do endereço, incluindo aqueles encapsulados ou mapeados em IPv6 Endereço IPv4 endereço, por exemplo::10.2.3.4 ou ::ffff:10.4.3.2.

tipo de sequência de bits

uma sequência de bits 1 e a string de 0. Elas podem ser usadas para armazenar e visualizar máscaras de bits. Existem dois tipos de bits SQL: bit(n) e bit varying(n), onde n é um inteiro positivo.

Os dados do tipo bit devem coincidir exatamente com o comprimento n, tentativas de armazenar dados mais curtos ou mais longos são incorretas. O tipo de dados bit varying é o tipo variável mais longo de até n; sequências mais longas serão rejeitadas. Escrever um bit sem comprimento é equivalente a bit(1) significa que não há limite de comprimento para bit varying.

tipo de busca de texto

busca full-text é encontrar documentos que correspondem a uma consulta em um conjunto de documentos naturais de linguagem.

PostgreSQL oferece dois tipos de dados para suportar busca full-text:

Tipo UUID

o tipo de dados uuid é usado para armazenar RFC 4122, ISO/IEF 9834-8:2005 e os padrões de identificação únicos gerais (UUID) definidos. (Alguns sistemas consideram esse tipo de dados como identificador global único, ou GUID.) Este identificador é gerado por algoritmo 128 identificador de bits, tornando impossível que ele seja idêntico a outro identificador gerado de maneira semelhante em módulos que usam a mesma algoritmo. Portanto, para sistemas distribuídos, esse tipo de identificador oferece uma garantia de unicidade melhor do que uma sequência, que só pode garantir unicidade em um único banco de dados.

UUID é escrito como uma sequência de dígitos hexadecimais minúsculos, dividido em grupos por caracteres de separação, especialmente um grupo8dígitos+3conjuntos4dígitos+um conjunto12dígitos, totalizando 32 números representam 128 bits, um exemplo de UUID dessa padrão é o seguinte:

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

Tipo XML

Os tipos de dados xml podem ser usados para armazenar dados XML. A vantagem de armazenar dados XML no tipo text é que ele pode verificar a estrutura bem formatada dos valores de entrada e ainda suporta verificações de segurança de tipo de função. Para usar este tipo de dados, é necessário usar configure na compilação. --com-libxml.

xml pode armazenar documentos bem formatados definidos pelo padrão XML, bem como por Decl? content O fragmento definido como "conteúdo", aproximadamente, isso significa que o fragmento de conteúdo pode ter múltiplos elementos de nível superior ou nós de caractere. A expressão xmlvalue IS DOCUMENT pode ser usada para determinar se um valor xml específico é um arquivo completo ou um fragmento de conteúdo. xmlvalue IS DOCUMENT}}

Criar valor XML

Usar a função xmlparse: para gerar valores do tipo XML a partir de dados de caractere:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><título>Manual</título><capítulo>...<//capítulo></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

Tipo JSON

O tipo de dados JSON pode ser usado para armazenar dados JSON (JavaScript Object Notation), esses dados também podem ser armazenados como texto, mas o tipo de dados JSON é mais vantajoso para verificar se cada valor armazenado é um valor JSON disponível.

Existem também funções relacionadas para manipular dados JSON:

Tipo de array

PostgreSQL permite definir campos como arrays multidimensionais de comprimento variável.

O tipo de array pode ser qualquer tipo básico ou tipo definido pelo usuário, tipo enumerativo ou tipo composto.

Declaração de array

Quando criamos uma tabela, podemos declarar arrays da seguinte forma:

CREATE TABLE sal_emp (
　　　　name text,
　　　　pagamento_por_trimestre integer[],
　　　　texto_horario text[][]
);

pagamento_por_trimestre é um array unidimensional de inteiros, schedule é um array bidimensional de texto.

Também podemos usar a palavra-chave "ARRAY", conforme mostrado a seguir:

CREATE TABLE sal_emp (
　　　nome text,
　　　pagamento_por_trimestre integer ARRAY[4],
　　　texto_horario[][]
);

Valores de inserção

Os valores de inserção usam chaves { } e os elementos dentro { } são separados por vírgula:

INSERT INTO sal_emp
　　　　VALUES ('Bill',
　　　　'{10000,　10000,　10000,　10000}',
　　　　'{{"reunião", "almoço"}, {{"treinamento", "apresentação"}}});
INSERT INTO sal_emp
　　　　VALUES　('Carol',
　　　　'{20000,　25000,　25000,　25000}',
　　　　'{{"breakfast",　"consulting"},　{"meeting",　"lunch"}}');

访问数组

现在我们可以在这个表上运行一些查询。

首先，我们演示如何访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名：

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

数组的下标数字是写在方括弧内的。

修改数组

我们可以对数组的值进行修改：

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
);

A sintaxe é semelhante a CREATE TABLE, mas aqui apenas podemos declarar nomes de campos e tipos.

Definimos o tipo, podemos usá-lo para criar tabelas:

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

Entrada de valor de tipo composto

Para escrever valores de tipo composto como constantes de texto, envolva os valores de campo em parênteses e separe-os com vírgulas. Você pode colocar aspas duplas em volta de qualquer valor de campo, se o valor contiver vírgulas ou parênteses, você deve usá-las.

O formato geral de constante de tipo composto é o seguinte:

' ( val1　, val2　, ... )'

Um exemplo é:

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

Acesso a tipo composto

Para acessar um domínio de campo de tipo composto, escrevemos um ponto seguido do nome do domínio, muito semelhante a selecting um campo de um nome de tabela. Na verdade, porque é tão semelhante a selecionar um campo de um nome de tabela, frequentemente precisamos usar parênteses para evitar confusão do analisador. Por exemplo, você pode precisar selecionar alguns subdomínios da tabela de instância on_hand, assim:

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

Isso não funcionará, porque, conforme a sintaxe do SQL, item é selecionado a partir de um nome de tabela, e não de um nome de campo. Você deve escrever assim:

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

Ou se você também precisar usar nomes de tabelas (por exemplo, em uma consulta de várias tabelas), escreva assim:

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

Agora, o objeto de parênteses é corretamente analisado como uma referência ao campo item, permitindo a seleção de subdomínios.

Tipo de intervalo

O tipo de dados de intervalo representa os valores de um tipo de elemento em um determinado intervalo.

Por exemplo, o intervalo de timestamp pode ser usado para representar o período de tempo em que uma sala de conferências foi reservada.

Os tipos de intervalo integrados do PostgreSQL são:

• int4range — intervalo de integer

• int8range — intervalo de bigint

• numrange — intervalo numérico

• tsrange — intervalo de timestamp sem fuso horário

• tstzrange — intervalo de timestamp com fuso horário

• daterange — intervalo de data

Além disso, você pode definir seu próprio tipo de intervalo.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
　　　　(1108'[2010-01-01　14:30,　2010-01-01　15:30)');
--　contém
SELECT int4range(10,　20) @>　3;
--　superposição
SELECT numrange(11.1,　22.2) && numrange(20.0,　30.0);
--　extrair borda superior
SELECT upper(int8range(15,　25));
--　calcular interseção
SELECT int4range(10,　20)　*　int4range(15,　25);
--　se o intervalo é vazio
SELECT isempty(numrange(1,　5));

A entrada de valores de intervalo deve seguir o formato a seguir:

(borda inferior, borda superior)
(borda inferior, borda superior]
[borda inferior, borda superior)
[borda inferior, borda superior]
vazio

Os parênteses ou colchetes mostram se a borda inferior e superior é ou não incluída. Observe que o formato final é vazio, representando um intervalo vazio (um intervalo sem valores).

--　inclui3não inclui7e inclui todos os pontos entre eles
SELECT '[3,7)::int4range;
--　não inclui3e7mas inclui todos os pontos entre eles
SELECT '('3,7)::int4range;
--　apenas inclui um único valor4
SELECT '[4,4]'::int4range;
--　não inclui pontos (padronizados como ‘vazio’)
SELECT '[4,4)::int4range;

Tipo de identificador de objeto

O PostgreSQL usa internamente o identificador de objeto (OID) como chave primária de várias tabelas do sistema.

Além disso, o sistema não adicionará um campo OID de sistema às tabelas criadas pelo usuário (a menos que seja declarado WITH OIDS no momento da criação da tabela ou a configuração do parâmetro default_with_oids esteja configurada para ativa). O tipo oid representa um identificador de objeto. Além disso, oid tem várias aliases: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig e regdictionary.

Tipos pseudónimos

O sistema de tipos do PostgreSQL contém uma série de entradas de uso especial, que são chamadas de tipos pseudónimos de acordo com a categoria. Os tipos pseudónimos não podem ser usados como tipos de dados de campo, mas podem ser usados para declarar os tipos de argumento ou resultado de uma função. Os tipos pseudónimos são úteis quando uma função não aceita e não retorna simplesmente um tipo de dados SQL.

A tabela a seguir lista todos os tipos pseudónimos: