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