title: Implicitly cast an ISO8601 string to TIMESTAMPTZ (postgresql) for Debezium
body: <p>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.</p>
  <p>I did notice that the following works:</p>
  <pre><code>SELECT TIMESTAMPTZ('2021-01-05T05:17:46Z');
  SELECT TIMESTAMPTZ('2021-01-05T05:17:46.123Z');
  </code></pre>
  <p>I tried the following:</p>
  <ol>
  <li>Create a function and a cast</li>
  </ol>
  <pre><code>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;
  </code></pre>
  <p>Unfortunately, it gives the following errors:</p>
  <blockquote>
  <p>function timestamptz(character varying) does not exist</p>
  </blockquote>
  <ol start="2">
  <li><p>I also tried the same as above but using plpgsql and got the same error.</p>
  </li>
  <li><p>I tried writing a manual parse, but had issues with the optional microsecond segment which gave me the following</p>
  </li>
  </ol>
  <pre><code>CREATE OR REPLACE FUNCTION varchar_to_timestamptz (val varchar) RETURNS timestamptz AS $$ 
      SELECT CASE 
          WHEN $1 LIKE '%.%' 
              THEN to_timestamp($1, 'YYYY-MM-DD&quot;T&quot;HH24:MI:SS.USZ')::timestamp without time zone at time zone 'Etc/UTC' 
          ELSE to_timestamp($1, 'YYYY-MM-DD&quot;T&quot;HH24:MI:SSZ')::timestamp without time zone at time zone 'Etc/UTC' END $$ LANGUAGE SQL;
  </code></pre>
  <p>Which worked, but didn't feel correct.</p>
  <p>Is there a better way to approach this implicit cast?</p>