Materialize/test/pg-cdc/types-temporal-with-tz.td

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

#
# Test the temporal data types with time zone
#

> CREATE SECRET pgpass AS 'postgres'
> CREATE CONNECTION pg TO POSTGRES (
    HOST postgres,
    DATABASE postgres,
    USER postgres,
    PASSWORD SECRET pgpass
  )

# Insert data pre-snapshot
$ postgres-execute connection=postgres://postgres:postgres@postgres
ALTER USER postgres WITH replication;
DROP SCHEMA IF EXISTS public CASCADE;
DROP PUBLICATION IF EXISTS mz_source;

CREATE SCHEMA public;

CREATE TABLE time_table (f1 TIME WITH TIME ZONE);
INSERT INTO time_table VALUES ('11:11:11.123456-09');
INSERT INTO time_table VALUES ('11:11:11.123456+09');
ALTER TABLE time_table REPLICA IDENTITY FULL;

CREATE TABLE timestamp_table (f1 TIMESTAMPTZ);
INSERT INTO timestamp_table VALUES ('2011-11-11 11:11:11.123456-09');
INSERT INTO timestamp_table VALUES ('2011-11-11 11:11:11.123456+09');
ALTER TABLE timestamp_table REPLICA IDENTITY FULL;

CREATE PUBLICATION mz_source_time FOR TABLE time_table;

CREATE PUBLICATION mz_source_timestamp FOR TABLE timestamp_table;

> CREATE SOURCE mz_source_timestamp
  FROM POSTGRES CONNECTION pg (PUBLICATION 'mz_source_timestamp');

> CREATE TABLE timestamp_table FROM SOURCE mz_source_timestamp (REFERENCE timestamp_table);

> SELECT COUNT(*) > 0 FROM timestamp_table;
true

# Insert the same data post-snapshot
$ postgres-execute connection=postgres://postgres:postgres@postgres
INSERT INTO time_table SELECT * FROM time_table;
INSERT INTO timestamp_table SELECT * FROM timestamp_table;

> SELECT pg_typeof(f1) FROM timestamp_table LIMIT 1;
"timestamp with time zone"

> SELECT * FROM timestamp_table;
"2011-11-11 02:11:11.123456 UTC"
"2011-11-11 02:11:11.123456 UTC"
"2011-11-11 20:11:11.123456 UTC"
"2011-11-11 20:11:11.123456 UTC"

> DROP SOURCE mz_source_timestamp CASCADE;

> CREATE SOURCE mz_source
  FROM POSTGRES CONNECTION pg (PUBLICATION 'mz_source_time');

# Mz does not support TIME WITH TIME ZOINE
! CREATE TABLE timestamp_table FROM SOURCE mz_source (REFERENCE time_table);
contains:type "pg_catalog.timetz" does not exist