YQL-19206 Move contrib/ydb/library/yql/tests/postgresql -> yql/essentials/tests/postgresql

commit_hash:46fdf59714b20cf2b61233a06e58365227d3c8b2

1 files changed, 1045 insertions, 0 deletions

diff --git a/yql/essentials/tests/postgresql/original/cases/interval.out b/yql/essentials/tests/postgresql/original/cases/interval.out
new file mode 100644
index 0000000000..8e2d535543
--- /dev/null
+++ b/yql/essentials/tests/postgresql/original/cases/interval.out
@@ -0,0 +1,1045 @@
+--
+-- INTERVAL
+--
+SET DATESTYLE = 'ISO';
+SET IntervalStyle to postgres;
+-- check acceptance of "time zone style"
+SELECT INTERVAL '01:00' AS "One hour";
+ One hour 
+----------
+ 01:00:00
+(1 row)
+
+SELECT INTERVAL '+02:00' AS "Two hours";
+ Two hours 
+-----------
+ 02:00:00
+(1 row)
+
+SELECT INTERVAL '-08:00' AS "Eight hours";
+ Eight hours 
+-------------
+ -08:00:00
+(1 row)
+
+SELECT INTERVAL '-1 +02:03' AS "22 hours ago...";
+  22 hours ago...  
+-------------------
+ -1 days +02:03:00
+(1 row)
+
+SELECT INTERVAL '-1 days +02:03' AS "22 hours ago...";
+  22 hours ago...  
+-------------------
+ -1 days +02:03:00
+(1 row)
+
+SELECT INTERVAL '1.5 weeks' AS "Ten days twelve hours";
+ Ten days twelve hours 
+-----------------------
+ 10 days 12:00:00
+(1 row)
+
+SELECT INTERVAL '1.5 months' AS "One month 15 days";
+ One month 15 days 
+-------------------
+ 1 mon 15 days
+(1 row)
+
+SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years...";
+            9 years...            
+----------------------------------
+ 9 years 1 mon -12 days +13:14:00
+(1 row)
+
+CREATE TABLE INTERVAL_TBL (f1 interval);
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 1 minute');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 5 hour');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 10 day');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 34 year');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 3 months');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 14 seconds ago');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('1 day 2 hours 3 minutes 4 seconds');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('6 years');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months');
+INSERT INTO INTERVAL_TBL (f1) VALUES ('5 months 12 hours');
+-- badly formatted interval
+INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted interval');
+ERROR:  invalid input syntax for type interval: "badly formatted interval"
+LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('badly formatted inter...
+                                              ^
+INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+ERROR:  invalid input syntax for type interval: "@ 30 eons ago"
+LINE 1: INSERT INTO INTERVAL_TBL (f1) VALUES ('@ 30 eons ago');
+                                              ^
+-- test interval operators
+SELECT * FROM INTERVAL_TBL;
+       f1        
+-----------------
+ 00:01:00
+ 05:00:00
+ 10 days
+ 34 years
+ 3 mons
+ -00:00:14
+ 1 day 02:03:04
+ 6 years
+ 5 mons
+ 5 mons 12:00:00
+(10 rows)
+
+SELECT * FROM INTERVAL_TBL
+   WHERE INTERVAL_TBL.f1 <> interval '@ 10 days';
+       f1        
+-----------------
+ 00:01:00
+ 05:00:00
+ 34 years
+ 3 mons
+ -00:00:14
+ 1 day 02:03:04
+ 6 years
+ 5 mons
+ 5 mons 12:00:00
+(9 rows)
+
+SELECT * FROM INTERVAL_TBL
+   WHERE INTERVAL_TBL.f1 <= interval '@ 5 hours';
+    f1     
+-----------
+ 00:01:00
+ 05:00:00
+ -00:00:14
+(3 rows)
+
+SELECT * FROM INTERVAL_TBL
+   WHERE INTERVAL_TBL.f1 < interval '@ 1 day';
+    f1     
+-----------
+ 00:01:00
+ 05:00:00
+ -00:00:14
+(3 rows)
+
+SELECT * FROM INTERVAL_TBL
+   WHERE INTERVAL_TBL.f1 = interval '@ 34 years';
+    f1    
+----------
+ 34 years
+(1 row)
+
+SELECT * FROM INTERVAL_TBL
+   WHERE INTERVAL_TBL.f1 >= interval '@ 1 month';
+       f1        
+-----------------
+ 34 years
+ 3 mons
+ 6 years
+ 5 mons
+ 5 mons 12:00:00
+(5 rows)
+
+SELECT * FROM INTERVAL_TBL
+   WHERE INTERVAL_TBL.f1 > interval '@ 3 seconds ago';
+       f1        
+-----------------
+ 00:01:00
+ 05:00:00
+ 10 days
+ 34 years
+ 3 mons
+ 1 day 02:03:04
+ 6 years
+ 5 mons
+ 5 mons 12:00:00
+(9 rows)
+
+SELECT r1.*, r2.*
+   FROM INTERVAL_TBL r1, INTERVAL_TBL r2
+   WHERE r1.f1 > r2.f1
+   ORDER BY r1.f1, r2.f1;
+       f1        |       f1        
+-----------------+-----------------
+ 00:01:00        | -00:00:14
+ 05:00:00        | -00:00:14
+ 05:00:00        | 00:01:00
+ 1 day 02:03:04  | -00:00:14
+ 1 day 02:03:04  | 00:01:00
+ 1 day 02:03:04  | 05:00:00
+ 10 days         | -00:00:14
+ 10 days         | 00:01:00
+ 10 days         | 05:00:00
+ 10 days         | 1 day 02:03:04
+ 3 mons          | -00:00:14
+ 3 mons          | 00:01:00
+ 3 mons          | 05:00:00
+ 3 mons          | 1 day 02:03:04
+ 3 mons          | 10 days
+ 5 mons          | -00:00:14
+ 5 mons          | 00:01:00
+ 5 mons          | 05:00:00
+ 5 mons          | 1 day 02:03:04
+ 5 mons          | 10 days
+ 5 mons          | 3 mons
+ 5 mons 12:00:00 | -00:00:14
+ 5 mons 12:00:00 | 00:01:00
+ 5 mons 12:00:00 | 05:00:00
+ 5 mons 12:00:00 | 1 day 02:03:04
+ 5 mons 12:00:00 | 10 days
+ 5 mons 12:00:00 | 3 mons
+ 5 mons 12:00:00 | 5 mons
+ 6 years         | -00:00:14
+ 6 years         | 00:01:00
+ 6 years         | 05:00:00
+ 6 years         | 1 day 02:03:04
+ 6 years         | 10 days
+ 6 years         | 3 mons
+ 6 years         | 5 mons
+ 6 years         | 5 mons 12:00:00
+ 34 years        | -00:00:14
+ 34 years        | 00:01:00
+ 34 years        | 05:00:00
+ 34 years        | 1 day 02:03:04
+ 34 years        | 10 days
+ 34 years        | 3 mons
+ 34 years        | 5 mons
+ 34 years        | 5 mons 12:00:00
+ 34 years        | 6 years
+(45 rows)
+
+-- Test intervals that are large enough to overflow 64 bits in comparisons
+CREATE TEMP TABLE INTERVAL_TBL_OF (f1 interval);
+INSERT INTO INTERVAL_TBL_OF (f1) VALUES
+  ('2147483647 days 2147483647 months'),
+  ('2147483647 days -2147483648 months'),
+  ('1 year'),
+  ('-2147483648 days 2147483647 months'),
+  ('-2147483648 days -2147483648 months');
+-- these should fail as out-of-range
+INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
+ERROR:  interval field value out of range: "2147483648 days"
+LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483648 days');
+                                                 ^
+INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days');
+ERROR:  interval field value out of range: "-2147483649 days"
+LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483649 days')...
+                                                 ^
+INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years');
+ERROR:  interval out of range
+LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('2147483647 years')...
+                                                 ^
+INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years');
+ERROR:  interval out of range
+LINE 1: INSERT INTO INTERVAL_TBL_OF (f1) VALUES ('-2147483648 years'...
+                                                 ^
+-- Test edge-case overflow detection in interval multiplication
+select extract(epoch from '256 microseconds'::interval * (2^55)::float8);
+ERROR:  interval out of range
+SELECT r1.*, r2.*
+   FROM INTERVAL_TBL_OF r1, INTERVAL_TBL_OF r2
+   WHERE r1.f1 > r2.f1
+   ORDER BY r1.f1, r2.f1;
+                    f1                     |                    f1                     
+-------------------------------------------+-------------------------------------------
+ -178956970 years -8 mons +2147483647 days | -178956970 years -8 mons -2147483648 days
+ 1 year                                    | -178956970 years -8 mons -2147483648 days
+ 1 year                                    | -178956970 years -8 mons +2147483647 days
+ 178956970 years 7 mons -2147483648 days   | -178956970 years -8 mons -2147483648 days
+ 178956970 years 7 mons -2147483648 days   | -178956970 years -8 mons +2147483647 days
+ 178956970 years 7 mons -2147483648 days   | 1 year
+ 178956970 years 7 mons 2147483647 days    | -178956970 years -8 mons -2147483648 days
+ 178956970 years 7 mons 2147483647 days    | -178956970 years -8 mons +2147483647 days
+ 178956970 years 7 mons 2147483647 days    | 1 year
+ 178956970 years 7 mons 2147483647 days    | 178956970 years 7 mons -2147483648 days
+(10 rows)
+
+CREATE INDEX ON INTERVAL_TBL_OF USING btree (f1);
+SET enable_seqscan TO false;
+EXPLAIN (COSTS OFF)
+SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
+                             QUERY PLAN                             
+--------------------------------------------------------------------
+ Index Only Scan using interval_tbl_of_f1_idx on interval_tbl_of r1
+(1 row)
+
+SELECT f1 FROM INTERVAL_TBL_OF r1 ORDER BY f1;
+                    f1                     
+-------------------------------------------
+ -178956970 years -8 mons -2147483648 days
+ -178956970 years -8 mons +2147483647 days
+ 1 year
+ 178956970 years 7 mons -2147483648 days
+ 178956970 years 7 mons 2147483647 days
+(5 rows)
+
+RESET enable_seqscan;
+DROP TABLE INTERVAL_TBL_OF;
+-- Test multiplication and division with intervals.
+-- Floating point arithmetic rounding errors can lead to unexpected results,
+-- though the code attempts to do the right thing and round up to days and
+-- minutes to avoid results such as '3 days 24:00 hours' or '14:20:60'.
+-- Note that it is expected for some day components to be greater than 29 and
+-- some time components be greater than 23:59:59 due to how intervals are
+-- stored internally.
+CREATE TABLE INTERVAL_MULDIV_TBL (span interval);
+COPY INTERVAL_MULDIV_TBL FROM STDIN;
+SELECT span * 0.3 AS product
+FROM INTERVAL_MULDIV_TBL;
+              product               
+------------------------------------
+ 1 year 12 days 122:24:00
+ -1 years -12 days +93:36:00
+ -3 days -14:24:00
+ 2 mons 13 days 01:22:28.8
+ -10 mons +120 days 37:28:21.6567
+ 1 mon 6 days
+ 4 mons 6 days
+ 24 years 11 mons 320 days 16:48:00
+(8 rows)
+
+SELECT span * 8.2 AS product
+FROM INTERVAL_MULDIV_TBL;
+                   product                   
+---------------------------------------------
+ 28 years 104 days 2961:36:00
+ -28 years -104 days +2942:24:00
+ -98 days -09:36:00
+ 6 years 1 mon -197 days +93:34:27.2
+ -24 years -7 mons +3946 days 640:15:11.9498
+ 2 years 8 mons 24 days
+ 9 years 6 mons 24 days
+ 682 years 7 mons 8215 days 19:12:00
+(8 rows)
+
+SELECT span / 10 AS quotient
+FROM INTERVAL_MULDIV_TBL;
+             quotient             
+----------------------------------
+ 4 mons 4 days 40:48:00
+ -4 mons -4 days +31:12:00
+ -1 days -04:48:00
+ 25 days -15:32:30.4
+ -3 mons +30 days 12:29:27.2189
+ 12 days
+ 1 mon 12 days
+ 8 years 3 mons 126 days 21:36:00
+(8 rows)
+
+SELECT span / 100 AS quotient
+FROM INTERVAL_MULDIV_TBL;
+        quotient         
+-------------------------
+ 12 days 13:40:48
+ -12 days -06:28:48
+ -02:52:48
+ 2 days 10:26:44.96
+ -6 days +01:14:56.72189
+ 1 day 04:48:00
+ 4 days 04:48:00
+ 9 mons 39 days 16:33:36
+(8 rows)
+
+DROP TABLE INTERVAL_MULDIV_TBL;
+SET DATESTYLE = 'postgres';
+SET IntervalStyle to postgres_verbose;
+SELECT * FROM INTERVAL_TBL;
+              f1               
+-------------------------------
+ @ 1 min
+ @ 5 hours
+ @ 10 days
+ @ 34 years
+ @ 3 mons
+ @ 14 secs ago
+ @ 1 day 2 hours 3 mins 4 secs
+ @ 6 years
+ @ 5 mons
+ @ 5 mons 12 hours
+(10 rows)
+
+-- test avg(interval), which is somewhat fragile since people have been
+-- known to change the allowed input syntax for type interval without
+-- updating pg_aggregate.agginitval
+select avg(f1) from interval_tbl;
+                       avg                       
+-------------------------------------------------
+ @ 4 years 1 mon 10 days 4 hours 18 mins 23 secs
+(1 row)
+
+-- test long interval input
+select '4 millenniums 5 centuries 4 decades 1 year 4 months 4 days 17 minutes 31 seconds'::interval;
+                  interval                  
+--------------------------------------------
+ @ 4541 years 4 mons 4 days 17 mins 31 secs
+(1 row)
+
+-- test long interval output
+-- Note: the actual maximum length of the interval output is longer,
+-- but we need the test to work for both integer and floating-point
+-- timestamps.
+select '100000000y 10mon -1000000000d -100000h -10min -10.000001s ago'::interval;
+                                       interval                                        
+---------------------------------------------------------------------------------------
+ @ 100000000 years 10 mons -1000000000 days -100000 hours -10 mins -10.000001 secs ago
+(1 row)
+
+-- test justify_hours() and justify_days()
+SELECT justify_hours(interval '6 months 3 days 52 hours 3 minutes 2 seconds') as "6 mons 5 days 4 hours 3 mins 2 seconds";
+ 6 mons 5 days 4 hours 3 mins 2 seconds 
+----------------------------------------
+ @ 6 mons 5 days 4 hours 3 mins 2 secs
+(1 row)
+
+SELECT justify_days(interval '6 months 36 days 5 hours 4 minutes 3 seconds') as "7 mons 6 days 5 hours 4 mins 3 seconds";
+ 7 mons 6 days 5 hours 4 mins 3 seconds 
+----------------------------------------
+ @ 7 mons 6 days 5 hours 4 mins 3 secs
+(1 row)
+
+-- test justify_interval()
+SELECT justify_interval(interval '1 month -1 hour') as "1 month -1 hour";
+  1 month -1 hour   
+--------------------
+ @ 29 days 23 hours
+(1 row)
+
+-- test fractional second input, and detection of duplicate units
+SET DATESTYLE = 'ISO';
+SET IntervalStyle TO postgres;
+SELECT '1 millisecond'::interval, '1 microsecond'::interval,
+       '500 seconds 99 milliseconds 51 microseconds'::interval;
+   interval   |    interval     |    interval     
+--------------+-----------------+-----------------
+ 00:00:00.001 | 00:00:00.000001 | 00:08:20.099051
+(1 row)
+
+SELECT '3 days 5 milliseconds'::interval;
+      interval       
+---------------------
+ 3 days 00:00:00.005
+(1 row)
+
+SELECT '1 second 2 seconds'::interval;              -- error
+ERROR:  invalid input syntax for type interval: "1 second 2 seconds"
+LINE 1: SELECT '1 second 2 seconds'::interval;
+               ^
+SELECT '10 milliseconds 20 milliseconds'::interval; -- error
+ERROR:  invalid input syntax for type interval: "10 milliseconds 20 milliseconds"
+LINE 1: SELECT '10 milliseconds 20 milliseconds'::interval;
+               ^
+SELECT '5.5 seconds 3 milliseconds'::interval;      -- error
+ERROR:  invalid input syntax for type interval: "5.5 seconds 3 milliseconds"
+LINE 1: SELECT '5.5 seconds 3 milliseconds'::interval;
+               ^
+SELECT '1:20:05 5 microseconds'::interval;          -- error
+ERROR:  invalid input syntax for type interval: "1:20:05 5 microseconds"
+LINE 1: SELECT '1:20:05 5 microseconds'::interval;
+               ^
+SELECT '1 day 1 day'::interval;                     -- error
+ERROR:  invalid input syntax for type interval: "1 day 1 day"
+LINE 1: SELECT '1 day 1 day'::interval;
+               ^
+SELECT interval '1-2';  -- SQL year-month literal
+   interval    
+---------------
+ 1 year 2 mons
+(1 row)
+
+SELECT interval '999' second;  -- oversize leading field is ok
+ interval 
+----------
+ 00:16:39
+(1 row)
+
+SELECT interval '999' minute;
+ interval 
+----------
+ 16:39:00
+(1 row)
+
+SELECT interval '999' hour;
+ interval  
+-----------
+ 999:00:00
+(1 row)
+
+SELECT interval '999' day;
+ interval 
+----------
+ 999 days
+(1 row)
+
+SELECT interval '999' month;
+    interval     
+-----------------
+ 83 years 3 mons
+(1 row)
+
+-- test SQL-spec syntaxes for restricted field sets
+SELECT interval '1' year;
+ interval 
+----------
+ 1 year
+(1 row)
+
+SELECT interval '2' month;
+ interval 
+----------
+ 2 mons
+(1 row)
+
+SELECT interval '3' day;
+ interval 
+----------
+ 3 days
+(1 row)
+
+SELECT interval '4' hour;
+ interval 
+----------
+ 04:00:00
+(1 row)
+
+SELECT interval '5' minute;
+ interval 
+----------
+ 00:05:00
+(1 row)
+
+SELECT interval '6' second;
+ interval 
+----------
+ 00:00:06
+(1 row)
+
+SELECT interval '1' year to month;
+ interval 
+----------
+ 1 mon
+(1 row)
+
+SELECT interval '1-2' year to month;
+   interval    
+---------------
+ 1 year 2 mons
+(1 row)
+
+SELECT interval '1 2' day to hour;
+    interval    
+----------------
+ 1 day 02:00:00
+(1 row)
+
+SELECT interval '1 2:03' day to hour;
+    interval    
+----------------
+ 1 day 02:00:00
+(1 row)
+
+SELECT interval '1 2:03:04' day to hour;
+    interval    
+----------------
+ 1 day 02:00:00
+(1 row)
+
+SELECT interval '1 2' day to minute;
+ERROR:  invalid input syntax for type interval: "1 2"
+LINE 1: SELECT interval '1 2' day to minute;
+                        ^
+SELECT interval '1 2:03' day to minute;
+    interval    
+----------------
+ 1 day 02:03:00
+(1 row)
+
+SELECT interval '1 2:03:04' day to minute;
+    interval    
+----------------
+ 1 day 02:03:00
+(1 row)
+
+SELECT interval '1 2' day to second;
+ERROR:  invalid input syntax for type interval: "1 2"
+LINE 1: SELECT interval '1 2' day to second;
+                        ^
+SELECT interval '1 2:03' day to second;
+    interval    
+----------------
+ 1 day 02:03:00
+(1 row)
+
+SELECT interval '1 2:03:04' day to second;
+    interval    
+----------------
+ 1 day 02:03:04
+(1 row)
+
+SELECT interval '1 2' hour to minute;
+ERROR:  invalid input syntax for type interval: "1 2"
+LINE 1: SELECT interval '1 2' hour to minute;
+                        ^
+SELECT interval '1 2:03' hour to minute;
+    interval    
+----------------
+ 1 day 02:03:00
+(1 row)
+
+SELECT interval '1 2:03:04' hour to minute;
+    interval    
+----------------
+ 1 day 02:03:00
+(1 row)
+
+SELECT interval '1 2' hour to second;
+ERROR:  invalid input syntax for type interval: "1 2"
+LINE 1: SELECT interval '1 2' hour to second;
+                        ^
+SELECT interval '1 2:03' hour to second;
+    interval    
+----------------
+ 1 day 02:03:00
+(1 row)
+
+SELECT interval '1 2:03:04' hour to second;
+    interval    
+----------------
+ 1 day 02:03:04
+(1 row)
+
+SELECT interval '1 2' minute to second;
+ERROR:  invalid input syntax for type interval: "1 2"
+LINE 1: SELECT interval '1 2' minute to second;
+                        ^
+SELECT interval '1 2:03' minute to second;
+    interval    
+----------------
+ 1 day 00:02:03
+(1 row)
+
+SELECT interval '1 2:03:04' minute to second;
+    interval    
+----------------
+ 1 day 02:03:04
+(1 row)
+
+SELECT interval '1 +2:03' minute to second;
+    interval    
+----------------
+ 1 day 00:02:03
+(1 row)
+
+SELECT interval '1 +2:03:04' minute to second;
+    interval    
+----------------
+ 1 day 02:03:04
+(1 row)
+
+SELECT interval '1 -2:03' minute to second;
+    interval     
+-----------------
+ 1 day -00:02:03
+(1 row)
+
+SELECT interval '1 -2:03:04' minute to second;
+    interval     
+-----------------
+ 1 day -02:03:04
+(1 row)
+
+SELECT interval '123 11' day to hour; -- ok
+     interval      
+-------------------
+ 123 days 11:00:00
+(1 row)
+
+SELECT interval '123 11' day; -- not ok
+ERROR:  invalid input syntax for type interval: "123 11"
+LINE 1: SELECT interval '123 11' day;
+                        ^
+SELECT interval '123 11'; -- not ok, too ambiguous
+ERROR:  invalid input syntax for type interval: "123 11"
+LINE 1: SELECT interval '123 11';
+                        ^
+SELECT interval '123 2:03 -2:04'; -- not ok, redundant hh:mm fields
+ERROR:  invalid input syntax for type interval: "123 2:03 -2:04"
+LINE 1: SELECT interval '123 2:03 -2:04';
+                        ^
+-- test syntaxes for restricted precision
+SELECT interval(0) '1 day 01:23:45.6789';
+    interval    
+----------------
+ 1 day 01:23:46
+(1 row)
+
+SELECT interval(2) '1 day 01:23:45.6789';
+     interval      
+-------------------
+ 1 day 01:23:45.68
+(1 row)
+
+SELECT interval '12:34.5678' minute to second(2);  -- per SQL spec
+  interval   
+-------------
+ 00:12:34.57
+(1 row)
+
+SELECT interval '1.234' second;
+   interval   
+--------------
+ 00:00:01.234
+(1 row)
+
+SELECT interval '1.234' second(2);
+  interval   
+-------------
+ 00:00:01.23
+(1 row)
+
+SELECT interval '1 2.345' day to second(2);
+ERROR:  invalid input syntax for type interval: "1 2.345"
+LINE 1: SELECT interval '1 2.345' day to second(2);
+                        ^
+SELECT interval '1 2:03' day to second(2);
+    interval    
+----------------
+ 1 day 02:03:00
+(1 row)
+
+SELECT interval '1 2:03.4567' day to second(2);
+     interval      
+-------------------
+ 1 day 00:02:03.46
+(1 row)
+
+SELECT interval '1 2:03:04.5678' day to second(2);
+     interval      
+-------------------
+ 1 day 02:03:04.57
+(1 row)
+
+SELECT interval '1 2.345' hour to second(2);
+ERROR:  invalid input syntax for type interval: "1 2.345"
+LINE 1: SELECT interval '1 2.345' hour to second(2);
+                        ^
+SELECT interval '1 2:03.45678' hour to second(2);
+     interval      
+-------------------
+ 1 day 00:02:03.46
+(1 row)
+
+SELECT interval '1 2:03:04.5678' hour to second(2);
+     interval      
+-------------------
+ 1 day 02:03:04.57
+(1 row)
+
+SELECT interval '1 2.3456' minute to second(2);
+ERROR:  invalid input syntax for type interval: "1 2.3456"
+LINE 1: SELECT interval '1 2.3456' minute to second(2);
+                        ^
+SELECT interval '1 2:03.5678' minute to second(2);
+     interval      
+-------------------
+ 1 day 00:02:03.57
+(1 row)
+
+SELECT interval '1 2:03:04.5678' minute to second(2);
+     interval      
+-------------------
+ 1 day 02:03:04.57
+(1 row)
+
+-- test casting to restricted precision (bug #14479)
+SELECT f1, f1::INTERVAL DAY TO MINUTE AS "minutes",
+  (f1 + INTERVAL '1 month')::INTERVAL MONTH::INTERVAL YEAR AS "years"
+  FROM interval_tbl;
+       f1        |     minutes     |  years   
+-----------------+-----------------+----------
+ 00:01:00        | 00:01:00        | 00:00:00
+ 05:00:00        | 05:00:00        | 00:00:00
+ 10 days         | 10 days         | 00:00:00
+ 34 years        | 34 years        | 34 years
+ 3 mons          | 3 mons          | 00:00:00
+ -00:00:14       | 00:00:00        | 00:00:00
+ 1 day 02:03:04  | 1 day 02:03:00  | 00:00:00
+ 6 years         | 6 years         | 6 years
+ 5 mons          | 5 mons          | 00:00:00
+ 5 mons 12:00:00 | 5 mons 12:00:00 | 00:00:00
+(10 rows)
+
+-- test inputting and outputting SQL standard interval literals
+SET IntervalStyle TO sql_standard;
+SELECT  interval '0'                       AS "zero",
+        interval '1-2' year to month       AS "year-month",
+        interval '1 2:03:04' day to second AS "day-time",
+        - interval '1-2'                   AS "negative year-month",
+        - interval '1 2:03:04'             AS "negative day-time";
+ zero | year-month | day-time  | negative year-month | negative day-time 
+------+------------+-----------+---------------------+-------------------
+ 0    | 1-2        | 1 2:03:04 | -1-2                | -1 2:03:04
+(1 row)
+
+-- test input of some not-quite-standard interval values in the sql style
+SET IntervalStyle TO postgres;
+SELECT  interval '+1 -1:00:00',
+        interval '-1 +1:00:00',
+        interval '+1-2 -3 +4:05:06.789',
+        interval '-1-2 +3 -4:05:06.789';
+    interval     |     interval      |              interval               |                interval                
+-----------------+-------------------+-------------------------------------+----------------------------------------
+ 1 day -01:00:00 | -1 days +01:00:00 | 1 year 2 mons -3 days +04:05:06.789 | -1 years -2 mons +3 days -04:05:06.789
+(1 row)
+
+-- test output of couple non-standard interval values in the sql style
+SET IntervalStyle TO sql_standard;
+SELECT  interval '1 day -1 hours',
+        interval '-1 days +1 hours',
+        interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds',
+        - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds';
+     interval     |     interval     |       interval       |       ?column?       
+------------------+------------------+----------------------+----------------------
+ +0-0 +1 -1:00:00 | +0-0 -1 +1:00:00 | +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789
+(1 row)
+
+-- test outputting iso8601 intervals
+SET IntervalStyle to iso_8601;
+select  interval '0'                                AS "zero",
+        interval '1-2'                              AS "a year 2 months",
+        interval '1 2:03:04'                        AS "a bit over a day",
+        interval '2:03:04.45679'                    AS "a bit over 2 hours",
+        (interval '1-2' + interval '3 4:05:06.7')   AS "all fields",
+        (interval '1-2' - interval '3 4:05:06.7')   AS "mixed sign",
+        (- interval '1-2' + interval '3 4:05:06.7') AS "negative";
+ zero | a year 2 months | a bit over a day | a bit over 2 hours |    all fields    |      mixed sign      |      negative      
+------+-----------------+------------------+--------------------+------------------+----------------------+--------------------
+ PT0S | P1Y2M           | P1DT2H3M4S       | PT2H3M4.45679S     | P1Y2M3DT4H5M6.7S | P1Y2M-3DT-4H-5M-6.7S | P-1Y-2M3DT4H5M6.7S
+(1 row)
+
+-- test inputting ISO 8601 4.4.2.1 "Format With Time Unit Designators"
+SET IntervalStyle to sql_standard;
+select  interval 'P0Y'                    AS "zero",
+        interval 'P1Y2M'                  AS "a year 2 months",
+        interval 'P1W'                    AS "a week",
+        interval 'P1DT2H3M4S'             AS "a bit over a day",
+        interval 'P1Y2M3DT4H5M6.7S'       AS "all fields",
+        interval 'P-1Y-2M-3DT-4H-5M-6.7S' AS "negative",
+        interval 'PT-0.1S'                AS "fractional second";
+ zero | a year 2 months |  a week   | a bit over a day |     all fields     |      negative      | fractional second 
+------+-----------------+-----------+------------------+--------------------+--------------------+-------------------
+ 0    | 1-2             | 7 0:00:00 | 1 2:03:04        | +1-2 +3 +4:05:06.7 | -1-2 -3 -4:05:06.7 | -0:00:00.1
+(1 row)
+
+-- test inputting ISO 8601 4.4.2.2 "Alternative Format"
+SET IntervalStyle to postgres;
+select  interval 'P00021015T103020'       AS "ISO8601 Basic Format",
+        interval 'P0002-10-15T10:30:20'   AS "ISO8601 Extended Format";
+       ISO8601 Basic Format       |     ISO8601 Extended Format      
+----------------------------------+----------------------------------
+ 2 years 10 mons 15 days 10:30:20 | 2 years 10 mons 15 days 10:30:20
+(1 row)
+
+-- Make sure optional ISO8601 alternative format fields are optional.
+select  interval 'P0002'                  AS "year only",
+        interval 'P0002-10'               AS "year month",
+        interval 'P0002-10-15'            AS "year month day",
+        interval 'P0002T1S'               AS "year only plus time",
+        interval 'P0002-10T1S'            AS "year month plus time",
+        interval 'P0002-10-15T1S'         AS "year month day plus time",
+        interval 'PT10'                   AS "hour only",
+        interval 'PT10:30'                AS "hour minute";
+ year only |   year month    |     year month day      | year only plus time |   year month plus time   |     year month day plus time     | hour only | hour minute 
+-----------+-----------------+-------------------------+---------------------+--------------------------+----------------------------------+-----------+-------------
+ 2 years   | 2 years 10 mons | 2 years 10 mons 15 days | 2 years 00:00:01    | 2 years 10 mons 00:00:01 | 2 years 10 mons 15 days 00:00:01 | 10:00:00  | 10:30:00
+(1 row)
+
+-- test a couple rounding cases that changed since 8.3 w/ HAVE_INT64_TIMESTAMP.
+SET IntervalStyle to postgres_verbose;
+select interval '-10 mons -3 days +03:55:06.70';
+                     interval                     
+--------------------------------------------------
+ @ 10 mons 3 days -3 hours -55 mins -6.7 secs ago
+(1 row)
+
+select interval '1 year 2 mons 3 days 04:05:06.699999';
+                      interval                       
+-----------------------------------------------------
+ @ 1 year 2 mons 3 days 4 hours 5 mins 6.699999 secs
+(1 row)
+
+select interval '0:0:0.7', interval '@ 0.70 secs', interval '0.7 seconds';
+  interval  |  interval  |  interval  
+------------+------------+------------
+ @ 0.7 secs | @ 0.7 secs | @ 0.7 secs
+(1 row)
+
+-- check that '30 days' equals '1 month' according to the hash function
+select '30 days'::interval = '1 month'::interval as t;
+ t 
+---
+ t
+(1 row)
+
+select interval_hash('30 days'::interval) = interval_hash('1 month'::interval) as t;
+ t 
+---
+ t
+(1 row)
+
+-- numeric constructor
+select make_interval(years := 2);
+ make_interval 
+---------------
+ @ 2 years
+(1 row)
+
+select make_interval(years := 1, months := 6);
+  make_interval  
+-----------------
+ @ 1 year 6 mons
+(1 row)
+
+select make_interval(years := 1, months := -1, weeks := 5, days := -7, hours := 25, mins := -180);
+       make_interval        
+----------------------------
+ @ 11 mons 28 days 22 hours
+(1 row)
+
+select make_interval() = make_interval(years := 0, months := 0, weeks := 0, days := 0, mins := 0, secs := 0.0);
+ ?column? 
+----------
+ t
+(1 row)
+
+select make_interval(hours := -2, mins := -10, secs := -25.3);
+          make_interval          
+---------------------------------
+ @ 2 hours 10 mins 25.3 secs ago
+(1 row)
+
+select make_interval(years := 'inf'::float::int);
+ERROR:  integer out of range
+select make_interval(months := 'NaN'::float::int);
+ERROR:  integer out of range
+select make_interval(secs := 'inf');
+ERROR:  interval out of range
+select make_interval(secs := 'NaN');
+ERROR:  interval out of range
+select make_interval(secs := 7e12);
+           make_interval            
+------------------------------------
+ @ 1944444444 hours 26 mins 40 secs
+(1 row)
+
+--
+-- test EXTRACT
+--
+SELECT f1,
+    EXTRACT(MICROSECOND FROM f1) AS MICROSECOND,
+    EXTRACT(MILLISECOND FROM f1) AS MILLISECOND,
+    EXTRACT(SECOND FROM f1) AS SECOND,
+    EXTRACT(MINUTE FROM f1) AS MINUTE,
+    EXTRACT(HOUR FROM f1) AS HOUR,
+    EXTRACT(DAY FROM f1) AS DAY,
+    EXTRACT(MONTH FROM f1) AS MONTH,
+    EXTRACT(QUARTER FROM f1) AS QUARTER,
+    EXTRACT(YEAR FROM f1) AS YEAR,
+    EXTRACT(DECADE FROM f1) AS DECADE,
+    EXTRACT(CENTURY FROM f1) AS CENTURY,
+    EXTRACT(MILLENNIUM FROM f1) AS MILLENNIUM,
+    EXTRACT(EPOCH FROM f1) AS EPOCH
+    FROM INTERVAL_TBL;
+              f1               | microsecond | millisecond |   second   | minute | hour | day | month | quarter | year | decade | century | millennium |       epoch       
+-------------------------------+-------------+-------------+------------+--------+------+-----+-------+---------+------+--------+---------+------------+-------------------
+ @ 1 min                       |           0 |       0.000 |   0.000000 |      1 |    0 |   0 |     0 |       1 |    0 |      0 |       0 |          0 |         60.000000
+ @ 5 hours                     |           0 |       0.000 |   0.000000 |      0 |    5 |   0 |     0 |       1 |    0 |      0 |       0 |          0 |      18000.000000
+ @ 10 days                     |           0 |       0.000 |   0.000000 |      0 |    0 |  10 |     0 |       1 |    0 |      0 |       0 |          0 |     864000.000000
+ @ 34 years                    |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     0 |       1 |   34 |      3 |       0 |          0 | 1072958400.000000
+ @ 3 mons                      |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     3 |       2 |    0 |      0 |       0 |          0 |    7776000.000000
+ @ 14 secs ago                 |   -14000000 |  -14000.000 | -14.000000 |      0 |    0 |   0 |     0 |       1 |    0 |      0 |       0 |          0 |        -14.000000
+ @ 1 day 2 hours 3 mins 4 secs |     4000000 |    4000.000 |   4.000000 |      3 |    2 |   1 |     0 |       1 |    0 |      0 |       0 |          0 |      93784.000000
+ @ 6 years                     |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     0 |       1 |    6 |      0 |       0 |          0 |  189345600.000000
+ @ 5 mons                      |           0 |       0.000 |   0.000000 |      0 |    0 |   0 |     5 |       2 |    0 |      0 |       0 |          0 |   12960000.000000
+ @ 5 mons 12 hours             |           0 |       0.000 |   0.000000 |      0 |   12 |   0 |     5 |       2 |    0 |      0 |       0 |          0 |   13003200.000000
+(10 rows)
+
+SELECT EXTRACT(FORTNIGHT FROM INTERVAL '2 days');  -- error
+ERROR:  interval units "fortnight" not recognized
+SELECT EXTRACT(TIMEZONE FROM INTERVAL '2 days');  -- error
+ERROR:  interval units "timezone" not supported
+SELECT EXTRACT(DECADE FROM INTERVAL '100 y');
+ extract 
+---------
+      10
+(1 row)
+
+SELECT EXTRACT(DECADE FROM INTERVAL '99 y');
+ extract 
+---------
+       9
+(1 row)
+
+SELECT EXTRACT(DECADE FROM INTERVAL '-99 y');
+ extract 
+---------
+      -9
+(1 row)
+
+SELECT EXTRACT(DECADE FROM INTERVAL '-100 y');
+ extract 
+---------
+     -10
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '100 y');
+ extract 
+---------
+       1
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '99 y');
+ extract 
+---------
+       0
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y');
+ extract 
+---------
+       0
+(1 row)
+
+SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y');
+ extract 
+---------
+      -1
+(1 row)
+
+-- date_part implementation is mostly the same as extract, so only
+-- test a few cases for additional coverage.
+SELECT f1,
+    date_part('microsecond', f1) AS microsecond,
+    date_part('millisecond', f1) AS millisecond,
+    date_part('second', f1) AS second,
+    date_part('epoch', f1) AS epoch
+    FROM INTERVAL_TBL;
+              f1               | microsecond | millisecond | second |   epoch    
+-------------------------------+-------------+-------------+--------+------------
+ @ 1 min                       |           0 |           0 |      0 |         60
+ @ 5 hours                     |           0 |           0 |      0 |      18000
+ @ 10 days                     |           0 |           0 |      0 |     864000
+ @ 34 years                    |           0 |           0 |      0 | 1072958400
+ @ 3 mons                      |           0 |           0 |      0 |    7776000
+ @ 14 secs ago                 |   -14000000 |      -14000 |    -14 |        -14
+ @ 1 day 2 hours 3 mins 4 secs |     4000000 |        4000 |      4 |      93784
+ @ 6 years                     |           0 |           0 |      0 |  189345600
+ @ 5 mons                      |           0 |           0 |      0 |   12960000
+ @ 5 mons 12 hours             |           0 |           0 |      0 |   13003200
+(10 rows)
+
+-- internal overflow test case
+SELECT extract(epoch from interval '1000000000 days');
+        extract        
+-----------------------
+ 86400000000000.000000
+(1 row)
+