--
-- Test loading and reading different data types to/from cstore_fdw foreign tables.
--
-- Settings to make the result deterministic
SET datestyle = "ISO, YMD";
SET timezone to 'GMT';
SET intervalstyle TO 'POSTGRES_VERBOSE';
-- Test array types
CREATE FOREIGN TABLE test_array_types (int_array int[], bigint_array bigint[],
	text_array text[]) SERVER cstore_server
	OPTIONS(filename '@abs_srcdir@/data/array_types.cstore');
COPY test_array_types FROM '@abs_srcdir@/data/array_types.csv' WITH CSV;
SELECT * FROM test_array_types;
        int_array         |                bigint_array                | text_array 
--------------------------+--------------------------------------------+------------
 {1,2,3}                  | {1,2,3}                                    | {a,b,c}
 {}                       | {}                                         | {}
 {-2147483648,2147483647} | {-9223372036854775808,9223372036854775807} | {""}
(3 rows)

-- Test date/time types
CREATE FOREIGN TABLE test_datetime_types (timestamp timestamp,
	timestamp_with_timezone timestamp with time zone, date date, time time,
	interval interval) SERVER cstore_server
	OPTIONS(filename '@abs_srcdir@/data/datetime_types.cstore');
COPY test_datetime_types FROM '@abs_srcdir@/data/datetime_types.csv' WITH CSV;
SELECT * FROM test_datetime_types;
      timestamp      | timestamp_with_timezone |    date    |   time   | interval  
---------------------+-------------------------+------------+----------+-----------
 2000-01-02 04:05:06 | 1999-01-08 12:05:06+00  | 2000-01-02 | 04:05:06 | @ 4 hours
 1970-01-01 00:00:00 | infinity                | -infinity  | 00:00:00 | @ 0
(2 rows)

-- Test enum and composite types
CREATE TYPE enum_type AS ENUM ('a', 'b', 'c');
CREATE TYPE composite_type AS (a int, b text);
CREATE FOREIGN TABLE test_enum_and_composite_types (enum enum_type,
	composite composite_type) SERVER cstore_server
	OPTIONS(filename '@abs_srcdir@/data/enum_and_composite_types.cstore');
COPY test_enum_and_composite_types FROM
	'@abs_srcdir@/data/enum_and_composite_types.csv' WITH CSV;
SELECT * FROM test_enum_and_composite_types;
 enum | composite 
------+-----------
 a    | (2,b)
 b    | (3,c)
(2 rows)

-- Test range types
CREATE FOREIGN TABLE test_range_types (int4range int4range, int8range int8range,
	numrange numrange, tsrange tsrange) SERVER cstore_server
	OPTIONS(filename '@abs_srcdir@/data/range_types.cstore');
COPY test_range_types FROM '@abs_srcdir@/data/range_types.csv' WITH CSV;
SELECT * FROM test_range_types;
 int4range | int8range | numrange |                    tsrange                    
-----------+-----------+----------+-----------------------------------------------
 [1,3)     | [1,3)     | [1,3)    | ["2000-01-02 00:30:00","2010-02-03 12:30:00")
 empty     | [1,)      | (,)      | empty
(2 rows)

-- Test other types
CREATE FOREIGN TABLE test_other_types (bool boolean, bytea bytea, money money,
	inet inet, bitstring bit varying(5), uuid uuid, json json) SERVER cstore_server
	OPTIONS(filename '@abs_srcdir@/data/other_types.cstore');
COPY test_other_types FROM '@abs_srcdir@/data/other_types.csv' WITH CSV;
SELECT * FROM test_other_types;
 bool |   bytea    | money |    inet     | bitstring |                 uuid                 |       json       
------+------------+-------+-------------+-----------+--------------------------------------+------------------
 f    | \xdeadbeef | $1.00 | 192.168.1.2 | 10101     | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | {"key": "value"}
 t    | \xcdb0     | $1.50 | 127.0.0.1   |           | a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 | []
(2 rows)

-- Test null values
CREATE FOREIGN TABLE test_null_values (a int, b int[], c composite_type)
	SERVER cstore_server
	OPTIONS(filename '@abs_srcdir@/data/null_values.cstore');
COPY test_null_values FROM '@abs_srcdir@/data/null_values.csv' WITH CSV;
SELECT * FROM test_null_values;
 a |   b    |  c  
---+--------+-----
   | {NULL} | (,)
   |        | 
(2 rows)