No hay nada sospechoso en su apariencia. Además, incluso te resultan familiares y desde hace mucho tiempo. Pero eso es sólo hasta que los revises. Aquí es donde muestran su naturaleza insidiosa, trabajando de manera completamente diferente a lo que esperabas. Y a veces hacen algo que te pone los pelos de punta: por ejemplo, pierden datos secretos que se les han confiado. Cuando los confrontas, afirman que no se conocen, aunque en las sombras trabajan duro bajo el mismo capó. Es hora de llevarlos finalmente al agua limpia. Tratemos también con estos tipos sospechosos.
La tipificación de datos en PostgreSQL, a pesar de toda su lógica, a veces presenta sorpresas muy extrañas. En este artículo intentaremos aclarar algunas de sus peculiaridades, comprender el motivo de su extraño comportamiento y entender cómo no tener problemas en la práctica diaria. A decir verdad, he compilado este artículo también como una especie de libro de referencia para mí, un libro de referencia al que podría consultarse fácilmente en casos controvertidos. Por lo tanto, se repondrá a medida que se descubran nuevas sorpresas de tipos sospechosos. Entonces, ¡vamos, oh incansables rastreadores de bases de datos!
Expediente número uno. real/doble precisión/numérico/dinero
Parecería que los tipos numéricos son los menos problemáticos en términos de sorpresas en el comportamiento. Pero no importa cómo sea. Entonces comencemos con ellos. Entonces…
Olvidé cómo contar
SELECT 0.1::real = 0.1
?column?
boolean
---------
f¿Qué pasa? El problema es que PostgreSQL convierte la constante sin tipo 0.1 a doble precisión e intenta compararla con 0.1 de tipo real. ¡Y estos son significados completamente diferentes! La idea es representar números reales en la memoria de la máquina. Dado que 0.1 no se puede representar como una fracción binaria finita (sería 0.0(0011) en binario), los números con diferentes dígitos serán diferentes, de ahí el resultado de que no son iguales. En términos generales, este es un tema para un artículo aparte, no escribiré con más detalle aquí.
¿De dónde viene el error?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24Mucha gente sabe que PostgreSQL permite la notación funcional para la conversión de tipos. Es decir, puedes escribir no sólo 1::int, sino también int(1), que será equivalente. ¡Pero no para tipos cuyos nombres constan de varias palabras! Por lo tanto, si desea convertir un valor numérico a un tipo de precisión doble en forma funcional, utilice el alias de este tipo float8, es decir, SELECT float8(1).
¿Qué es más grande que el infinito?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
t¡Mira cómo es! Resulta que hay algo más grande que el infinito, ¡y es NaN! Al mismo tiempo, la documentación de PostgreSQL nos mira con ojos honestos y afirma que NaN es obviamente mayor que cualquier otro número y, por tanto, infinito. Lo contrario también es cierto para -NaN. ¡Hola amantes de las matemáticas! Pero debemos recordar que todo esto opera en el contexto de números reales.
Redondeo de ojos
SELECT round('2.5'::double precision)
, round('2.5'::numeric)
round | round
double precision | numeric
-----------------+---------
2 | 3Otro saludo inesperado desde la base. Nuevamente, recuerde que los tipos numéricos y de doble precisión tienen diferentes efectos de redondeo. Para numérico, el habitual, cuando se redondea 0,5 hacia arriba, y para doble precisión, 0,5 se redondea hacia el entero par más cercano.
El dinero es 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
Символ: 19Según PostgreSQL, el dinero no es un número real. Según algunas personas también. Necesitamos recordar que solo es posible convertir el tipo de dinero al tipo numérico, del mismo modo que solo se puede convertir el tipo numérico al tipo de dinero. Pero ahora puedes jugar con él como desees. Pero no será el mismo dinero.
Smallint y generación de 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.
Символ: 18A PostgreSQL no le gusta perder el tiempo en nimiedades. ¿Cuáles son estas secuencias basadas en Smallint? entero, ¡nada menos! Por lo tanto, al intentar ejecutar la consulta anterior, la base de datos intenta convertir Smallint a algún otro tipo de entero y ve que puede haber varias conversiones de este tipo. ¿Qué elenco elegir? No puede decidir esto y por lo tanto falla con un error.
Expediente número dos. "char"/char/varchar/texto
También están presentes una serie de rarezas en los tipos de personajes. Conozcámoslos también.
¿Qué clase de trucos son estos?
SELECT 'ПЕТЯ'::"char"
, 'ПЕТЯ'::"char"::bytea
, 'ПЕТЯ'::char
, 'ПЕТЯ'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09f¿Qué tipo de "char" es este, qué clase de payaso es este? No los necesitamos... Porque pretende ser un carácter común y corriente, aunque esté entre comillas. Y se diferencia de un char normal, que no tiene comillas, en que genera solo el primer byte de la representación de cadena, mientras que un char normal genera el primer carácter. En nuestro caso, el primer carácter es la letra P, que en la representación Unicode ocupa 2 bytes, como lo demuestra la conversión del resultado al tipo bytea. Y el tipo “char” toma sólo el primer byte de esta representación Unicode. Entonces, ¿por qué se necesita este tipo? La documentación de PostgreSQL dice que este es un tipo especial que se utiliza para necesidades especiales. Por eso es poco probable que lo necesitemos. Pero míralo a los ojos y no te equivocarás cuando lo conozcas con su comportamiento especial.
Espacios adicionales. Fuera de la vista, fuera de la mente
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Eche un vistazo al ejemplo dado. Convertí especialmente todos los resultados al tipo bytea, para que fuera claramente visible lo que había allí. ¿Dónde están los espacios finales después de convertir a varchar(6)? La documentación dice sucintamente: "Al convertir el valor de un carácter a otro tipo de carácter, se descartan los espacios en blanco finales". Hay que recordar este disgusto. Y tenga en cuenta que si una constante de cadena entrecomillada se convierte directamente al tipo varchar(6), los espacios finales se conservan. Así son los milagros.
Expediente número tres. json/jsonb
JSON es una estructura separada que vive su propia vida. Por tanto, sus entidades y las de PostgreSQL son ligeramente diferentes. Aquí hay ejemplos.
Johnson y Johnson. Siente la diferencia
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fEl caso es que JSON tiene su propia entidad nula, que no es análoga a NULL en PostgreSQL. Al mismo tiempo, el objeto JSON en sí puede tener el valor NULL, por lo que la expresión SELECT null::jsonb IS NULL (tenga en cuenta la ausencia de comillas simples) devolverá verdadero esta vez.
Una letra lo 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]}El caso es que json y jsonb son estructuras completamente diferentes. En json, el objeto se almacena tal cual, y en jsonb ya está almacenado en forma de una estructura indexada y analizada. Es por eso que en el segundo caso, el valor del objeto por la clave 1 fue reemplazado de [1, 2, 3] a [7, 8, 9], que entró en la estructura al final con la misma clave.
No bebas agua de tu cara
SELECT '{"reading": 1.230e-5}'::jsonb
, '{"reading": 1.230e-5}'::json
jsonb | json
jsonb | json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}PostgreSQL en su implementación JSONB cambia el formato de los números reales, llevándolos a la forma clásica. Esto no sucede con el tipo JSON. Un poco extraño, pero tiene razón.
Expediente número cuatro. fecha/hora/marca de tiempo
También hay algunas rarezas con los tipos de fecha/hora. Mirémoslos. Permítanme hacer una reserva de inmediato: algunas de las características de comportamiento quedan claras si comprende bien la esencia de trabajar con zonas horarias. Pero este también es un tema para un artículo aparte.
Mi no entender el tuyo
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.
Символ: 8Parecería que ¿qué es incomprensible aquí? Pero la base de datos todavía no entiende qué pusimos en primer lugar aquí: ¿el año o el día? Y decide que es enero del 99 de 2008, lo que la deja boquiabierta. En términos generales, al transmitir fechas en formato de texto, es necesario verificar con mucho cuidado qué tan correctamente las reconoció la base de datos (en particular, analizar el parámetro datestyle con el comando SHOW datestyle), ya que las ambigüedades en este asunto pueden resultar muy costosas.
De donde eres
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 qué la base de datos no puede comprender la hora especificada explícitamente? Porque la zona horaria no tiene abreviatura, sino nombre completo, lo que tiene sentido sólo en el contexto de una fecha, ya que tiene en cuenta el historial de cambios de zona horaria y no funciona sin fecha. Y la propia redacción de la línea de tiempo plantea preguntas: ¿qué quiso decir realmente el programador? Por tanto, aquí todo es lógico, si nos fijamos.
¿Lo que está mal con él?
Imagínese la situación. Tienes un campo en tu tabla con tipo timestamptz. Quieres indexarlo. Pero comprende que crear un índice en este campo no siempre está justificado debido a su alta selectividad (casi todos los valores de este tipo serán únicos). Entonces decide reducir la selectividad del índice convirtiendo el tipo en una fecha. Y te llevas una 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¿Qué pasa? El hecho es que para convertir un tipo de marca de tiempo a un tipo de fecha, se utiliza el valor del parámetro del sistema TimeZone, lo que hace que la función de conversión de tipo dependa de un parámetro personalizado, es decir volátil. Este tipo de funciones no están permitidas en el índice. En este caso, debe indicar explícitamente en qué zona horaria se realiza la conversión de tipos.
Cuando el ahora ni siquiera es ahora en absoluto
Estamos acostumbrados a que now() devuelva la fecha/hora actual, teniendo en cuenta la zona horaria. Pero mire las siguientes 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;¡La fecha/hora se devuelve igual sin importar cuánto tiempo haya pasado desde la solicitud anterior! ¿Qué pasa? El hecho es que now() no es la hora actual, sino la hora de inicio de la transacción actual. Por lo tanto, no cambia dentro de la transacción. Cualquier consulta lanzada fuera del alcance de una transacción está implícitamente envuelta en una transacción, por lo que no notamos que el tiempo devuelto por un simple SELECT now(); de hecho, no la actual... Si desea obtener una hora actual honesta, debe usar la función clock_timestamp().
Expediente número cinco. poco
un poco extraño
SELECT '111'::bit(4)
bit
bit(4)
------
1110¿De qué lado se deben agregar los bits en caso de extensión de tipo? Parece ser de izquierdas. Pero sólo la base tiene una opinión diferente al respecto. Tenga cuidado: si el número de dígitos no coincide al convertir un tipo, no obtendrá lo que desea. Esto se aplica tanto a la adición de bits a la derecha como al recorte de bits. También a la derecha...
Expediente número seis. matrices
Incluso NULL no se disparó
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Como gente normal criada en SQL, esperamos que el resultado de esta expresión sea NULL. Pero no estaba ahí. Se devuelve una matriz. ¿Por qué? Porque en este caso la base convierte NULL en una matriz de números enteros e implícitamente llama a la función array_cat. Pero aún no está claro por qué este "gato de matriz" no restablece la matriz. Este comportamiento también es necesario recordarlo.
Resumir. Hay muchas cosas extrañas. La mayoría de ellos, por supuesto, no son tan críticos como para hablar de comportamientos manifiestamente inapropiados. Y otros se explican por la facilidad de uso o la frecuencia de su aplicabilidad en determinadas situaciones. Pero al mismo tiempo hay muchas sorpresas. Por lo tanto, es necesario conocerlos. Si encuentra algo más extraño o inusual en el comportamiento de cualquier tipo, escriba en los comentarios, estaré encantado de añadirlo a los expedientes disponibles sobre ellos.
Fuente: habr.com