Em sua aparência externa, nada levanta suspeitas. Além disso, elas parecem claras e conhecidas. Mas isso é apenas até que você as verifique. É aqui que elas revelam sua verdadeira natureza traiçoeira, agindo de maneira totalmente diferente do que você esperava. Às vezes, provocam situações tão alarmantes que fazem seus cabelos se arrepiar — por exemplo, perdendo dados secretos em quem confiaram. Quando você os confronta, afirmam que não se conhecem, embora nos bastidores trabalhem arduamente sob o mesmo teto. É hora de expô-los. Vamos, então, investigar esses tipos suspeitos.
A tipificação de dados no PostgreSQL, apesar de sua lógica, realmente apresenta surpresas estranhas. Neste artigo, tentaremos esclarecer algumas de suas peculiaridades, entender a causa de seu comportamento esquisito e descobrir como evitar problemas no dia a dia. Para ser sincero, redigi este artigo também como um guia para mim mesmo, um recurso que eu pudesse consultar facilmente em casos de dúvida. Portanto, ele será atualizado à medida que novas surpresas de tipos suspeitos forem descobertas. Vamos lá, incansáveis exploradores de bancos de dados!
Dossiê número um. real/double precision/numeric/money
À primeira vista, os tipos numéricos parecem ser os menos problemáticos em termos de surpresas no comportamento. Mas, de jeito nenhum. Vamos começar por eles. Então...
Desaprenderam a contar
SELECT 0.1::real = 0.1
?column?
boolean
---------
fQual é o problema? É que o PostgreSQL converte a constante não tipificada 0.1 para o tipo double precision e tenta compará-la com 0.1 do tipo real. E esses são valores completamente diferentes! A questão está na representação de números reais na memória do computador. Como 0.1 não pode ser representado como uma fração binária finita (seria 0.0(0011) na forma binária), números com diferentes precisões serão diferentes, resultando na conclusão de que não são iguais. Na verdade, este é um tópico para um artigo separado, não entrarei em mais detalhes aqui.
De onde vem o erro?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Erro **********
ERROR: syntax error at or near "("
Estado SQL: 42601
Símbolo: 24Muitas pessoas sabem que o PostgreSQL permite a gravação funcional de conversões de tipos. Ou seja, você pode escrever não apenas 1::int, mas também int(1), que será equivalente. Mas isso não vale para tipos cujo nome consiste em várias palavras! Portanto, se você quiser converter um valor numérico para o tipo double precision de maneira funcional, use o alias desse tipo float8, ou seja, SELECT float8(1).
O que é maior que a infinidade?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tOlha só! Parece que existe algo maior que a infinidade, e isso é NaN! Ao mesmo tempo, a documentação do PostgreSQL nos observa com um olhar sincero e afirma que NaN é intrinsecamente maior que qualquer outro número e, portanto, maior que a infinidade. O mesmo se aplica a -NaN. Olá, amantes da análise matemática! Mas devemos lembrar que tudo isso se dá no contexto dos números reais.
Arredondando olhos
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Mais uma saudação inesperada do banco de dados. E novamente temos que lembrar que para os tipos double precision e numeric, regras de arredondamento diferentes se aplicam. Para numeric, o arredondamento é o habitual, onde 0,5 é arredondado para cima, enquanto para double precision, o arredondamento de 0,5 é feito para o inteiro par mais próximo.
Dinheiro é algo especial
SELECT '10'::money::float8
ERROR: não é possível converter o tipo money para double precision
LINE 1: SELECT '10'::money::float8
^
********** Erro **********
ERROR: não é possível converter o tipo money para double precision
SQL estado: 42846
Símbolo: 19Segundo o PostgreSQL, dinheiro não é um número real. Na opinião de algumas pessoas, também não é. Devemos lembrar que a conversão do tipo money só é possível para o tipo numeric, assim como o tipo money só pode ser convertido para o tipo numeric. E com ele, já podemos brincar como quisermos. Mas isso já não será mais aquele dinheiro.
Smallint e geração de sequências
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERROR: função generate_series(smallint, smallint, smallint) não é única
LINE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
DICA: Não foi possível escolher uma melhor função candidata. Você pode precisar adicionar conversões de tipo explícitas.
********** Erro **********
ERROR: função generate_series(smallint, smallint, smallint) não é única
SQL estado: 42725
Dica: Não foi possível escolher uma melhor função candidata. Você pode precisar adicionar conversões de tipo explícitas.
Símbolo: 18O PostgreSQL não gosta de ser modesto. Que tipo de sequências baseadas em smallint? int, no mínimo! Portanto, ao tentar executar a consulta acima, o banco de dados tenta converter smallint para algum outro tipo numérico e percebe que pode haver várias conversões. Qual conversão escolher? Ele não consegue decidir isso, e por isso gera um erro.
Dossiê número dois. «char»/char/varchar/text
Há uma série de peculiaridades também nos tipos de caractere. Vamos conhecê-los.
Que truques são esses?
SELECT 'PETYA'::"char"
, 'PETYA'::"char"::bytea
, 'PETYA'::char
, 'PETYA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fQue tipo é esse «char», que palhaço é esse? Não precisamos dele... Porque ele se disfarça de um char comum, mesmo estando entre aspas. E a diferença para o char comum, que está sem aspas, é que ele exibe apenas o primeiro byte da representação da string, enquanto o char normal exibe o primeiro caractere. No nosso caso, o primeiro caractere é a letra П, que ocupa 2 bytes na representação Unicode, conforme evidenciado pela conversão do resultado para o tipo bytea. E o tipo «char» captura apenas o primeiro byte dessa representação Unicode. Então, para que esse tipo é necessário? A documentação do PostgreSQL afirma que é um tipo especial, usado para necessidades específicas. Portanto, provavelmente não precisaremos dele. Mas olhe em seus olhos e não cometa erros ao encontrá-lo com seu comportamento peculiar.
Espaços desnecessários. Fora de vista, fora da mente.
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Olhe para o exemplo apresentado. Eu intencionalmente converti todos os resultados para o tipo bytea, para que ficasse claro o que está armazenado. Onde estão os espaços em branco após a conversão para o tipo varchar(6)? A documentação afirma de forma sucinta: «Na conversão de um valor de caractere para outro tipo de caractere, os espaços adicionais são descartados». Essa aversão deve ser lembrada. E note que se a constante de string entre aspas é imediatamente convertida para o tipo varchar(6), os espaços finais são mantidos. Essas são as surpresas.
Dossiê número três. json/jsonb
JSON é uma estrutura distinta, que vive sua própria vida. Portanto, suas entidades e as entidades do PostgreSQL diferem um pouco. Aqui estão alguns exemplos.
Johnson & Johnson. Sinta a diferença
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fO fato é que o JSON tem sua própria entidade null, que não é equivalente a NULL no PostgreSQL. Ao mesmo tempo, o objeto JSON pode realmente ter um valor NULL, portanto, a expressão SELECT null::jsonb IS NULL (note a ausência de aspas simples) desta vez retornará true.
Uma letra muda tudo
SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json
json
json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}
---
SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb
jsonb
jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}O fato é que json e jsonb são estruturas completamente diferentes. No json, o objeto é armazenado como está, enquanto no jsonb ele é armazenado como uma estrutura analisada e indexada. É por isso que, no segundo caso, o valor do objeto para a chave 1 foi substituído de [1, 2, 3] para [7, 8, 9], que chegou à estrutura por último com a mesma chave.
Não se deve beber água do rosto
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}O PostgreSQL, na implementação do JSONB, altera a formatação de números de ponto flutuante, levando-os ao formato clássico. Para o tipo JSON, isso não acontece. Estranho um pouco, mas é seu direito.
Dossiê número quatro. date/time/timestamp
Com os tipos de data/hora também há algumas estranhezas. Vamos analisá-las. Deixe-me esclarecer que algumas das peculiaridades do comportamento se tornam compreensíveis se você entender bem como funciona o trabalho com fusos horários. Mas isso também é um tema para um artigo separado.
Meu você não entende
SELECT '08-Jan-99'::date
ERROR: valor do campo de data/hora fora do intervalo: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Talvez você precise de uma configuração de "datestyle" diferente.
********** Erro **********
ERROR: valor do campo de data/hora fora do intervalo: "08-Jan-99"
Estado SQL: 22008
Dica: Talvez você precise de uma configuração de "datestyle" diferente.
Símbolo: 8Parece que não há nada confuso aqui? Mas mesmo assim, o banco não entende o que colocamos em primeiro lugar — o ano ou o dia? E decide que isso é 99 de janeiro de 2008, o que explode sua lógica. De fato, ao passar datas em formato de texto, é preciso verificar cuidadosamente o quão corretamente o banco as reconheceu (particularmente, analisando o parâmetro datestyle com o comando SHOW datestyle), uma vez que ambiguidades nesse questionamento podem custar muito caro.
De onde você surgiu?
SELECIONE '04:05 Europa/Moscovo'::time
ERRO: sintaxe de entrada inválida para o tipo time: "04:05 Europa/Moscovo"
LINHA 1: SELECIONE '04:05 Europa/Moscovo'::time
^
********** Erro **********
ERRO: sintaxe de entrada inválida para o tipo time: "04:05 Europa/Moscovo"
Estado SQL: 22007
Símbolo: 8Por que o banco de dados não consegue entender o horário especificado? Porque para o fuso horário foi indicada uma nomenclatura completa em vez da abreviação, que só faz sentido no contexto de uma data, uma vez que considera a história das mudanças de fusos horários, e isso não funciona sem uma data. E a própria formulação da string de tempo levanta questões — o que exatamente o programador quis dizer? Portanto, tudo faz sentido, se você analisar.
O que ele tem de errado?
Imagine a situação. Você tem um campo na tabela do tipo timestamptz. Você quer indexá-lo. Mas entende que construir um índice por esse campo nem sempre é justificável devido à sua alta seletividade (praticamente todos os valores desse tipo serão únicos). Portanto, você decide reduzir a seletividade do índice, convertendo esse tipo para data. E recebe uma surpresa:
CRIE ÍNDICE "iIdent-DateLastUpdate"
NA public."Ident" USANDO btree
(("DTLastUpdate"::date));
ERRO: funções na expressão do índice devem ser marcadas como IMUTÁVEIS
********** Erro **********
ERRO: funções na expressão do índice devem ser marcadas como IMUTÁVEIS
Estado SQL: 42P17Qual é o problema? O problema é que para converter o tipo timestamptz para o tipo date, é utilizado o valor do parâmetro do sistema TimeZone, o que torna a função de conversão de tipo dependente de um parâmetro configurável, ou seja, mutável (volatile). Essas funções não são permitidas em índices. Nesse caso, é necessário especificar explicitamente qual fuso horário está sendo usado para a conversão de tipo.
Quando agora não é realmente agora
Estamos acostumados a que now() retorne a data/hora atual considerando o fuso horário. Mas olhe os seguintes comandos:
INICIAR TRANSAÇÃO;
SELECIONE now();
agora
timestamp com fuso horário
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECIONE now();
agora
timestamp com fuso horário
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECIONE now();
agora
timestamp com fuso horário
-----------------------------
2019-11-26 13:13:04.271419+03
COMITAR;A data/hora retornam sempre as mesmas, não importa quanto tempo tenha passado desde a última solicitação! Qual é o problema? O fato é que now() não é o horário atual, mas sim o horário de início da transação atual. Portanto, durante a transação, ele não muda. Qualquer solicitação iniciada fora do contexto da transação é implicada em uma transação, por isso não percebemos que o horário retornado pela simples solicitação SELECT now(); na verdade não é atual... Se você quiser saber o horário atual, deve usar a função clock_timestamp().
Dossiê número cinco. bit
Um pouco estranho
SELECT '111'::bit(4)
bit
bit(4)
------
1110De que lado devem ser adicionados os bits em caso de expansão do tipo? Parece que à esquerda. Mas o banco de dados tem uma opinião diferente sobre isso. Tenha cuidado: ao desconsiderar a quantidade de dígitos durante a conversão de tipos, você obterá algo totalmente diferente do que queria. Isso se aplica tanto à adição de bits à direita quanto ao corte de bits. Também à direita...
Dossiê número seis. Arrays
Nem mesmo NULL falhou
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Como pessoas normais, educadas em SQL, esperamos que o resultado desta expressão seja NULL. Mas não é esse o caso. Um array é retornado. Por quê? Porque, neste caso, o banco converte NULL em um array inteiro e chama implicitamente a função array_cat. Mas continua sendo confuso por que este 'gato array' não anula o array. Esse comportamento deve ser apenas memorável.
Em resumo. Há muitas estranhezas. A maioria, é claro, não é tão crítica a ponto de se falar de comportamentos absurdamente inadequados. Outros são explicados pela conveniência ou frequência de uso em determinadas situações. No entanto, também há muitas surpresas. Portanto, é necessário conhecê-las. Se você encontrar algo mais estranho ou incomum no comportamento de algum tipo, escreva nos comentários, terei prazer em adicionar ao dossiê existente.
Fonte: habr.com
