1-- 2-- INET 3-- 4 5-- prepare the table... 6 7DROP TABLE INET_TBL; 8CREATE TABLE INET_TBL (c cidr, i inet); 9INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.226/24'); 10INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.0/26', '192.168.1.226'); 11INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/24'); 12INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.0/25'); 13INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/24'); 14INSERT INTO INET_TBL (c, i) VALUES ('192.168.1', '192.168.1.255/25'); 15INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8'); 16INSERT INTO INET_TBL (c, i) VALUES ('10.0.0.0', '10.1.2.3/8'); 17INSERT INTO INET_TBL (c, i) VALUES ('10.1.2.3', '10.1.2.3/32'); 18INSERT INTO INET_TBL (c, i) VALUES ('10.1.2', '10.1.2.3/24'); 19INSERT INTO INET_TBL (c, i) VALUES ('10.1', '10.1.2.3/16'); 20INSERT INTO INET_TBL (c, i) VALUES ('10', '10.1.2.3/8'); 21INSERT INTO INET_TBL (c, i) VALUES ('10', '11.1.2.3/8'); 22INSERT INTO INET_TBL (c, i) VALUES ('10', '9.1.2.3/8'); 23INSERT INTO INET_TBL (c, i) VALUES ('10:23::f1', '10:23::f1/64'); 24INSERT INTO INET_TBL (c, i) VALUES ('10:23::8000/113', '10:23::ffff'); 25INSERT INTO INET_TBL (c, i) VALUES ('::ffff:1.2.3.4', '::4.3.2.1/24'); 26-- check that CIDR rejects invalid input: 27INSERT INTO INET_TBL (c, i) VALUES ('192.168.1.2/30', '192.168.1.226'); 28INSERT INTO INET_TBL (c, i) VALUES ('1234::1234::1234', '::1.2.3.4'); 29-- check that CIDR rejects invalid input when converting from text: 30INSERT INTO INET_TBL (c, i) VALUES (cidr('192.168.1.2/30'), '192.168.1.226'); 31INSERT INTO INET_TBL (c, i) VALUES (cidr('ffff:ffff:ffff:ffff::/24'), '::192.168.1.226'); 32SELECT '' AS ten, c AS cidr, i AS inet FROM INET_TBL; 33 34-- now test some support functions 35 36SELECT '' AS ten, i AS inet, host(i), text(i), family(i) FROM INET_TBL; 37SELECT '' AS ten, c AS cidr, broadcast(c), 38 i AS inet, broadcast(i) FROM INET_TBL; 39SELECT '' AS ten, c AS cidr, network(c) AS "network(cidr)", 40 i AS inet, network(i) AS "network(inet)" FROM INET_TBL; 41SELECT '' AS ten, c AS cidr, masklen(c) AS "masklen(cidr)", 42 i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL; 43 44SELECT '' AS four, c AS cidr, masklen(c) AS "masklen(cidr)", 45 i AS inet, masklen(i) AS "masklen(inet)" FROM INET_TBL 46 WHERE masklen(c) <= 8; 47 48SELECT '' AS six, c AS cidr, i AS inet FROM INET_TBL 49 WHERE c = i; 50 51SELECT '' AS ten, i, c, 52 i < c AS lt, i <= c AS le, i = c AS eq, 53 i >= c AS ge, i > c AS gt, i <> c AS ne, 54 i << c AS sb, i <<= c AS sbe, 55 i >> c AS sup, i >>= c AS spe, 56 i && c AS ovr 57 FROM INET_TBL; 58 59SELECT max(i) AS max, min(i) AS min FROM INET_TBL; 60SELECT max(c) AS max, min(c) AS min FROM INET_TBL; 61 62-- check the conversion to/from text and set_netmask 63SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL; 64 65-- check that btree index works correctly 66CREATE INDEX inet_idx1 ON inet_tbl(i); 67SET enable_seqscan TO off; 68EXPLAIN (COSTS OFF) 69SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr; 70SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr; 71EXPLAIN (COSTS OFF) 72SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr; 73SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr; 74EXPLAIN (COSTS OFF) 75SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >>= i; 76SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >>= i; 77EXPLAIN (COSTS OFF) 78SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >> i; 79SELECT * FROM inet_tbl WHERE '192.168.1.0/24'::cidr >> i; 80SET enable_seqscan TO on; 81DROP INDEX inet_idx1; 82 83-- check that gist index works correctly 84CREATE INDEX inet_idx2 ON inet_tbl using gist (i inet_ops); 85SET enable_seqscan TO off; 86SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 87SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i; 88SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i; 89SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i; 90SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i; 91SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i; 92SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i; 93SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i; 94SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i; 95SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i; 96SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i; 97 98-- test index-only scans 99EXPLAIN (COSTS OFF) 100SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 101SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 102 103SET enable_seqscan TO on; 104DROP INDEX inet_idx2; 105 106-- check that spgist index works correctly 107CREATE INDEX inet_idx3 ON inet_tbl using spgist (i); 108SET enable_seqscan TO off; 109SELECT * FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 110SELECT * FROM inet_tbl WHERE i <<= '192.168.1.0/24'::cidr ORDER BY i; 111SELECT * FROM inet_tbl WHERE i && '192.168.1.0/24'::cidr ORDER BY i; 112SELECT * FROM inet_tbl WHERE i >>= '192.168.1.0/24'::cidr ORDER BY i; 113SELECT * FROM inet_tbl WHERE i >> '192.168.1.0/24'::cidr ORDER BY i; 114SELECT * FROM inet_tbl WHERE i < '192.168.1.0/24'::cidr ORDER BY i; 115SELECT * FROM inet_tbl WHERE i <= '192.168.1.0/24'::cidr ORDER BY i; 116SELECT * FROM inet_tbl WHERE i = '192.168.1.0/24'::cidr ORDER BY i; 117SELECT * FROM inet_tbl WHERE i >= '192.168.1.0/24'::cidr ORDER BY i; 118SELECT * FROM inet_tbl WHERE i > '192.168.1.0/24'::cidr ORDER BY i; 119SELECT * FROM inet_tbl WHERE i <> '192.168.1.0/24'::cidr ORDER BY i; 120 121-- test index-only scans 122EXPLAIN (COSTS OFF) 123SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 124SELECT i FROM inet_tbl WHERE i << '192.168.1.0/24'::cidr ORDER BY i; 125 126SET enable_seqscan TO on; 127DROP INDEX inet_idx3; 128 129-- simple tests of inet boolean and arithmetic operators 130SELECT i, ~i AS "~i" FROM inet_tbl; 131SELECT i, c, i & c AS "and" FROM inet_tbl; 132SELECT i, c, i | c AS "or" FROM inet_tbl; 133SELECT i, i + 500 AS "i+500" FROM inet_tbl; 134SELECT i, i - 500 AS "i-500" FROM inet_tbl; 135SELECT i, c, i - c AS "minus" FROM inet_tbl; 136SELECT '127.0.0.1'::inet + 257; 137SELECT ('127.0.0.1'::inet + 257) - 257; 138SELECT '127::1'::inet + 257; 139SELECT ('127::1'::inet + 257) - 257; 140SELECT '127.0.0.2'::inet - ('127.0.0.2'::inet + 500); 141SELECT '127.0.0.2'::inet - ('127.0.0.2'::inet - 500); 142SELECT '127::2'::inet - ('127::2'::inet + 500); 143SELECT '127::2'::inet - ('127::2'::inet - 500); 144-- these should give overflow errors: 145SELECT '127.0.0.1'::inet + 10000000000; 146SELECT '127.0.0.1'::inet - 10000000000; 147SELECT '126::1'::inet - '127::2'::inet; 148SELECT '127::1'::inet - '126::2'::inet; 149-- but not these 150SELECT '127::1'::inet + 10000000000; 151SELECT '127::1'::inet - '127::2'::inet; 152 153-- insert one more row with addressed from different families 154INSERT INTO INET_TBL (c, i) VALUES ('10', '10::/8'); 155-- now, this one should fail 156SELECT inet_merge(c, i) FROM INET_TBL; 157-- fix it by inet_same_family() condition 158SELECT inet_merge(c, i) FROM INET_TBL WHERE inet_same_family(c, i); 159 160-- Test inet sortsupport with a variety of boundary inputs: 161SELECT a FROM (VALUES 162 ('0.0.0.0/0'::inet), 163 ('0.0.0.0/1'::inet), 164 ('0.0.0.0/32'::inet), 165 ('0.0.0.1/0'::inet), 166 ('0.0.0.1/1'::inet), 167 ('127.126.127.127/0'::inet), 168 ('127.127.127.127/0'::inet), 169 ('127.128.127.127/0'::inet), 170 ('192.168.1.0/24'::inet), 171 ('192.168.1.0/25'::inet), 172 ('192.168.1.1/23'::inet), 173 ('192.168.1.1/5'::inet), 174 ('192.168.1.1/6'::inet), 175 ('192.168.1.1/25'::inet), 176 ('192.168.1.2/25'::inet), 177 ('192.168.1.1/26'::inet), 178 ('192.168.1.2/26'::inet), 179 ('192.168.1.2/23'::inet), 180 ('192.168.1.255/5'::inet), 181 ('192.168.1.255/6'::inet), 182 ('192.168.1.3/1'::inet), 183 ('192.168.1.3/23'::inet), 184 ('192.168.1.4/0'::inet), 185 ('192.168.1.5/0'::inet), 186 ('255.0.0.0/0'::inet), 187 ('255.1.0.0/0'::inet), 188 ('255.2.0.0/0'::inet), 189 ('255.255.000.000/0'::inet), 190 ('255.255.000.000/0'::inet), 191 ('255.255.000.000/15'::inet), 192 ('255.255.000.000/16'::inet), 193 ('255.255.255.254/32'::inet), 194 ('255.255.255.000/32'::inet), 195 ('255.255.255.001/31'::inet), 196 ('255.255.255.002/31'::inet), 197 ('255.255.255.003/31'::inet), 198 ('255.255.255.003/32'::inet), 199 ('255.255.255.001/32'::inet), 200 ('255.255.255.255/0'::inet), 201 ('255.255.255.255/0'::inet), 202 ('255.255.255.255/0'::inet), 203 ('255.255.255.255/1'::inet), 204 ('255.255.255.255/16'::inet), 205 ('255.255.255.255/16'::inet), 206 ('255.255.255.255/31'::inet), 207 ('255.255.255.255/32'::inet), 208 ('255.255.255.253/32'::inet), 209 ('255.255.255.252/32'::inet), 210 ('255.3.0.0/0'::inet), 211 ('0000:0000:0000:0000:0000:0000:0000:0000/0'::inet), 212 ('0000:0000:0000:0000:0000:0000:0000:0000/128'::inet), 213 ('0000:0000:0000:0000:0000:0000:0000:0001/128'::inet), 214 ('10:23::f1/64'::inet), 215 ('10:23::f1/65'::inet), 216 ('10:23::ffff'::inet), 217 ('127::1'::inet), 218 ('127::2'::inet), 219 ('8000:0000:0000:0000:0000:0000:0000:0000/1'::inet), 220 ('::1:ffff:ffff:ffff:ffff/128'::inet), 221 ('::2:ffff:ffff:ffff:ffff/128'::inet), 222 ('::4:3:2:0/24'::inet), 223 ('::4:3:2:1/24'::inet), 224 ('::4:3:2:2/24'::inet), 225 ('ffff:83e7:f118:57dc:6093:6d92:689d:58cf/70'::inet), 226 ('ffff:84b0:4775:536e:c3ed:7116:a6d6:34f0/44'::inet), 227 ('ffff:8566:f84:5867:47f1:7867:d2ba:8a1a/69'::inet), 228 ('ffff:8883:f028:7d2:4d68:d510:7d6b:ac43/73'::inet), 229 ('ffff:8ae8:7c14:65b3:196:8e4a:89ae:fb30/89'::inet), 230 ('ffff:8dd0:646:694c:7c16:7e35:6a26:171/104'::inet), 231 ('ffff:8eef:cbf:700:eda3:ae32:f4b4:318b/121'::inet), 232 ('ffff:90e7:e744:664:a93:8efe:1f25:7663/122'::inet), 233 ('ffff:9597:c69c:8b24:57a:8639:ec78:6026/111'::inet), 234 ('ffff:9e86:79ea:f16e:df31:8e4d:7783:532e/88'::inet), 235 ('ffff:a0c7:82d3:24de:f762:6e1f:316d:3fb2/23'::inet), 236 ('ffff:fffa:ffff:ffff:ffff:ffff:ffff:ffff/0'::inet), 237 ('ffff:fffb:ffff:ffff:ffff:ffff:ffff:ffff/0'::inet), 238 ('ffff:fffc:ffff:ffff:ffff:ffff:ffff:ffff/0'::inet), 239 ('ffff:fffd:ffff:ffff:ffff:ffff:ffff:ffff/0'::inet), 240 ('ffff:fffe:ffff:ffff:ffff:ffff:ffff:ffff/0'::inet), 241 ('ffff:ffff:ffff:fffa:ffff:ffff:ffff:ffff/0'::inet), 242 ('ffff:ffff:ffff:fffb:ffff:ffff:ffff:ffff/0'::inet), 243 ('ffff:ffff:ffff:fffc:ffff:ffff:ffff:ffff/0'::inet), 244 ('ffff:ffff:ffff:fffd::/128'::inet), 245 ('ffff:ffff:ffff:fffd:ffff:ffff:ffff:ffff/0'::inet), 246 ('ffff:ffff:ffff:fffe::/128'::inet), 247 ('ffff:ffff:ffff:fffe:ffff:ffff:ffff:ffff/0'::inet), 248 ('ffff:ffff:ffff:ffff:4:3:2:0/24'::inet), 249 ('ffff:ffff:ffff:ffff:4:3:2:1/24'::inet), 250 ('ffff:ffff:ffff:ffff:4:3:2:2/24'::inet), 251 ('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff/0'::inet), 252 ('ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff/128'::inet) 253) AS i(a) ORDER BY a; 254