Implicitly cast an ISO8601 string to TIMESTAMPTZ (postgresql) for Debezium

user_1726359 0
Viewed 357

I am using a 3rd party application (Debezium Connector). It has to write date time strings in ISO-8601 format into a TIMESTAMPTZ column. Unfortunately this fails, because there is no implicit cast from varchar to timestamp tz.

I did notice that the following works:

SELECT TIMESTAMPTZ('2021-01-05T05:17:46Z');
SELECT TIMESTAMPTZ('2021-01-05T05:17:46.123Z');

I tried the following:

  1. Create a function and a cast
CREATE OR REPLACE FUNCTION varchar_to_timestamptz(val VARCHAR) 
RETURNS timestamptz AS $$
    SELECT TIMESTAMPTZ(val) INTO tstz;
$$ LANGUAGE SQL;
CREATE CAST (varchar as timestamptz) WITH FUNCTION varchar_to_timestamptz (varchar) AS IMPLICIT;

Unfortunately, it gives the following errors:

function timestamptz(character varying) does not exist

  1. I also tried the same as above but using plpgsql and got the same error.

  2. I tried writing a manual parse, but had issues with the optional microsecond segment which gave me the following

CREATE OR REPLACE FUNCTION varchar_to_timestamptz (val varchar) RETURNS timestamptz AS $$ 
    SELECT CASE 
        WHEN $1 LIKE '%.%' 
            THEN to_timestamp($1, 'YYYY-MM-DD"T"HH24:MI:SS.USZ')::timestamp without time zone at time zone 'Etc/UTC' 
        ELSE to_timestamp($1, 'YYYY-MM-DD"T"HH24:MI:SSZ')::timestamp without time zone at time zone 'Etc/UTC' END $$ LANGUAGE SQL;

Which worked, but didn't feel correct.

Is there a better way to approach this implicit cast?

debezium apache-kafka-connect postgresql
1 Answers

If the value should be converted upon insert, define an assignment cast. You need no function; using the type input and output functions will do:

CREATE CAST (varchar AS timestamptz) WITH INOUT AS ASSIGNMENT;

Be warned that messing with the casts on standard data types can lead to problems, because it increases the ambiguity. It would be much better if you could find a way to use an explicit cast.

user_64643080
Related