1-- 2-- Testing data types without comparison operators 3-- If a data type doesn't have comparison operators, we should store NULL for min/max values 4-- Verify that (1) min/max entries in columnar.chunk is NULL as expected 5-- (2) we can run queries which has equality conditions in WHERE clause for that column with correct results 6-- 7-- varchar 8CREATE TABLE test_varchar (a varchar) USING columnar; 9INSERT INTO test_varchar VALUES ('Hello'); 10SELECT minimum_value, maximum_value FROM columnar.chunk; 11 minimum_value | maximum_value 12--------------------------------------------------------------------- 13 | 14(1 row) 15 16SELECT * FROM test_varchar WHERE a = 'Hello'; 17 a 18--------------------------------------------------------------------- 19 Hello 20(1 row) 21 22DROP TABLE test_varchar; 23-- cidr 24CREATE TABLE test_cidr (a cidr) USING columnar; 25INSERT INTO test_cidr VALUES ('192.168.100.128/25'); 26SELECT minimum_value, maximum_value FROM columnar.chunk; 27 minimum_value | maximum_value 28--------------------------------------------------------------------- 29 | 30(1 row) 31 32SELECT * FROM test_cidr WHERE a = '192.168.100.128/25'; 33 a 34--------------------------------------------------------------------- 35 192.168.100.128/25 36(1 row) 37 38DROP TABLE test_cidr; 39-- json 40CREATE TABLE test_json (a json) USING columnar; 41INSERT INTO test_json VALUES ('5'::json); 42SELECT minimum_value, maximum_value FROM columnar.chunk; 43 minimum_value | maximum_value 44--------------------------------------------------------------------- 45 | 46(1 row) 47 48SELECT * FROM test_json WHERE a::text = '5'::json::text; 49 a 50--------------------------------------------------------------------- 51 5 52(1 row) 53 54DROP TABLE test_json; 55-- line 56CREATE TABLE test_line (a line) USING columnar; 57INSERT INTO test_line VALUES ('{1, 2, 3}'); 58SELECT minimum_value, maximum_value FROM columnar.chunk; 59 minimum_value | maximum_value 60--------------------------------------------------------------------- 61 | 62(1 row) 63 64SELECT * FROM test_line WHERE a = '{1, 2, 3}'; 65 a 66--------------------------------------------------------------------- 67 {1,2,3} 68(1 row) 69 70DROP TABLE test_line; 71-- lseg 72CREATE TABLE test_lseg (a lseg) USING columnar; 73INSERT INTO test_lseg VALUES ('( 1 , 2 ) , ( 3 , 4 )'); 74SELECT minimum_value, maximum_value FROM columnar.chunk; 75 minimum_value | maximum_value 76--------------------------------------------------------------------- 77 | 78(1 row) 79 80SELECT * FROM test_lseg WHERE a = '( 1 , 2 ) , ( 3 , 4 )'; 81 a 82--------------------------------------------------------------------- 83 [(1,2),(3,4)] 84(1 row) 85 86DROP TABLE test_lseg; 87-- path 88CREATE TABLE test_path (a path) USING columnar; 89INSERT INTO test_path VALUES ('( 1 , 2 ) , ( 3 , 4 ) , ( 5 , 6 )'); 90SELECT minimum_value, maximum_value FROM columnar.chunk; 91 minimum_value | maximum_value 92--------------------------------------------------------------------- 93 | 94(1 row) 95 96SELECT * FROM test_path WHERE a = '( 1 , 2 ) , ( 3 , 4 ) , ( 5 , 6 )'; 97 a 98--------------------------------------------------------------------- 99 ((1,2),(3,4),(5,6)) 100(1 row) 101 102DROP TABLE test_path; 103-- txid_snapshot 104CREATE TABLE test_txid_snapshot (a txid_snapshot) USING columnar; 105INSERT INTO test_txid_snapshot VALUES ('10:20:10,14,15'); 106SELECT minimum_value, maximum_value FROM columnar.chunk; 107 minimum_value | maximum_value 108--------------------------------------------------------------------- 109 | 110(1 row) 111 112SELECT * FROM test_txid_snapshot WHERE a::text = '10:20:10,14,15'::txid_snapshot::text; 113 a 114--------------------------------------------------------------------- 115 10:20:10,14,15 116(1 row) 117 118DROP TABLE test_txid_snapshot; 119-- xml 120CREATE TABLE test_xml (a xml) USING columnar; 121INSERT INTO test_xml VALUES ('<foo>bar</foo>'::xml); 122SELECT minimum_value, maximum_value FROM columnar.chunk; 123 minimum_value | maximum_value 124--------------------------------------------------------------------- 125 | 126(1 row) 127 128SELECT * FROM test_xml WHERE a::text = '<foo>bar</foo>'::xml::text; 129 a 130--------------------------------------------------------------------- 131 <foo>bar</foo> 132(1 row) 133 134DROP TABLE test_xml; 135-- user defined 136CREATE TYPE user_defined_color AS ENUM ('red', 'orange', 'yellow', 137 'green', 'blue', 'purple'); 138CREATE TABLE test_user_defined_color (a user_defined_color) USING columnar; 139INSERT INTO test_user_defined_color VALUES ('red'); 140SELECT minimum_value, maximum_value FROM columnar.chunk; 141 minimum_value | maximum_value 142--------------------------------------------------------------------- 143 | 144(1 row) 145 146SELECT * FROM test_user_defined_color WHERE a = 'red'; 147 a 148--------------------------------------------------------------------- 149 red 150(1 row) 151 152DROP TABLE test_user_defined_color; 153DROP TYPE user_defined_color; 154SHOW server_version \gset 155SELECT substring(:'server_version', '\d+')::int > 12 AS server_version_above_twelve 156\gset 157\if :server_version_above_twelve 158\else 159\q