SET timezone = 'UTC';
SELECT typlen, typbyval, typalign FROM pg_type WHERE typname = 'nanotimestamptz8';
 typlen | typbyval | typalign 
--------+----------+----------
      8 | t        | d
(1 row)

-- range = int64 ns, strict errors 1 ns outside
SELECT '1677-09-21 00:12:43.145224192 UTC'::nanotimestamptz8 AS min8, '2262-04-11 23:47:16.854775807 UTC'::nanotimestamptz8 AS max8;
               min8               |               max8               
----------------------------------+----------------------------------
 1677-09-21 00:12:43.145224192+00 | 2262-04-11 23:47:16.854775807+00
(1 row)

SELECT '1677-09-21 00:12:43.145224191 UTC'::nanotimestamptz8;
ERROR:  nanotimestamptz8 out of range
LINE 1: SELECT '1677-09-21 00:12:43.145224191 UTC'::nanotimestamptz8...
               ^
DETAIL:  nanotimestamptz8 covers only 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807 UTC.
HINT:  Use nanotimestamptz (16 bytes) for values outside this range.
SELECT '2262-04-11 23:47:16.854775808 UTC'::nanotimestamptz8;
ERROR:  nanotimestamptz8 out of range
LINE 1: SELECT '2262-04-11 23:47:16.854775808 UTC'::nanotimestamptz8...
               ^
DETAIL:  nanotimestamptz8 covers only 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807 UTC.
HINT:  Use nanotimestamptz (16 bytes) for values outside this range.
SELECT '1500-01-01 UTC'::nanotimestamptz8;
ERROR:  nanotimestamptz8 out of range
LINE 1: SELECT '1500-01-01 UTC'::nanotimestamptz8;
               ^
DETAIL:  nanotimestamptz8 covers only 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807 UTC.
HINT:  Use nanotimestamptz (16 bytes) for values outside this range.
SELECT '1969-12-31 23:59:59.999999999 UTC'::nanotimestamptz8, '1970-01-01 UTC'::nanotimestamptz8;
         nanotimestamptz8         |         nanotimestamptz8         
----------------------------------+----------------------------------
 1969-12-31 23:59:59.999999999+00 | 1970-01-01 00:00:00.000000000+00
(1 row)

SET timezone = 'Asia/Taipei';
SELECT '2024-01-01 00:00:00.123456789 UTC'::nanotimestamptz8;
         nanotimestamptz8         
----------------------------------
 2024-01-01 08:00:00.123456789+08
(1 row)

SET timezone = 'UTC';
-- bigint: binary-compatible, explicit only
SELECT castmethod, castcontext FROM pg_cast WHERE castsource = 'nanotimestamptz8'::regtype AND casttarget = 'bigint'::regtype
UNION ALL SELECT castmethod, castcontext FROM pg_cast WHERE castsource = 'bigint'::regtype AND casttarget = 'nanotimestamptz8'::regtype;
 castmethod | castcontext 
------------+-------------
 b          | e
 b          | e
(2 rows)

SELECT (-9223372036854775808)::bigint::nanotimestamptz8, 9223372036854775807::bigint::nanotimestamptz8, (-1)::bigint::nanotimestamptz8;
         nanotimestamptz8         |         nanotimestamptz8         |         nanotimestamptz8         
----------------------------------+----------------------------------+----------------------------------
 1677-09-21 00:12:43.145224192+00 | 2262-04-11 23:47:16.854775807+00 | 1969-12-31 23:59:59.999999999+00
(1 row)

SELECT '1969-12-31 23:59:59.999999999 UTC'::nanotimestamptz8::bigint;
 int8 
------
   -1
(1 row)

-- 16B <-> 8B
SELECT '2024-01-01 00:00:00.000000001 UTC'::nanotimestamptz8::nanotimestamptz;
         nanotimestamptz          
----------------------------------
 2024-01-01 00:00:00.000000001+00
(1 row)

SELECT '1677-09-21 00:12:43.145224192 UTC'::nanotimestamptz::nanotimestamptz8;
         nanotimestamptz8         
----------------------------------
 1677-09-21 00:12:43.145224192+00
(1 row)

SELECT '1677-09-21 00:12:43.145224191 UTC'::nanotimestamptz::nanotimestamptz8;
ERROR:  nanotimestamptz8 out of range
DETAIL:  nanotimestamptz8 covers only 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807 UTC.
HINT:  Use nanotimestamptz (16 bytes) for values outside this range.
SELECT '2262-04-11 23:47:16.854775808 UTC'::nanotimestamptz::nanotimestamptz8;
ERROR:  nanotimestamptz8 out of range
DETAIL:  nanotimestamptz8 covers only 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807 UTC.
HINT:  Use nanotimestamptz (16 bytes) for values outside this range.
SELECT '2024-01-01 UTC'::nanotimestamptz8 = '2024-01-01 UTC'::nanotimestamptz AS mixed_eq;
 mixed_eq 
----------
 t
(1 row)

-- timestamptz (floor)
SELECT '1969-12-31 23:59:59.999999999 UTC'::nanotimestamptz8::timestamptz, '2024-01-01 00:00:00.123456999 UTC'::nanotimestamptz8::timestamptz;
             timestamptz             |             timestamptz             
-------------------------------------+-------------------------------------
 Wed Dec 31 23:59:59.999999 1969 UTC | Mon Jan 01 00:00:00.123456 2024 UTC
(1 row)

SELECT '2024-01-01 00:00:00.123456 UTC'::timestamptz::nanotimestamptz8;
         nanotimestamptz8         
----------------------------------
 2024-01-01 00:00:00.123456000+00
(1 row)

SELECT '1600-01-01 UTC'::timestamptz::nanotimestamptz8;
ERROR:  nanotimestamptz8 out of range
DETAIL:  nanotimestamptz8 covers only 1677-09-21 00:12:43.145224192 to 2262-04-11 23:47:16.854775807 UTC.
HINT:  Use nanotimestamptz (16 bytes) for values outside this range.
-- binary I/O
SELECT nanotimestamptz8_send('1969-12-31 23:59:59.999999999 UTC');
 nanotimestamptz8_send 
-----------------------
 \xffffffffffffffff
(1 row)

CREATE TEMP TABLE b8(t nanotimestamptz8);
INSERT INTO b8 VALUES ('1677-09-21 00:12:43.145224192 UTC'), ('1969-12-31 23:59:59.999999999 UTC'), ('2262-04-11 23:47:16.854775807 UTC');
-- per-port file name so several servers (different OS users) can run the suite
SELECT '/tmp/pg_nanotime_regress8_' || current_setting('port') || '.bin' AS binfile \gset
COPY b8 TO :'binfile' WITH (FORMAT binary);
CREATE TEMP TABLE b8d(t nanotimestamptz8);
COPY b8d FROM :'binfile' WITH (FORMAT binary);
SELECT count(*) FROM b8 JOIN b8d USING (t);
 count 
-------
     3
(1 row)

-- clocks
SELECT provolatile, proname FROM pg_proc WHERE proname IN ('clock_ns8', 'now_ns8', 'statement_ns8') ORDER BY proname;
 provolatile |    proname    
-------------+---------------
 v           | clock_ns8
 s           | now_ns8
 s           | statement_ns8
(3 rows)

BEGIN;
SELECT now_ns8()::nanotimestamptz = now_ns() AS same_xact_reading, statement_ns8()::nanotimestamptz = statement_ns() AS same_stmt_reading;
 same_xact_reading | same_stmt_reading 
-------------------+-------------------
 t                 | t
(1 row)

SELECT clock_ns8() >= now_ns8() AS clock_after;
 clock_after 
-------------
 t
(1 row)

COMMIT;
CREATE TEMP TABLE ev8(id int, t nanotimestamptz8 DEFAULT clock_ns8(), tx nanotimestamptz8 DEFAULT now_ns8());
INSERT INTO ev8(id) SELECT g FROM generate_series(1, 1000) g;
SELECT count(DISTINCT t) > 1 AS clock_varies, count(DISTINCT tx) AS now_constant FROM ev8;
 clock_varies | now_constant 
--------------+--------------
 t            |            1
(1 row)

-- indexes
CREATE TEMP TABLE ix8(t nanotimestamptz8, v int);
INSERT INTO ix8 SELECT (g::bigint * 1000000007 - 5000000000000)::nanotimestamptz8, g FROM generate_series(1, 10000) g;
INSERT INTO ix8 VALUES ((-9223372036854775808)::bigint::nanotimestamptz8, -1), (9223372036854775807::bigint::nanotimestamptz8, -2);
ANALYZE ix8;
SET enable_seqscan = off;
SET enable_bitmapscan = off;
CREATE INDEX ix8_bt ON ix8(t);
EXPLAIN (COSTS OFF) SELECT v FROM ix8 WHERE t >= '1969-12-31 UTC' AND t < '1970-01-01 UTC';
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using ix8_bt on ix8
   Index Cond: ((t >= '1969-12-31 00:00:00.000000000+00'::nanotimestamptz8) AND (t < '1970-01-01 00:00:00.000000000+00'::nanotimestamptz8))
(2 rows)

SELECT t, v FROM ix8 ORDER BY t LIMIT 2;
                t                 | v  
----------------------------------+----
 1677-09-21 00:12:43.145224192+00 | -1
 1969-12-31 22:36:41.000000007+00 |  1
(2 rows)

SELECT t, v FROM ix8 ORDER BY t DESC LIMIT 2;
                t                 |   v   
----------------------------------+-------
 2262-04-11 23:47:16.854775807+00 |    -2
 1970-01-01 01:23:20.000070000+00 | 10000
(2 rows)

DROP INDEX ix8_bt;
CREATE INDEX ix8_h ON ix8 USING hash(t);
EXPLAIN (COSTS OFF) SELECT v FROM ix8 WHERE t = '1969-12-31 22:36:41.000000007 UTC';
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Index Scan using ix8_h on ix8
   Index Cond: (t = '1969-12-31 22:36:41.000000007+00'::nanotimestamptz8)
(2 rows)

SELECT v FROM ix8 WHERE t = '1969-12-31 22:36:41.000000007 UTC';
 v 
---
 1
(1 row)

DROP INDEX ix8_h;
SET enable_bitmapscan = on;
CREATE INDEX ix8_brin ON ix8 USING brin(t);
EXPLAIN (COSTS OFF) SELECT v FROM ix8 WHERE t >= '1969-12-31 23:00:00 UTC' AND t < '1969-12-31 23:00:01 UTC';
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on ix8
   Recheck Cond: ((t >= '1969-12-31 23:00:00.000000000+00'::nanotimestamptz8) AND (t < '1969-12-31 23:00:01.000000000+00'::nanotimestamptz8))
   ->  Bitmap Index Scan on ix8_brin
         Index Cond: ((t >= '1969-12-31 23:00:00.000000000+00'::nanotimestamptz8) AND (t < '1969-12-31 23:00:01.000000000+00'::nanotimestamptz8))
(4 rows)

SELECT v FROM ix8 WHERE t >= '1969-12-31 23:00:00 UTC' AND t < '1969-12-31 23:00:01 UTC';
  v   
------
 1400
(1 row)

RESET enable_seqscan;
SELECT min(t), max(t), count(DISTINCT t) FROM ix8;
               min                |               max                | count 
----------------------------------+----------------------------------+-------
 1677-09-21 00:12:43.145224192+00 | 2262-04-11 23:47:16.854775807+00 | 10002
(1 row)