SET intervalstyle = 'postgres';
CREATE SERVER binary_interval_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'interval_test', driver 'binary');
CREATE SERVER http_interval_loopback FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(dbname 'interval_test', driver 'http');
CREATE USER MAPPING FOR CURRENT_USER SERVER binary_interval_loopback;
CREATE USER MAPPING FOR CURRENT_USER SERVER http_interval_loopback;
CREATE SERVER interval_admin FOREIGN DATA WRAPPER clickhouse_fdw;
CREATE USER MAPPING FOR CURRENT_USER SERVER interval_admin;
\set ECHO errors
CALL clickhouse_perform('interval_admin', 'DROP DATABASE IF EXISTS interval_test');
CALL clickhouse_perform('interval_admin', 'CREATE DATABASE interval_test');
CALL clickhouse_perform('interval_admin', format($$
    CREATE TABLE interval_test.intervals (
        id       Int32                NOT NULL,
        base     DateTime64(6, 'UTC') NOT NULL,
        nanos    IntervalNanosecond   NOT NULL,
        micros   IntervalMicrosecond  NOT NULL,
        millis   IntervalMillisecond  NOT NULL,
        seconds  IntervalSecond       NOT NULL,
        minutes  IntervalMinute       NOT NULL,
        hours    IntervalHour         NOT NULL,
        days     IntervalDay          NOT NULL,
        weeks    IntervalWeek         NOT NULL,
        months   IntervalMonth        NOT NULL,
        quarters IntervalQuarter      NOT NULL,
        years    IntervalYear         NOT NULL
    ) ENGINE = MergeTree ORDER BY (id);
$$));
-- Start with the imported format using the interval type.
CREATE SCHEMA ival_bin;
CREATE SCHEMA ival_http;
IMPORT FOREIGN SCHEMA "interval_test" FROM SERVER binary_interval_loopback INTO ival_bin;
NOTICE:  pg_clickhouse: ClickHouse <column "nanos"> precision exceeds microseconds (6), interval truncates it
IMPORT FOREIGN SCHEMA "interval_test" FROM SERVER http_interval_loopback INTO ival_http;
NOTICE:  pg_clickhouse: ClickHouse <column "nanos"> precision exceeds microseconds (6), interval truncates it
SELECT attname, format_type(atttypid, atttypmod) AS type
  FROM pg_attribute
 WHERE attrelid = 'ival_bin.intervals'::regclass AND attnum > 0
 ORDER BY attnum;
 attname  |            type             
----------+-----------------------------
 id       | integer
 base     | timestamp(6) with time zone
 nanos    | interval
 micros   | interval
 millis   | interval
 seconds  | interval
 minutes  | interval
 hours    | interval
 days     | interval
 weeks    | interval
 months   | interval
 quarters | interval
 years    | interval
(13 rows)

SELECT attname, format_type(atttypid, atttypmod) AS type
  FROM pg_attribute
 WHERE attrelid = 'ival_http.intervals'::regclass AND attnum > 0
 ORDER BY attnum;
 attname  |            type             
----------+-----------------------------
 id       | integer
 base     | timestamp(6) with time zone
 nanos    | interval
 micros   | interval
 millis   | interval
 seconds  | interval
 minutes  | interval
 hours    | interval
 days     | interval
 weeks    | interval
 months   | interval
 quarters | interval
 years    | interval
(13 rows)

-- Insert values.
INSERT INTO ival_bin.intervals
VALUES ( 1, '2026-09-01 00:00:00Z', '42 microsecond', '42 microsecond', '42 ms', '42 s', '42 m', '42 h', '42 d', '42 w', '42 mon', '168 mon', '42 y')
     , ( 2, '2026-09-02 00:00:00Z', '21 microsecond', '21 microsecond', '21 ms', '21 s', '21 m', '21 h', '21 d', '21 w', '21 mon', '84 mon',  '21 y')
;
INSERT INTO ival_http.intervals
VALUES ( 3, '2026-09-03 00:00:00Z', '21 microsecond', '21 microsecond', '21 ms', '21 s', '21 m', '21 h', '21 d', '21 w', '21 mon', '84 mon',  '21 y')
     , ( 4, '2026-09-04 00:00:00Z', '33 microsecond', '33 microsecond', '33 ms', '33 s', '33 m', '33 h', '33 d', '33 w', '33 mon', '132 mon', '33 y')
;
-- They should all be there.
SELECT * FROM ival_bin.intervals ORDER BY id;
 id |             base             |      nanos      |     micros      |    millis    | seconds  | minutes  |  hours   |  days   |  weeks   |     months     | quarters |  years   
----+------------------------------+-----------------+-----------------+--------------+----------+----------+----------+---------+----------+----------------+----------+----------
  1 | Mon Aug 31 17:00:00 2026 PDT | 00:00:00.000042 | 00:00:00.000042 | 00:00:00.042 | 00:00:42 | 00:42:00 | 42:00:00 | 42 days | 294 days | 3 years 6 mons | 14 years | 42 years
  2 | Tue Sep 01 17:00:00 2026 PDT | 00:00:00.000021 | 00:00:00.000021 | 00:00:00.021 | 00:00:21 | 00:21:00 | 21:00:00 | 21 days | 147 days | 1 year 9 mons  | 7 years  | 21 years
  3 | Wed Sep 02 17:00:00 2026 PDT | 00:00:00.000021 | 00:00:00.000021 | 00:00:00.021 | 00:00:21 | 00:21:00 | 21:00:00 | 21 days | 147 days | 1 year 9 mons  | 7 years  | 21 years
  4 | Thu Sep 03 17:00:00 2026 PDT | 00:00:00.000033 | 00:00:00.000033 | 00:00:00.033 | 00:00:33 | 00:33:00 | 33:00:00 | 33 days | 231 days | 2 years 9 mons | 11 years | 33 years
(4 rows)

SELECT * FROM ival_http.intervals ORDER BY id;
 id |             base             |      nanos      |     micros      |    millis    | seconds  | minutes  |  hours   |  days   |  weeks   |     months     | quarters |  years   
----+------------------------------+-----------------+-----------------+--------------+----------+----------+----------+---------+----------+----------------+----------+----------
  1 | Mon Aug 31 17:00:00 2026 PDT | 00:00:00.000042 | 00:00:00.000042 | 00:00:00.042 | 00:00:42 | 00:42:00 | 42:00:00 | 42 days | 294 days | 3 years 6 mons | 14 years | 42 years
  2 | Tue Sep 01 17:00:00 2026 PDT | 00:00:00.000021 | 00:00:00.000021 | 00:00:00.021 | 00:00:21 | 00:21:00 | 21:00:00 | 21 days | 147 days | 1 year 9 mons  | 7 years  | 21 years
  3 | Wed Sep 02 17:00:00 2026 PDT | 00:00:00.000021 | 00:00:00.000021 | 00:00:00.021 | 00:00:21 | 00:21:00 | 21:00:00 | 21 days | 147 days | 1 year 9 mons  | 7 years  | 21 years
  4 | Thu Sep 03 17:00:00 2026 PDT | 00:00:00.000033 | 00:00:00.000033 | 00:00:00.033 | 00:00:33 | 00:33:00 | 33:00:00 | 33 days | 231 days | 2 years 9 mons | 11 years | 33 years
(4 rows)

-- Test operator pushdown.
EXPLAIN (verbose, COSTS OFF)
SELECT id, base + nanos
  FROM ival_bin.intervals
 WHERE base + nanos > base
 ORDER BY id;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ival_bin.intervals
   Output: id, (base + nanos)
   Remote SQL: SELECT id, base, nanos FROM interval_test.intervals WHERE (((base + nanos) > base)) ORDER BY id ASC NULLS LAST
(3 rows)

SELECT id, base + nanos
  FROM ival_bin.intervals
 WHERE base + nanos > base
 ORDER BY id;
 id |              ?column?               
----+-------------------------------------
  1 | Mon Aug 31 17:00:00.000042 2026 PDT
  2 | Tue Sep 01 17:00:00.000021 2026 PDT
  3 | Wed Sep 02 17:00:00.000021 2026 PDT
  4 | Thu Sep 03 17:00:00.000033 2026 PDT
(4 rows)

EXPLAIN (verbose, COSTS OFF)
 SELECT id, base - nanos
   FROM ival_bin.intervals
  WHERE base - nanos < base
  ORDER BY id;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on ival_bin.intervals
   Output: id, (base - nanos)
   Remote SQL: SELECT id, base, nanos FROM interval_test.intervals WHERE (((base - nanos) < base)) ORDER BY id ASC NULLS LAST
(3 rows)

 SELECT id, base - nanos
   FROM ival_bin.intervals
  WHERE base - nanos < base
  ORDER BY id;
 id |              ?column?               
----+-------------------------------------
  1 | Mon Aug 31 16:59:59.999958 2026 PDT
  2 | Tue Sep 01 16:59:59.999979 2026 PDT
  3 | Wed Sep 02 16:59:59.999979 2026 PDT
  4 | Thu Sep 03 16:59:59.999967 2026 PDT
(4 rows)

-- Now try BIGINTS.
CREATE FOREIGN TABLE ival_bin.int_intervals (
    id       INT         NOT NULL,
    base     TIMESTAMPTZ NOT NULL,
    nanos    BIGINT      NOT NULL,
    micros   BIGINT      NOT NULL,
    millis   BIGINT      NOT NULL,
    seconds  BIGINT      NOT NULL,
    minutes  BIGINT      NOT NULL,
    hours    BIGINT      NOT NULL,
    days     BIGINT      NOT NULL,
    weeks    BIGINT      NOT NULL,
    months   BIGINT      NOT NULL,
    quarters BIGINT      NOT NULL,
    years    BIGINT      NOT NULL
) SERVER binary_interval_loopback OPTIONS (table_name 'intervals');
CREATE FOREIGN TABLE ival_http.int_intervals (
    id       INT         NOT NULL,
    base     TIMESTAMPTZ NOT NULL,
    nanos    BIGINT      NOT NULL,
    micros   BIGINT      NOT NULL,
    millis   BIGINT      NOT NULL,
    seconds  BIGINT      NOT NULL,
    minutes  BIGINT      NOT NULL,
    hours    BIGINT      NOT NULL,
    days     BIGINT      NOT NULL,
    weeks    BIGINT      NOT NULL,
    months   BIGINT      NOT NULL,
    quarters BIGINT      NOT NULL,
    years    BIGINT      NOT NULL
) SERVER http_interval_loopback OPTIONS (table_name 'intervals');
-- Insert data.
CALL clickhouse_perform('interval_admin', 'TRUNCATE interval_test.intervals');
INSERT INTO ival_bin.int_intervals
VALUES ( 1, '2026-09-01 00:00:00Z', 42, 42, 42, 42, 42, 42, 42, 42, 42, 42, 42)
     , ( 2, '2026-09-02 00:00:00Z', 21, 21, 21, 21, 21, 21, 21, 21, 21, 21, 21)
;
-- Fails because http driver doesn't know the remote interval subtype. Would
-- need to either `DESCRIBE` the table in advance, or perhaps store the types
-- in a column option. Probably not worth it given that the binary driver
-- works fine and is preferred.
INSERT INTO ival_http.int_intervals
VALUES ( 3, '2026-09-03 00:00:00Z', 21, 21, 21, 21, 21, 21, 21, 21, 21, 21, 21)
     , ( 4, '2026-09-04 00:00:00Z', 33, 33, 33, 33, 33, 33, 33, 33, 33, 33, 33)
;
SELECT * FROM ival_bin.int_intervals ORDER BY id;
 id |             base             | nanos | micros | millis | seconds | minutes | hours | days | weeks | months | quarters | years 
----+------------------------------+-------+--------+--------+---------+---------+-------+------+-------+--------+----------+-------
  1 | Mon Aug 31 17:00:00 2026 PDT |    42 |     42 |     42 |      42 |      42 |    42 |   42 |    42 |     42 |       42 |    42
  2 | Tue Sep 01 17:00:00 2026 PDT |    21 |     21 |     21 |      21 |      21 |    21 |   21 |    21 |     21 |       21 |    21
  3 | Wed Sep 02 17:00:00 2026 PDT |    21 |     21 |     21 |      21 |      21 |    21 |   21 |    21 |     21 |       21 |    21
  4 | Thu Sep 03 17:00:00 2026 PDT |    33 |     33 |     33 |      33 |      33 |    33 |   33 |    33 |     33 |       33 |    33
(4 rows)

SELECT * FROM ival_http.int_intervals ORDER BY id;
 id |             base             | nanos | micros | millis | seconds | minutes | hours | days | weeks | months | quarters | years 
----+------------------------------+-------+--------+--------+---------+---------+-------+------+-------+--------+----------+-------
  1 | Mon Aug 31 17:00:00 2026 PDT |    42 |     42 |     42 |      42 |      42 |    42 |   42 |    42 |     42 |       42 |    42
  2 | Tue Sep 01 17:00:00 2026 PDT |    21 |     21 |     21 |      21 |      21 |    21 |   21 |    21 |     21 |       21 |    21
  3 | Wed Sep 02 17:00:00 2026 PDT |    21 |     21 |     21 |      21 |      21 |    21 |   21 |    21 |     21 |       21 |    21
  4 | Thu Sep 03 17:00:00 2026 PDT |    33 |     33 |     33 |      33 |      33 |    33 |   33 |    33 |     33 |       33 |    33
(4 rows)

CALL clickhouse_perform('interval_admin', 'DROP DATABASE interval_test');
DROP USER MAPPING FOR CURRENT_USER SERVER binary_interval_loopback;
DROP USER MAPPING FOR CURRENT_USER SERVER http_interval_loopback;
DROP SERVER binary_interval_loopback CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to foreign table ival_bin.intervals
drop cascades to foreign table ival_bin.int_intervals
DROP SERVER http_interval_loopback CASCADE;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to foreign table ival_http.intervals
drop cascades to foreign table ival_http.int_intervals