PostgreSQL - Tipo de dados

Neste capítulo, discutiremos sobre os tipos de dados usados ​​no PostgreSQL. Ao criar a tabela, para cada coluna, você especifica um tipo de dados, ou seja, que tipo de dados deseja armazenar nos campos da tabela.

Isso permite vários benefícios -

  • Consistency - As operações em colunas do mesmo tipo de dados fornecem resultados consistentes e geralmente são as mais rápidas.

  • Validation - O uso adequado de tipos de dados implica validação de formato de dados e rejeição de dados fora do escopo do tipo de dados.

  • Compactness - Como uma coluna pode armazenar um único tipo de valor, ele é armazenado de forma compacta.

  • Performance- O uso adequado de tipos de dados fornece o armazenamento de dados mais eficiente. Os valores armazenados podem ser processados ​​rapidamente, o que melhora o desempenho.

PostgreSQL oferece suporte a um amplo conjunto de tipos de dados. Além disso, os usuários podem criar seus próprios tipos de dados personalizados usando o comando CREATE TYPE SQL. Existem diferentes categorias de tipos de dados no PostgreSQL. Eles são discutidos abaixo.

Tipos Numéricos

Os tipos numéricos consistem em números inteiros de dois, quatro e oito bytes, números de ponto flutuante de quatro e oito bytes e decimais de precisão selecionável. A tabela a seguir lista os tipos disponíveis.

Nome Tamanho de Armazenamento Descrição Alcance
smallint 2 bytes inteiro de pequeno alcance -32768 a +32767
inteiro 4 bytes escolha típica para inteiro -2147483648 a +2147483647
bigint 8 bytes inteiro de grande alcance -9223372036854775808 a 9223372036854775807
decimal variável precisão especificada pelo usuário, exata até 131072 dígitos antes da vírgula decimal; até 16383 dígitos após o ponto decimal
numérico variável precisão especificada pelo usuário, exata até 131072 dígitos antes da vírgula decimal; até 16383 dígitos após o ponto decimal
real 4 bytes precisão variável, inexata Precisão de 6 dígitos decimais
dupla precisão 8 bytes precisão variável, inexata Precisão de 15 dígitos decimais
pequeno 2 bytes pequeno inteiro de incremento automático 1 a 32767
serial 4 bytes número inteiro autoincrementado 1 a 2147483647
grande série 8 bytes grande número inteiro de incremento automático 1 a 9223372036854775807

Tipos monetários

O tipo de dinheiro armazena uma quantia em moeda com uma precisão fracionária fixa. Os valores dos tipos de dados numéricos, int e bigint podem ser convertidos em dinheiro . O uso de números de ponto flutuante não é recomendado para lidar com dinheiro devido ao potencial de erros de arredondamento.

Nome Tamanho de Armazenamento Descrição Alcance
dinheiro 8 bytes quantidade de moeda -92233720368547758,08 a +92233720368547758,07

Tipos de personagem

A tabela abaixo lista os tipos de caracteres de uso geral disponíveis no PostgreSQL.

S. No. Nome e Descrição
1

character varying(n), varchar(n)

comprimento variável com limite
2

character(n), char(n)

comprimento fixo, acolchoado em branco
3

text

comprimento ilimitado variável

Tipos de dados binários

O tipo de dados bytea permite o armazenamento de cadeias binárias como na tabela abaixo.

Nome Tamanho de Armazenamento Descrição
tchau 1 ou 4 bytes mais a string binária real string binária de comprimento variável

Tipos de data / hora

O PostgreSQL suporta um conjunto completo de tipos de data e hora SQL, conforme mostrado na tabela abaixo. As datas são contadas de acordo com o calendário gregoriano. Aqui, todos os tipos têm resolução de1 microsecond / 14 digits exceto date tipo, cuja resolução é day.

Nome Tamanho de Armazenamento Descrição Baixo valor Valor alto
carimbo de data / hora [(p)] [sem fuso horário] 8 bytes data e hora (sem fuso horário) 4713 AC 294276 DC
TIMESTAMPTZ 8 bytes data e hora, com fuso horário 4713 AC 294276 DC
encontro 4 bytes data (sem hora do dia) 4713 AC 5874897 DC
hora [(p)] [sem fuso horário] 8 bytes hora do dia (sem data) 00:00:00 24:00:00
hora [(p)] com fuso horário 12 bytes somente horas do dia, com fuso horário 00: 00: 00 + 1459 24: 00-1459
intervalo [campos] [(p)] 12 bytes intervalo de tempo -178000000 anos 178000000 anos

Tipo Booleano

PostgreSQL fornece o tipo Booleano SQL padrão. O tipo de dados Booleano pode ter os estados verdadeiro , falso e um terceiro estado, desconhecido , que é representado pelo valor nulo SQL.

Nome Tamanho de Armazenamento Descrição
boleano 1 byte estado de verdadeiro ou falso

Tipo Enumerado

Tipos enumerados (enum) são tipos de dados que compreendem um conjunto de valores estáticos e ordenados. Eles são equivalentes aos tipos de enum suportados em várias linguagens de programação.

Ao contrário de outros tipos, os tipos enumerados precisam ser criados usando o comando CREATE TYPE. Este tipo é usado para armazenar um conjunto de valores estáticos e ordenados. Por exemplo, direções de bússola, ou seja, NORTE, SUL, EAST e OESTE ou dias da semana como mostrado abaixo -

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

Enumerado, uma vez criado, pode ser usado como qualquer outro tipo.

Tipo Geométrico

Os tipos de dados geométricos representam objetos espaciais bidimensionais. O tipo mais fundamental, o ponto, forma a base de todos os outros tipos.

Nome Tamanho de Armazenamento Representação Descrição
ponto 16 bytes Aponte em um avião (x, y)
linha 32 bytes Linha infinita (não totalmente implementada) ((x1, y1), (x2, y2))
lseg 32 bytes Segmento de linha finita ((x1, y1), (x2, y2))
caixa 32 bytes Caixa Retangular ((x1, y1), (x2, y2))
caminho 16 + 16n bytes Caminho fechado (semelhante ao polígono) ((x1, y1), ...)
caminho 16 + 16n bytes Caminho aberto [(x1, y1), ...]
polígono 40 + 16n Polígono (semelhante ao caminho fechado) ((x1, y1), ...)
círculo 24 bytes Círculo <(x, y), r> (ponto central e raio)

Tipo de endereço de rede

O PostgreSQL oferece tipos de dados para armazenar endereços IPv4, IPv6 e MAC. É melhor usar esses tipos em vez de tipos de texto simples para armazenar endereços de rede, porque esses tipos oferecem verificação de erro de entrada e operadores e funções especializados.

Nome Tamanho de Armazenamento Descrição
cidr 7 ou 19 bytes Redes IPv4 e IPv6
inet 7 ou 19 bytes Hosts e redes IPv4 e IPv6
macaddr 6 bytes Endereços MAC

Tipo de seqüência de bits

Os tipos de sequência de bits são usados ​​para armazenar máscaras de bits. Eles são 0 ou 1. Existem dois tipos de bits SQL:bit(n) e bit varying(n), onde n é um número inteiro positivo.

Tipo de pesquisa de texto

Esse tipo oferece suporte à pesquisa de texto completo, que é a atividade de pesquisar uma coleção de documentos em linguagem natural para localizar aqueles que melhor correspondem a uma consulta. Existem dois tipos de dados para isso -

S. No. Nome e Descrição
1

tsvector

Esta é uma lista classificada de palavras distintas que foram normalizadas para mesclar diferentes variantes da mesma palavra, chamadas de "lexemas".
2

tsquery

Isso armazena lexemas que devem ser pesquisados ​​e os combina honrando os operadores booleanos & (AND), | (Ou e ! (NÃO). Os parênteses podem ser usados ​​para forçar o agrupamento dos operadores.

Tipo UUID

Um UUID (Universally Unique Identifiers) é escrito como uma sequência de dígitos hexadecimais minúsculos, em vários grupos separados por hifens, especificamente um grupo de oito dígitos, seguido por três grupos de quatro dígitos, seguido por um grupo de 12 dígitos, para um total de 32 dígitos que representam os 128 bits.

Um exemplo de UUID é - 550e8400-e29b-41d4-a716-446655440000

Tipo XML

O tipo de dados XML pode ser usado para armazenar dados XML. Para armazenar dados XML, primeiro você deve criar valores XML usando a função xmlparse da seguinte forma -

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

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 tem a vantagem de verificar se cada valor armazenado é um valor JSON válido. Também estão disponíveis funções de suporte relacionadas, que podem ser usadas diretamente para lidar com o tipo de dados JSON da seguinte maneira.

Exemplo Resultado de exemplo
array_to_json ('{{1,5}, {99,100}}' :: int []) [[1,5], [99,100]]
row_to_json (linha (1, 'foo')) {"f1": 1, "f2": "foo"}

Tipo de Matriz

O PostgreSQL oferece a oportunidade de definir uma coluna de uma tabela como um array multidimensional de comprimento variável. Matrizes de qualquer tipo de base, tipo enum ou tipo composto integrado ou definido pelo usuário podem ser criadas.

Declaração de Matrizes

O tipo de matriz pode ser declarado como

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

ou usando a palavra-chave "ARRAY" como

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

Inserindo valores

Os valores da matriz podem ser inseridos como uma constante literal, colocando os valores dos elementos entre chaves e separando-os por vírgulas. Um exemplo é mostrado abaixo -

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

Acessando matrizes

Um exemplo para acessar Arrays é mostrado abaixo. O comando dado abaixo selecionará as pessoas cujas economias são maiores no segundo trimestre do que no quarto trimestre.

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Modificando Arrays

Um exemplo de modificação de matrizes é mostrado abaixo.

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

ou usando a sintaxe de expressão ARRAY -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Pesquisando matrizes

Um exemplo de pesquisa de matrizes é mostrado abaixo.

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 o tamanho da matriz for conhecido, o método de pesquisa fornecido acima pode ser usado. Caso contrário, o exemplo a seguir mostra como pesquisar quando o tamanho não é conhecido.

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

Tipos Compostos

Este tipo representa uma lista de nomes de campos e seus tipos de dados, ou seja, estrutura de uma linha ou registro de uma tabela.

Declaração de Tipos Compostos

O exemplo a seguir mostra como declarar um tipo composto

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

Este tipo de dados pode ser usado nas tabelas de criação conforme abaixo -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Entrada de valor composto

Os valores compostos podem ser inseridos como uma constante literal, colocando os valores do campo entre parênteses e separando-os por vírgulas. Um exemplo é mostrado abaixo -

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

Isso é válido para o inventário_item definido acima. A palavra-chave ROW é opcional, desde que você tenha mais de um campo na expressão.

Acessando Tipos Compostos

Para acessar um campo de uma coluna composta, use um ponto seguido do nome do campo, da mesma forma que selecionar um campo em um nome de tabela. Por exemplo, para selecionar alguns subcampos de nossa tabela de exemplo on_hand, a consulta seria como mostrado abaixo -

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

Você também pode usar o nome da tabela (por exemplo, em uma consulta multitables), como este -

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

Tipos de faixa

Tipos de intervalo representam tipos de dados que usam um intervalo de dados. O tipo de intervalo pode ser intervalos discretos (por exemplo, todos os valores inteiros de 1 a 10) ou intervalos contínuos (por exemplo, qualquer ponto no tempo entre 10h00 e 11h00).

Os tipos de intervalos integrados disponíveis incluem os seguintes intervalos -

  • int4range - Intervalo de inteiro

  • int8range - Alcance de bigint

  • numrange - Variação numérica

  • tsrange - Intervalo de carimbo de data / hora sem fuso horário

  • tstzrange - Intervalo de carimbo de data / hora com fuso horário

  • daterange - Intervalo de data

Os tipos de intervalos personalizados podem ser criados para disponibilizar novos tipos de intervalos, como intervalos de endereços IP usando o tipo inet como base, ou intervalos flutuantes usando o tipo de dados flutuante como base.

Os tipos de intervalo oferecem suporte a limites de intervalo inclusivos e exclusivos usando os caracteres [] e (), respectivamente. Por exemplo, '[4,9)' representa todos os inteiros começando de e incluindo 4 até, mas não incluindo 9.

Tipos de identificador de objeto

Identificadores de objeto (OIDs) são usados ​​internamente pelo PostgreSQL como chaves primárias para várias tabelas de sistema. Se WITH OIDS for especificado ou a variável de configuração default_with_oids estiver habilitada, somente então, em tais casos, OIDs são adicionados às tabelas criadas pelo usuário. A tabela a seguir lista vários tipos de alias. Os tipos de alias OID não têm operações próprias, exceto para rotinas de entrada e saída especializadas.

Nome Referências Descrição Exemplo de valor
oid qualquer identificador numérico de objeto 564182
regproc pg_proc nome da função soma
procedimento normativo pg_proc função com tipos de argumento soma (int4)
regoper pg_operator nome do operador +
regoperador pg_operator operador com tipos de argumento * (inteiro, inteiro) ou - (NENHUM, inteiro)
regclass pg_class nome da relação pg_type
regtype pg_type nome do tipo de dados inteiro
regconfig pg_ts_config configuração de pesquisa de texto Inglês
regdictionary pg_ts_dict dicionário de pesquisa de texto simples

Pseudo tipos

O sistema de tipos do PostgreSQL contém várias entradas de propósito especial que são chamadas coletivamente de pseudo-tipos. Um pseudo-tipo não pode ser usado como um tipo de dados de coluna, mas pode ser usado para declarar o argumento de uma função ou tipo de resultado.

A tabela abaixo lista os pseudo-tipos existentes.

S. No. Nome e Descrição
1

any

Indica que uma função aceita qualquer tipo de dado de entrada.
2

anyelement

Indica que uma função aceita qualquer tipo de dados.
3

anyarray

Indica que uma função aceita qualquer tipo de dados de matriz.
4

anynonarray

Indica que uma função aceita qualquer tipo de dados que não seja de matriz.
5

anyenum

Indica que uma função aceita qualquer tipo de dados enum.
6

anyrange

Indica que uma função aceita qualquer tipo de dado de intervalo.
7

cstring

Indica que uma função aceita ou retorna uma string C terminada em nulo.
8

internal

Indica que uma função aceita ou retorna um tipo de dados interno do servidor.
9

language_handler

Um manipulador de chamadas de linguagem procedural é declarado para retornar language_handler.
10

fdw_handler

Um manipulador de wrapper de dados externos é declarado para retornar fdw_handler.
11

record

Identifica uma função que retorna um tipo de linha não especificado.
12

trigger

Uma função de gatilho é declarada para retornar o gatilho.
13

void

Indica que uma função não retorna nenhum valor.