El tipo de datos no coincide en las fechas al analizar un json, dentro de una declaración INSERT

Resuelto Oleksandr Zakharchenko asked hace 9 meses • 0 respuestas

A continuación se muestra un pequeño código para analizar una matriz json en una tabla en una base de datos:

CREATE TEMPORARY TABLE json_import (doc json) ON COMMIT DROP;

INSERT INTO json_import (doc) VALUES(
    '[
        {
            "cli_nk": 1,
            "effective_from_date": "2021-01-18",
            "effective_to_date": "",
            "user_dml_type_code": "U",
            "user_fix_desc": "qwert"
        },
        {
            "cli_nk": 2,
            "effective_from_date": "2023-03-25",
            "effective_to_date": "",
            "user_dml_type_code": "I",
            "user_fix_desc": "qwe"
        },
        {
            "cli_nk": 4,
            "effective_from_date": "2015-07-18",
            "effective_to_date": "2999-12-31",
            "user_dml_type_code": "D",
            "user_fix_desc": "ytrewq"
        },
        {
            "cli_nk": 5,
            "effective_from_date": "2016-08-03",
            "effective_to_date": "2999-12-31",
            "user_dml_type_code": "U",
            "user_fix_desc": "zxc"
        }
    ]'
);

INSERT INTO user_type (cli_nk, effective_from_date, effective_to_date, user_dml_type_code, user_fix_desc)
SELECT
    NULLIF (p.cli_nk, ''),
    NULLIF (p.effective_from_date, ''),
    NULLIF (p.effective_to_date, ''),
    NULLIF (p.user_dml_type_code, ''),
    NULLIF (p.user_fix_desc, '')
FROM json_import l
    CROSS JOIN LATERAL json_populate_recordset(NULL::user_type, doc) AS p;

DDL de mi tabla user_type:

CREATE TABLE public.user_type (
    cli_nk text NULL,
    effective_from_date date NULL,
    effective_to_date date NULL,
    user_dml_type_code bpchar(1) NOT NULL,
    user_fix_desc text NULL
);

Pero después de intentar ejecutarlo, aparece el siguiente error:

Error de SQL [22007]: ERROR: sintaxis no válida para el tipo de fecha: "" Posición: 180

¿Cómo puedo manejar este error y llenar la tabla correctamente?

Feb 16 '24 22:02 Oleksandr Zakharchenko

Aceptado

Su problema está relacionado con el hecho de que algunas de las fechas contenidas en su json no están especificadas (son iguales a la cadena vacía). Cuando Postgres intenta convertir la cadena vacía al tipo de datos DATE durante la instrucción INSERT, no sabe qué hacer con ella, porque la cadena vacía no es un valor nulo.

Una forma de solucionar este problema es definir un tipo de datos temporal, que pueda adquirir cadenas vacías como cadenas y luego convertir los tipos de datos sin procesar en los tipos de datos de la tabla.

CREATE TYPE raw_data AS (
    cli_nk               INTEGER, 
    effective_from_date  TEXT,
    effective_to_date    TEXT,
    user_dml_type_code   TEXT,
    user_fix_desc        TEXT
);

INSERT INTO user_type 
SELECT cli_nk,
       CAST(CASE WHEN NOT effective_from_date = '' THEN effective_from_date END AS DATE),
       CAST(CASE WHEN NOT effective_to_date = '' THEN effective_to_date END AS DATE),
       user_dml_type_code,
       user_fix_desc
FROM json_import l
CROSS JOIN LATERAL JSON_POPULATE_RECORDSET(NULL::raw_data, l.doc) AS p;

Producción :

tintinar	fecha_efectiva_desde_fecha	efectivo_hasta_la fecha	código_tipo_dml_usuario	usuario_fix_desc
1	2021-01-18T00:00:00.000Z	nulo	Ud.	qwert
2	2023-03-25T00:00:00.000Z	nulo	I	qwe
4	2015-07-18T00:00:00.000Z	2999-12-31T00:00:00.000Z	D	ytrewq
5	2016-08-03T00:00:00.000Z	2999-12-31T00:00:00.000Z	Ud.	zxc

Consulte la demostración aquí .

Feb 16 '2024 15:02 lemon