En su apariencia externa, nada provoca sospechas. De hecho, incluso parecen familiares y bien conocidos. Pero eso solo hasta que los verifiques. En ese momento, revelarán su naturaleza engañosa, actuando de manera completamente distinta a lo que esperabas. A veces hacen cosas que te ponen los pelos de punta, como perder datos secretos que les han sido confiados. Cuando los enfrentas, afirman no conocerse, aunque en la sombra trabajan arduamente bajo el mismo capó. Ya es hora de llevarlos a la luz. ¡Vamos a desenmascarar a estos tipos sospechosos!
La tipología de datos en PostgreSQL, a pesar de su lógica, a veces presenta sorpresas muy extrañas. En este artículo intentaremos aclarar algunas de sus peculiaridades, entender la razón detrás de su comportamiento extraño y saber cómo evitar problemas en la práctica cotidiana. Para ser sincero, redacté este artículo también como un manual para mí mismo, un manual al que puedo recurrir fácilmente en casos polémicos. Por lo tanto, se irá actualizando a medida que se descubran nuevas sorpresas de estos tipos sospechosos. ¡Así que, adelante, infatigables exploradores de bases de datos!
Dossier número uno. real/double precision/numeric/money
Aparentemente, los tipos numéricos son los menos problemáticos en términos de sorpresas en su comportamiento. Pero nada de eso. Por eso empezaremos con ellos. Así que…
Han olvidado contar
SELECT 0.1::real = 0.1
?column?
boolean
---------
f¿Cuál es el problema? PostgreSQL convierte la constante no tipificada 0.1 al tipo double precision y intenta compararla con 0.1 tipo real. ¡Y estos son valores absolutamente diferentes! Se debe a cómo se representan los números reales en la memoria de la máquina. Dado que 0.1 no puede representarse como una fracción binaria finita (sería 0.0(0011) en binario), los números con diferente precisión serán distintos, de ahí el resultado de que no son iguales. En términos generales, este es un tema para un artículo separado, así que no escribiré más sobre ello aquí.
¿De dónde viene el error?
SELECT double precision(1)
ERROR: syntax error at or near "("
LINE 1: SELECT double precision(1)
^
********** Error **********
ERROR: syntax error at or near "("
SQL state: 42601
Symbol: 24Muchos saben que PostgreSQL permite la conversión funcional de tipos. Es decir, se puede escribir no solo 1::int, sino también int(1), lo cual es equivalente. ¡Pero no para tipos cuyo nombre consta de varias palabras! Así que, si deseas convertir un valor numérico al tipo double precision de manera funcional, utiliza el alias de este tipo float8, es decir, SELECT float8(1).
¿Qué hay más allá de la infinidad?
SELECT 'Infinity'::double precision < 'NaN'::double precision
?column?
boolean
---------
t¡Así que eso es! Resulta que hay algo más grande que la infinidad, ¡y es NaN! Mientras tanto, la documentación de PostgreSQL nos mira con honestidad y afirma que NaN es inherentemente mayor que cualquier otro número y, por lo tanto, que la infinidad. Lo mismo es cierto para -NaN. ¡Hola, amantes del análisis matemático! Pero hay que recordar que todo esto actúa en el contexto de los 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 de la base de datos. Y de nuevo hay que recordar que para los tipos double precision y numeric se aplican diferentes reglas de redondeo. Para numeric, es el redondeo habitual, donde 0,5 se redondea hacia arriba, mientras que para double precision, el redondeo de 0,5 ocurre hacia el entero par más cercano.
El dinero es algo especial
SELECT '10'::money::float8
ERROR: no se puede convertir el tipo money a double precision
LINE 1: SELECT '10'::money::float8
^
********** Error **********
ERROR: no se puede convertir el tipo money a double precision
Estado SQL: 42846
Símbolo: 19Según PostgreSQL, el dinero no es un número real. Según algunos individuos, tampoco. Debemos recordar que la conversión del tipo money solo es posible al tipo numeric, así como el tipo money solo se puede convertir a numeric. Pero a partir de ahí, se puede jugar como se desee. Pero ya no será ese dinero.
Smallint y generación de secuencias
SELECT *
FROM generate_series(1::smallint, 5::smallint, 1::smallint)
ERROR: la función generate_series(smallint, smallint, smallint) no es única
LINE 2: FROM generate_series(1::smallint, 5::smallint, 1::smallint...
^
SUGERENCIA: No se pudo elegir una mejor función candidata. Podrías necesitar agregar conversiones de tipo explícitas.
********** Error **********
ERROR: la función generate_series(smallint, smallint, smallint) no es única
Estado SQL: 42725
Sugerencia: No se pudo elegir una mejor función candidata. Podrías necesitar agregar conversiones de tipo explícitas.
Símbolo: 18A PostgreSQL no le gusta ser tacaño. ¿Qué tipo de secuencias basadas en smallint? ¡int, al menos! Por ello, al intentar ejecutar la consulta anterior, la base de datos intenta convertir smallint a algún otro tipo entero y ve que puede haber múltiples conversiones. ¿Qué conversión elegir? No puede resolver esto y, por lo tanto, falla con un error.
Dossier número dos. "char"/char/varchar/text
También hay una serie de peculiaridades con los tipos de carácter. Conozcámoslas también.
¿Qué trucos son estos?
SELECT 'PETYA'::"char"
, 'PETYA'::"char"::bytea
, 'PETYA'::char
, 'PETYA'::char::bytea
char | bytea | bpchar | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
╨ | xd0 | П | xd09f¿Qué es este tipo "char"? ¿Qué payaso es este? No necesitamos algo así... Porque se hace pasar por un char normal, solo porque está entre comillas. Y se diferencia de un char normal, que está sin comillas, en que solo muestra el primer byte de la representación de la cadena, mientras que un char normal muestra el primer carácter. En nuestro caso, el primer carácter es la letra П, que en la representación unicode ocupa 2 bytes, como lo demuestra la conversión del resultado al tipo bytea. Y el tipo "char" solo toma el primer byte de esa representación unicode. ¿Entonces, para qué se necesita este tipo? La documentación de PostgreSQL dice que es un tipo especial utilizado para necesidades particulares. Así que probablemente no lo necesitaremos. Pero mírale a los ojos y no te equivoques cuando lo encuentres con su comportamiento especial.
Espacios de más. Fuera de la vista, fuera del corazón.
SELECT 'abc '::char(6)::bytea
, 'abc '::char(6)::varchar(6)::bytea
, 'abc '::varchar(6)::bytea
bytea | bytea | bytea
bytea | bytea | bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020Mira el ejemplo proporcionado. Especialmente he convertido todos los resultados al tipo bytea para que sea visualmente claro lo que hay. ¿Dónde están los espacios finales después de la conversión al tipo varchar(6)? La documentación afirma lacónicamente: "Al convertir un valor de carácter a otro tipo de carácter, los espacios adicionales se eliminan". Esta aversión debe ser recordada. Y nota que si la constante de cadena entre comillas se convierte directamente al tipo varchar(6), los espacios finales se mantienen. Así son las maravillas.
Dossier número tres. json/jsonb
JSON es una estructura separada que vive su propia vida. Por ello, sus entidades son un poco diferentes de las entidades de PostgreSQL. Aquí hay algunos ejemplos.
Johnson y Johnson. Siente la diferencia
SELECT 'null'::jsonb IS NULL
?column?
boolean
---------
fTodo se reduce a que JSON tiene su propia entidad null, que no es equivalente a NULL en PostgreSQL. Al mismo tiempo, el propio objeto JSON puede tener un valor NULL, por lo que la expresión SELECT null::jsonb IS NULL (fíjate en la ausencia de comillas simples) devolverá true 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]}Todo se reduce a que json y jsonb son estructuras completamente diferentes. En json, el objeto se almacena tal cual, mientras que en jsonb se almacena como una estructura descompuesta e indexada. Por eso, en el segundo caso, el valor del objeto bajo la clave 1 se reemplazó de [1, 2, 3] a [7, 8, 9], que llegó a la estructura al final con la misma clave.
De la cara no se bebe agua
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 de JSONB cambia el formato de los números reales, llevándolos a su forma clásica. Para el tipo JSON no ocurre eso. Es un poco extraño, pero es su derecho.
Dossier número cuatro. fecha/hora/timestamp
Con los tipos de fecha/hora también hay ciertas peculiaridades. Vamos a verlas. Desde ya aclaro que algunas de las peculiaridades del comportamiento se hacen evidentes si se entiende bien cómo funcionan las zonas horarias. Pero esto también es tema para un artículo aparte.
Yo tuyo no entender
SELECT '08-Jan-99'::date
ERROR: valor de campo de fecha/hora fuera de rango: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
^
HINT: Quizás necesites una configuración de "datestyle" diferente.
********** Error **********
ERROR: valor de campo de fecha/hora fuera de rango: "08-Jan-99"
Estado SQL: 22008
Sugerencia: Quizás necesites una configuración de "datestyle" diferente.
Symbol: 8Aparentemente, ¿qué hay de incomprensible aquí? Pero aún así, la base de datos no entiende qué hemos puesto en primer lugar: ¿el año o el día? Y decide que es enero 99 del año 2008, lo que explota su mente. En general, al pasar fechas en formato de texto, es muy importante verificar cuánto ha reconocido correctamente 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 costar muy caro.
¿De dónde saliste así?
SELECCIONAR '04:05 Europa/Moscú'::hora
ERROR: sintaxis de entrada no válida para el tipo hora: "04:05 Europa/Moscú"
LÍNEA 1: SELECCIONAR '04:05 Europa/Moscú'::hora
^
********** Error **********
ERROR: sintaxis de entrada no válida para el tipo hora: "04:05 Europa/Moscú"
Estado SQL: 22007
Símbolo: 8¿Por qué la base de datos no puede entender la hora especificada? Porque para la zona horaria se indica no una abreviatura, sino el nombre completo, que tiene sentido solo en el contexto de una fecha, ya que tiene en cuenta la historia de los cambios de zonas horarias, y esta historia no funciona sin una fecha. Además, la formulación de la cadena de tiempo plantea preguntas: ¿qué quería realmente decir el programador? Por lo tanto, todo tiene lógica si se analiza.
¿Qué tiene de malo?
Imagínese la situación. Tiene un campo en su tabla con el tipo timestamptz. Quiere indexarlo. Pero entiende que construir un índice sobre este campo no siempre es justificable debido a su alta selectividad (casi todos los valores de este tipo serán únicos). Por lo tanto, decide reducir la selectividad del índice convirtiendo este tipo a fecha. Y obtiene una sorpresa:
CREAR ÍNDICE "iIdent-DateLastUpdate"
EN public."Ident" USANDO btree
(("DTLastUpdate"::fecha));
ERROR: las funciones en la expresión del índice deben marcarse como INMUTABLES
********** Error **********
ERROR: las funciones en la expresión del índice deben marcarse como INMUTABLES
Estado SQL: 42P17¿Cuál es el problema? Que para convertir el tipo timestamptz al tipo fecha se utiliza el valor del parámetro del sistema TimeZone, lo que hace que la función de conversión tipo dependa de un parámetro configurable, es decir, variable (volátil). Dichas funciones no son permitidas en el índice. En este caso, es necesario especificar explícitamente en qué zona horaria se realiza la conversión de tipo.
Cuando now no es realmente now
Estamos acostumbrados a que now() devuelva la fecha/hora actual teniendo en cuenta la zona horaria. Pero observe las siguientes consultas:
INICIAR TRANSACCIÓN;
SELECCIONAR now();
now
timestamp con zona horaria
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECCIONAR now();
now
timestamp con zona horaria
-----------------------------
2019-11-26 13:13:04.271419+03
...
SELECCIONAR now();
now
timestamp con zona horaria
-----------------------------
2019-11-26 13:13:04.271419+03
COMPROMETER;La fecha/hora se devuelve igual independientemente del tiempo transcurrido desde la última consulta. ¿Cuál es el problema? Que now() no es la hora actual, sino el tiempo de inicio de la transacción actual. Por lo tanto, dentro de la transacción no cambia. Cualquier consulta que se ejecute fuera del marco de la transacción se envuelve implícitamente en una transacción, por lo que no notamos que el tiempo que devuelve una simple consulta SELECT now(); en realidad no es el actual... Si deseas obtener la hora actual de manera honesta, debes usar la función clock_timestamp().
Dossier número cinco. bit
Un poco extraño
SELECT '111'::bit(4)
bit
bit(4)
------
1110¿Por qué lado se deben agregar bits al ampliar el tipo? Parece que a la izquierda. Pero la base tiene una opinión diferente sobre esto. Ten cuidado: si el número de bits no coincide al convertir el tipo, obtendrás algo completamente diferente de lo que esperabas. Esto se aplica tanto a la adición de bits a la derecha como al recorte de bits. También a la derecha...
Dossier número seis. Arreglos
Incluso NULL no disparó
SELECT ARRAY[1, 2] || NULL
?column?
integer[]
---------
{1,2}Como personas normales, educadas en SQL, esperamos que el resultado de esta expresión sea NULL. Pero no es así. Se devuelve un arreglo. ¿Por qué? Porque en este caso la base convierte NULL en un arreglo entero y llama implícitamente a la función array_cat. Pero sigue sin estar claro por qué este "gato de arreglo" no anula el arreglo. Este comportamiento también debe recordarse.
En resumen. Hay muchas rarezas. La mayoría de ellas, por supuesto, no son lo suficientemente críticas como para hablar de un comportamiento escandalosamente inadecuado. Otras se explican por la facilidad de uso o la frecuencia de su aplicabilidad en ciertas situaciones. Pero, al mismo tiempo, hay muchas sorpresas. Por lo tanto, es necesario saber sobre ellas. Si encuentras algo más extraño o inusual en el comportamiento de algún tipo, déjalo en los comentarios, con gusto agregaré a los dossiers existentes.
Fuente: habr.com
