Data type mismatch on dates while parsing a json, within an INSERT statement

Question

Below is a small code of parsing a json array into a table in a database:

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 of my user_type table:

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
);

But after I try to execute it, I get the following error:

SQL Error [22007]: ERROR: invalid syntax for date type: "" Position: 180

How can I handle this error and fill the table correctly?

Answer 1

NULLIF returns the datatype that is used as input, in your case TEXT. You must cast the result from NULLIF to the desired data type, in your case a DATE:

INSERT INTO user_type (cli_nk, effective_from_date, effective_to_date, user_dml_type_code, user_fix_desc)
    SELECT
        NULLIF (p.cli_nk, ''),
        CAST(NULLIF (p.effective_from_date, '') AS date),
        CAST(NULLIF (p.effective_to_date, '') AS 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;

Answer 2

Your issue is related to the fact that some of the dates contained in your json are not specified (are equal to the empty string). When Postgres attempts the cast of the empty string to DATE datatype during the INSERT statement, doesn't know what to do with it, because the empty string is not a null value.

One way to go around to this problem is to define a temporary datatype, that can acquire empty strings as strings, then wrangle the raw datatypes to the table's datatypes.

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;

Output:

cli_nk	effective_from_date	effective_to_date	user_dml_type_code	user_fix_desc
1	2021-01-18T00:00:00.000Z	null	U	qwert
2	2023-03-25T00:00:00.000Z	null	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	U	zxc

Check the demo here.