Não há nada de suspeito em sua aparência. Além disso, eles até parecem familiares para você há muito tempo. Mas isso é só até você verificá-los. É aqui que eles mostram sua natureza insidiosa, funcionando de maneira completamente diferente do que você esperava. E às vezes eles fazem algo que deixa seus cabelos em pé - por exemplo, eles perdem dados secretos que lhes foram confiados. Quando você os confronta, eles afirmam que não se conhecem, embora nas sombras trabalhem duro sob o mesmo capuz. É hora de finalmente trazê-los para a água potável. Vamos também lidar com esses tipos suspeitos.
A digitação de dados no PostgreSQL, apesar de toda a sua lógica, às vezes apresenta surpresas muito estranhas. Neste artigo tentaremos esclarecer algumas de suas peculiaridades, entender o motivo de seu comportamento estranho e entender como não ter problemas na prática cotidiana. Para falar a verdade, compilei este artigo também como uma espécie de livro de referência para mim mesmo, um livro de referência que poderia ser facilmente consultado em casos controversos. Portanto, ele será reabastecido à medida que novas surpresas de tipos suspeitos forem descobertas. Então, vamos lá, ó rastreadores de banco de dados incansáveis!
Dossiê número um. real/precisão dupla/numérico/dinheiro
Parece que os tipos numéricos são os menos problemáticos em termos de surpresas no comportamento. Mas não importa como seja. Então vamos começar com eles. Então…
Esqueci como contar
SELECT 0.1::real = 0.1
?column?
boolean
---------
fQual é o problema? O problema é que o PostgreSQL converte a constante não digitada 0.1 em precisão dupla e tenta compará-la com 0.1 do tipo real. E esses são significados completamente diferentes! A ideia é representar números reais na memória da máquina. Como 0.1 não pode ser representado como uma fração binária finita (seria 0.0(0011) em binário), números com profundidades de bits diferentes serão diferentes, daí o resultado de que não são iguais. De modo geral, este é um tópico para um artigo separado, não escreverei mais detalhes aqui.
De onde vem o erro?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Muitas pessoas sabem que o PostgreSQL permite notação funcional para conversão de tipos. Ou seja, você pode escrever não apenas 1::int, mas também int(1), que será equivalente. Mas não para tipos cujos nomes consistem em várias palavras! Portanto, se você deseja converter um valor numérico para o tipo de precisão dupla na forma funcional, use o alias deste tipo float8, ou seja, SELECT float8(1).
O que é maior que o infinito?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
tVeja como é! Acontece que existe algo maior que o infinito e é NaN! Ao mesmo tempo, a documentação do PostgreSQL nos olha com olhos honestos e afirma que NaN é obviamente maior que qualquer outro número e, portanto, infinito. O oposto também é verdadeiro para -NaN. Olá, amantes da matemática! Mas devemos lembrar que tudo isto funciona no contexto de números reais.
Arredondamento dos olhos
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Outra saudação inesperada da base. Novamente, lembre-se de que a precisão dupla e os tipos numéricos têm efeitos de arredondamento diferentes. Para numérico - o usual, quando 0,5 é arredondado para cima, e para precisão dupla - 0,5 é arredondado para o número inteiro par mais próximo.
Dinheiro é algo especial
SELECT '10'::money::float8
ERROR: cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19De acordo com o PostgreSQL, o dinheiro não é um número real. De acordo com algumas pessoas também. Precisamos lembrar que a conversão do tipo dinheiro só é possível para o tipo numérico, assim como somente o tipo numérico pode ser convertido para o tipo dinheiro. Mas agora você pode brincar com isso como seu coração desejar. Mas não será o mesmo dinheiro.
Smallint e geração de sequência
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERROR: function generate_series(smallint, smallint, smallint) is not unique
LINE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
HINT: Could not choose a best candidate function. You might need to add explicit type casts.
********** Ошибка **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-состояние: 42725
Подсказка: Could not choose a best candidate function. You might need to add explicit type casts.
Символ: 18O PostgreSQL não gosta de perder tempo com ninharias. Quais são essas sequências baseadas em smallint? int, nada menos! Portanto, ao tentar executar a consulta acima, o banco de dados tenta converter smallint para algum outro tipo inteiro e vê que pode haver várias conversões desse tipo. Qual elenco escolher? Ela não consegue decidir isso e, portanto, cai com um erro.
Arquivo número dois. "char"/char/varchar/texto
Uma série de estranhezas também estão presentes nos tipos de caracteres. Vamos conhecê-los também.
Que tipo de truques são esses?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09fQue tipo de “char” é esse, que tipo de palhaço é esse? Não precisamos deles... Porque ele finge ser um caractere comum, mesmo estando entre aspas. E difere de um char normal, que não tem aspas, pois gera apenas o primeiro byte da representação da string, enquanto um char normal gera o primeiro caractere. No nosso caso, o primeiro caractere é a letra P, que na representação unicode ocupa 2 bytes, como evidenciado pela conversão do resultado para o tipo bytea. E o tipo “char” ocupa apenas o primeiro byte desta representação unicode. Então por que esse tipo é necessário? A documentação do PostgreSQL diz que este é um tipo especial usado para necessidades especiais. Portanto, é improvável que precisemos disso. Mas olhe nos olhos dele e você não se enganará ao conhecê-lo com seu comportamento especial.
Espaços extras. fora da vista, longe 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 | x616263202020Dê uma olhada no exemplo dado. Converti especialmente todos os resultados para o tipo bytea, para que ficasse claramente visível o que estava lá. Onde estão os espaços finais após a conversão para varchar(6)? A documentação afirma sucintamente: "Ao converter o valor do caractere para outro tipo de caractere, o espaço em branco à direita é descartado." Essa antipatia deve ser lembrada. E observe que se uma constante de string entre aspas for convertida diretamente no tipo varchar(6), os espaços finais serão preservados. Esses são os milagres.
Arquivo número três. json/jsonb
JSON é uma estrutura separada que vive sua própria vida. Portanto, suas entidades e as do PostgreSQL são ligeiramente diferentes. Aqui estão alguns exemplos.
Johnson e Johnson. sinta a diferença
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fO problema é que JSON tem sua própria entidade nula, que não é análoga a NULL no PostgreSQL. Ao mesmo tempo, o próprio objeto JSON pode muito bem ter o valor NULL, então a expressão SELECT null::jsonb IS NULL (observe a ausência de aspas simples) retornará true desta vez.
Uma carta 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 problema é que json e jsonb são estruturas completamente diferentes. Em json, o objeto é armazenado como está, e em jsonb ele já está armazenado na forma de uma estrutura indexada e analisada. É por isso que no segundo caso o valor do objeto pela chave 1 foi substituído de [1, 2, 3] para [7, 8, 9], que entrou na estrutura bem no final com a mesma chave.
Não beba á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 em sua implementação JSONB altera a formatação dos números reais, trazendo-os para a forma clássica. Isso não acontece para o tipo JSON. Um pouco estranho, mas ele está certo.
Arquivo número quatro. data/hora/carimbo de data/hora
Existem também algumas peculiaridades com os tipos de data/hora. Vamos dar uma olhada neles. Deixe-me fazer uma reserva desde já que algumas características comportamentais ficam claras se você entender bem a essência de trabalhar com fusos horários. Mas este também é um tópico para um artigo separado.
O meu não entendo o seu
SELECT '08-Jan-99'::date
ERROR: date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Perhaps you need a different "datestyle" setting.
********** Ошибка **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-состояние: 22008
Подсказка: Perhaps you need a different "datestyle" setting.
Символ: 8Parece que o que é incompreensível aqui? Mas o banco de dados ainda não entende o que colocamos aqui em primeiro lugar – o ano ou o dia? E ela decide que é 99 de janeiro de 2008, o que a deixa boquiaberta. De modo geral, ao transmitir datas em formato de texto, é necessário verificar com muito cuidado o quão corretamente o banco de dados as reconheceu (em particular, analisar o parâmetro datestyle com o comando SHOW datestyle), pois ambigüidades neste assunto podem custar muito caro.
De onde você veio?
SELECT '04:05 Europe/Moscow'::time
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
^
********** Ошибка **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL-состояние: 22007
Символ: 8Por que o banco de dados não consegue entender o tempo especificado explicitamente? Porque o fuso horário não tem abreviatura, mas sim um nome completo, o que só faz sentido no contexto de uma data, pois leva em consideração o histórico de mudanças de fuso horário, e não funciona sem data. E o próprio texto da linha do tempo levanta questões - o que o programador realmente quis dizer? Portanto, tudo é lógico aqui, se você olhar bem.
O que há de errado com ele?
Imagine a situação. Você tem um campo na sua tabela com o tipo timestamptz. Você deseja indexá-lo. Mas você entende que nem sempre se justifica construir um índice neste campo devido à sua alta seletividade (quase todos os valores desse tipo serão únicos). Então você decide reduzir a seletividade do índice convertendo o tipo em uma data. E você tem uma surpresa:
CREATE INDEX "iIdent-DateLastUpdate"
ON public."Ident" USING btree
(("DTLastUpdate"::date));
ERROR: functions in index expression must be marked IMMUTABLE
********** Ошибка **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL-состояние: 42P17Qual é o problema? O fato é que para converter um tipo timestamptz em um tipo de data, é 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 customizado, ou seja, volátil. Tais funções não são permitidas no índice. Neste caso, você deve indicar explicitamente em qual fuso horário a conversão de tipo é realizada.
Quando agora nem é agora
Estamos acostumados a now() retornar a data/hora atual, levando em consideração o fuso horário. Mas observe as seguintes consultas:
START TRANSACTION;
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECT now();
now
timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03
COMMIT;A data/hora é retornada da mesma forma, não importa quanto tempo tenha passado desde a solicitação anterior! Qual é o problema? O fato é que now() não é a hora atual, mas a hora de início da transação atual. Portanto, não muda dentro da transação. Qualquer consulta lançada fora do escopo de uma transação é envolvida implicitamente em uma transação, e é por isso que não percebemos que o tempo retornado por um simples SELECT now(); na verdade, não o atual... Se você deseja obter um horário atual honesto, você precisa usar a função clock_timestamp().
Arquivo número cinco. pedaço
Estranho um pouco
SELECT '111'::bit(4)
bit
bit(4)
------
1110De que lado os bits devem ser adicionados em caso de extensão de tipo? Parece estar à esquerda. Mas só a base tem uma opinião diferente sobre o assunto. Cuidado: se o número de dígitos não corresponder ao lançar um tipo, você não conseguirá o que deseja. Isso se aplica tanto à adição de bits à direita quanto ao corte de bits. Também à direita...
Arquivo número seis. Matrizes
Mesmo NULL não disparou
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Como pessoas normais criadas em SQL, esperamos que o resultado desta expressão seja NULL. Mas não estava lá. Uma matriz é retornada. Por que? Porque neste caso a base converte NULL em um array inteiro e chama implicitamente a função array_cat. Mas ainda não está claro por que esse “array cat” não redefine o array. Esse comportamento também precisa ser lembrado.
Resumir. Existem muitas coisas estranhas. A maioria deles, é claro, não é tão crítica a ponto de falar sobre comportamento flagrantemente inapropriado. E outros são explicados pela facilidade de uso ou pela frequência de sua aplicabilidade em determinadas situações. Mas, ao mesmo tempo, há muitas surpresas. Portanto, você precisa saber sobre eles. Se você encontrar algo mais estranho ou incomum no comportamento de qualquer tipo, escreva nos comentários, terei o maior prazer em acrescentar aos dossiês disponíveis sobre eles.
Fonte: habr.com