🥇Tipos sospeitosos

Non hai nada sospeitoso sobre a súa aparencia. Ademais, ata che parecen familiares ben e durante moito tempo. Pero iso só ata que os comprobe. Aquí é onde mostran a súa natureza insidiosa, traballando de forma completamente diferente do que esperabas. E ás veces fan algo que pon o pelo de punta; por exemplo, perden os datos secretos que se lles confiaron. Cando os enfrontas, afirman que non se coñecen, aínda que na sombra traballan con dilixencia baixo o mesmo capó. Por fin é hora de levalos á auga limpa. Imos tamén tratar con estes tipos sospeitosos.

A dixitación de datos en PostgreSQL, con toda a súa lóxica, presenta ás veces sorpresas moi estrañas. Neste artigo trataremos de aclarar algunhas das súas peculiaridades, comprender o motivo do seu estraño comportamento e comprender como non atopar problemas na práctica cotiá. A verdade, recompilei este artigo tamén como unha especie de libro de consulta para min, un libro de consulta ao que se podería facer referencia facilmente en casos polémicos. Polo tanto, repoñerase a medida que se descubran novas sorpresas de tipos sospeitosos. Entón, imos, oh rastreadores de bases de datos incansables!

Dossier número un. real/dobre precisión/numérico/diñeiro

Parece que os tipos numéricos son os menos problemáticos en canto a sorpresas no comportamento. Pero non importa como sexa. Entón, imos comezar con eles. Entón…

Esquecín como contar

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

Que pasa? O problema é que PostgreSQL converte a constante non tipificada 0.1 en dobre precisión e tenta comparala con 0.1 de tipo real. E estes son significados completamente diferentes! A idea é representar números reais na memoria da máquina. Dado que 0.1 non se pode representar como unha fracción binaria finita (sería 0.0(0011) en binario), os números con diferentes profundidades de bit serán diferentes, polo que o resultado é que non son iguais. En xeral, este é un tema para un artigo separado; non vou escribir con máis detalle aquí.

De onde vén 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
Символ: 24

Moita xente sabe que PostgreSQL permite a notación funcional para a conversión de tipos. É dicir, pode escribir non só 1::int, senón tamén int(1), que será equivalente. Pero non para tipos cuxos nomes consisten en varias palabras! Polo tanto, se quere emitir un valor numérico para dobre tipo de precisión en forma funcional, use o alias deste tipo float8, é dicir, SELECT float8(1).

Que é máis grande que o infinito?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

Mira como é! Resulta que hai algo máis grande que o infinito, e é NaN! Ao mesmo tempo, a documentación de PostgreSQL míranos con ollos honestos e afirma que NaN é obviamente maior que calquera outro número e, polo tanto, infinito. O contrario tamén é certo para -NaN. Ola, amantes das matemáticas! Pero debemos lembrar que todo isto opera no contexto dos números reais.

Redondeo de ollos

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

Outro saúdo inesperado da base. De novo, recorda que os tipos de precisión dobre e numéricos teñen efectos de redondeo diferentes. Para numéricos - o método habitual, cando 0,5 se redondea cara arriba, e para a precisión dobre - 0,5 é redondeado cara ao número enteiro par máis próximo.

O diñeiro é 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
Символ: 19

Segundo PostgreSQL, o diñeiro non é un número real. Segundo algúns individuos, tamén. Temos que lembrar que o tipo de diñeiro só é posible para o tipo numérico, do mesmo xeito que só o tipo numérico se pode enviar ao tipo de diñeiro. Pero agora podes xogar con el como queira o teu corazón. Pero non serán os mesmos cartos.

Xeración de pequenos e secuencias

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.
Символ: 18

A PostgreSQL non lle gusta perder o tempo en bagatelas. Cales son estas secuencias baseadas en smallint? int, nada menos! Polo tanto, ao tentar executar a consulta anterior, a base de datos tenta lanzar smallint a outro tipo de número enteiro e ve que pode haber varios deste tipo. Que reparto escoller? Non pode decidir isto e, polo tanto, falla cun erro.

Expediente número dous. "char"/char/varchar/text

Unha serie de rarezas tamén están presentes nos tipos de personaxes. Imos coñecelos tamén.

Que tipo de trucos son estes?

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

Que tipo de "char" é este, que tipo de pallaso é este? Non necesitamos esas... Porque pretende ser un char común, aínda que estea entre comiñas. E diferénciase dun carácter normal, que está sen comiñas, en que saca só o primeiro byte da representación da cadea, mentres que un carácter normal produce o primeiro carácter. No noso caso, o primeiro carácter é a letra P, que na representación Unicode ocupa 2 bytes, como se demostra ao converter o resultado ao tipo bytea. E o tipo "char" só toma o primeiro byte desta representación Unicode. Entón, por que é necesario este tipo? A documentación de PostgreSQL di que este é un tipo especial usado para necesidades especiais. Polo que é pouco probable que o necesitemos. Pero míralle aos ollos e non te equivocarás cando o atopes co seu comportamento especial.

Espazos extra. Fóra da vista, fóra 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 | x616263202020

Bótalle un ollo ao exemplo dado. Convertín especialmente todos os resultados ao tipo bytea para que fose claramente visible o que había. Onde están os espazos ao final despois de lanzar a varchar(6)? A documentación indica de forma sucinta: "Ao enviar o valor do carácter a outro tipo de carácter, descartarase o espazo en branco ao final". Este desgusto hai que lembralo. E teña en conta que se unha constante de cadea entre comiñas se lanza directamente ao tipo varchar(6), os espazos ao final consérvanse. Tales son os milagres.

Expediente número tres. json/jsonb

JSON é unha estrutura separada que vive a súa propia vida. Polo tanto, as súas entidades e as de PostgreSQL son lixeiramente diferentes. Aquí tes exemplos.

Johnson e Johnson. sentir a diferenza

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

O caso é que JSON ten a súa propia entidade nula, que non é o análogo de NULL en PostgreSQL. Ao mesmo tempo, o propio obxecto JSON pode ter o valor NULL, polo que a expresión SELECT null::jsonb IS NULL (teña en conta a ausencia de comiñas simples) devolverá verdadeiro nesta ocasión.

Unha letra cambia todo

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 caso é que json e jsonb son estruturas completamente diferentes. En json, o obxecto gárdase tal e como está, e en jsonb xa está almacenado en forma de estrutura indexada analizada. É por iso que no segundo caso, o valor do obxecto pola clave 1 foi substituído de [1, 2, 3] a [7, 8, 9], que entrou na estrutura ao final coa mesma clave.

Non bebas auga da túa cara

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL na súa implementación JSONB cambia o formato dos números reais, achegándoos á forma clásica. Isto non ocorre co tipo JSON. Un pouco raro, pero ten razón.

Expediente número catro. data/hora/hora

Tamén hai algunhas rarezas cos tipos de data/hora. Mirámolos. Permíteme facer unha reserva de inmediato para que algunhas das características do comportamento queden claras se entendes ben a esencia de traballar con fusos horarios. Pero este tamén é un tema para un artigo separado.

Os meus teus non entenden

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.
Символ: 8

Parecería que aquí é incomprensible? Pero a base de datos aínda non comprende o que poñemos aquí en primeiro lugar: o ano ou o día? E ela decide que é o 99 de xaneiro de 2008, o que lle sorprende. En xeral, cando se transmiten datas en formato de texto, cómpre comprobar con moito coidado o correcto recoñecemento das bases de datos (en particular, analizar o parámetro datestyle co comando SHOW datestyle), xa que as ambigüidades neste asunto poden ser moi caras.

De onde conseguiches isto?

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
Символ: 8

Por que a base de datos non pode comprender o tempo especificado explícitamente? Porque a zona horaria non ten unha abreviatura, senón un nome completo, o que só ten sentido no contexto dunha data, xa que ten en conta o historial de cambios de fuso horario e non funciona sen data. E a propia redacción da liña do tempo suscita preguntas: que quería dicir realmente o programador? Polo tanto, todo é lóxico aquí, se o miras.

Que lle pasa?

Imaxina a situación. Tes un campo na túa táboa co tipo timestamptz. Queres indexalo. Pero entendes que a construción dun índice neste campo non sempre se xustifica debido á súa alta selectividade (case todos os valores deste tipo serán únicos). Así que decides reducir a selectividade do índice escribindo o tipo a unha data. E tes unha sorpresa:

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-состояние: 42P17

Que pasa? O caso é que para emitir un tipo timestamptz a un tipo de data, utilízase o valor do parámetro do sistema TimeZone, o que fai que a función de conversión de tipo dependa dun parámetro personalizado, é dicir. volátil. Este tipo de funcións non están permitidas no índice. Neste caso, debes indicar explícitamente en que zona horaria se realiza o tipo de emisión.

Cando agora non é nin agora en absoluto

Estamos afeitos a agora() devolver a data/hora actual, tendo en conta a zona horaria. Pero mira 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 devólvese igual sen importar canto tempo pasou desde a solicitude anterior. Que pasa? O feito é que agora () non é a hora actual, senón a hora de inicio da transacción actual. Polo tanto, non cambia dentro da transacción. Calquera consulta lanzada fóra do ámbito dunha transacción está envolta nunha transacción de forma implícita, polo que non observamos que o tempo devolto por un simple SELECT now(); de feito, non a actual... Se queres obter unha hora actual honesta, cómpre usar a función clock_timestamp().

Expediente número cinco. pouco

Estraño un pouco

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

De que lado deberían engadirse os bits en caso de extensión de tipo? Parece que está á esquerda. Pero só a base ten unha opinión diferente sobre este asunto. Teña coidado: se o número de díxitos non coincide ao emitir un tipo, non obterá o que quería. Isto aplícase tanto para engadir bits á dereita como para recortar bits. Tamén á dereita...

Expediente número seis. Arrays

Nin sequera NULL disparou

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

Como persoas normais criadas en SQL, esperamos que o resultado desta expresión sexa NULL. Pero non estaba alí. Devólvese unha matriz. Por que? Porque neste caso a base converte NULL nunha matriz de enteiros e chama implícitamente á función array_cat. Pero aínda non está claro por que este "gato de matriz" non restablece a matriz. Tamén hai que lembrar este comportamento.

Resume. Hai moitas cousas estrañas. A maioría deles, por suposto, non son tan críticos como para falar de comportamentos descaradamente inadecuados. E outros explícanse pola facilidade de uso ou pola frecuencia da súa aplicabilidade en determinadas situacións. Pero ao mesmo tempo, hai moitas sorpresas. Polo tanto, cómpre saber sobre eles. Se atopas algo máis estraño ou inusual no comportamento de calquera tipo, escribe nos comentarios, estarei encantado de engadir aos expedientes dispoñibles neles.

Fonte: www.habr.com

Tipos sospeitosos