1-- 2-- macaddr8 3-- 4 5-- test various cases of valid and invalid input 6-- valid 7SELECT '08:00:2b:01:02:03 '::macaddr8; 8SELECT ' 08:00:2b:01:02:03 '::macaddr8; 9SELECT ' 08:00:2b:01:02:03'::macaddr8; 10SELECT '08:00:2b:01:02:03:04:05 '::macaddr8; 11SELECT ' 08:00:2b:01:02:03:04:05 '::macaddr8; 12SELECT ' 08:00:2b:01:02:03:04:05'::macaddr8; 13 14SELECT '123 08:00:2b:01:02:03'::macaddr8; -- invalid 15SELECT '08:00:2b:01:02:03 123'::macaddr8; -- invalid 16SELECT '123 08:00:2b:01:02:03:04:05'::macaddr8; -- invalid 17SELECT '08:00:2b:01:02:03:04:05 123'::macaddr8; -- invalid 18SELECT '08:00:2b:01:02:03:04:05:06:07'::macaddr8; -- invalid 19SELECT '08-00-2b-01-02-03-04-05-06-07'::macaddr8; -- invalid 20SELECT '08002b:01020304050607'::macaddr8; -- invalid 21SELECT '08002b01020304050607'::macaddr8; -- invalid 22SELECT '0z002b0102030405'::macaddr8; -- invalid 23SELECT '08002b010203xyza'::macaddr8; -- invalid 24 25SELECT '08:00-2b:01:02:03:04:05'::macaddr8; -- invalid 26SELECT '08:00-2b:01:02:03:04:05'::macaddr8; -- invalid 27SELECT '08:00:2b:01.02:03:04:05'::macaddr8; -- invalid 28SELECT '08:00:2b:01.02:03:04:05'::macaddr8; -- invalid 29 30-- test converting a MAC address to modified EUI-64 for inclusion 31-- in an ipv6 address 32SELECT macaddr8_set7bit('00:08:2b:01:02:03'::macaddr8); 33 34CREATE TABLE macaddr8_data (a int, b macaddr8); 35 36INSERT INTO macaddr8_data VALUES (1, '08:00:2b:01:02:03'); 37INSERT INTO macaddr8_data VALUES (2, '08-00-2b-01-02-03'); 38INSERT INTO macaddr8_data VALUES (3, '08002b:010203'); 39INSERT INTO macaddr8_data VALUES (4, '08002b-010203'); 40INSERT INTO macaddr8_data VALUES (5, '0800.2b01.0203'); 41INSERT INTO macaddr8_data VALUES (6, '0800-2b01-0203'); 42INSERT INTO macaddr8_data VALUES (7, '08002b010203'); 43INSERT INTO macaddr8_data VALUES (8, '0800:2b01:0203'); 44INSERT INTO macaddr8_data VALUES (9, 'not even close'); -- invalid 45 46INSERT INTO macaddr8_data VALUES (10, '08:00:2b:01:02:04'); 47INSERT INTO macaddr8_data VALUES (11, '08:00:2b:01:02:02'); 48INSERT INTO macaddr8_data VALUES (12, '08:00:2a:01:02:03'); 49INSERT INTO macaddr8_data VALUES (13, '08:00:2c:01:02:03'); 50INSERT INTO macaddr8_data VALUES (14, '08:00:2a:01:02:04'); 51 52INSERT INTO macaddr8_data VALUES (15, '08:00:2b:01:02:03:04:05'); 53INSERT INTO macaddr8_data VALUES (16, '08-00-2b-01-02-03-04-05'); 54INSERT INTO macaddr8_data VALUES (17, '08002b:0102030405'); 55INSERT INTO macaddr8_data VALUES (18, '08002b-0102030405'); 56INSERT INTO macaddr8_data VALUES (19, '0800.2b01.0203.0405'); 57INSERT INTO macaddr8_data VALUES (20, '08002b01:02030405'); 58INSERT INTO macaddr8_data VALUES (21, '08002b0102030405'); 59 60SELECT * FROM macaddr8_data ORDER BY 1; 61 62CREATE INDEX macaddr8_data_btree ON macaddr8_data USING btree (b); 63CREATE INDEX macaddr8_data_hash ON macaddr8_data USING hash (b); 64 65SELECT a, b, trunc(b) FROM macaddr8_data ORDER BY 2, 1; 66 67SELECT b < '08:00:2b:01:02:04' FROM macaddr8_data WHERE a = 1; -- true 68SELECT b > '08:00:2b:ff:fe:01:02:04' FROM macaddr8_data WHERE a = 1; -- false 69SELECT b > '08:00:2b:ff:fe:01:02:03' FROM macaddr8_data WHERE a = 1; -- false 70SELECT b::macaddr <= '08:00:2b:01:02:04' FROM macaddr8_data WHERE a = 1; -- true 71SELECT b::macaddr >= '08:00:2b:01:02:04' FROM macaddr8_data WHERE a = 1; -- false 72SELECT b = '08:00:2b:ff:fe:01:02:03' FROM macaddr8_data WHERE a = 1; -- true 73SELECT b::macaddr <> '08:00:2b:01:02:04'::macaddr FROM macaddr8_data WHERE a = 1; -- true 74SELECT b::macaddr <> '08:00:2b:01:02:03'::macaddr FROM macaddr8_data WHERE a = 1; -- false 75 76SELECT b < '08:00:2b:01:02:03:04:06' FROM macaddr8_data WHERE a = 15; -- true 77SELECT b > '08:00:2b:01:02:03:04:06' FROM macaddr8_data WHERE a = 15; -- false 78SELECT b > '08:00:2b:01:02:03:04:05' FROM macaddr8_data WHERE a = 15; -- false 79SELECT b <= '08:00:2b:01:02:03:04:06' FROM macaddr8_data WHERE a = 15; -- true 80SELECT b >= '08:00:2b:01:02:03:04:06' FROM macaddr8_data WHERE a = 15; -- false 81SELECT b = '08:00:2b:01:02:03:04:05' FROM macaddr8_data WHERE a = 15; -- true 82SELECT b <> '08:00:2b:01:02:03:04:06' FROM macaddr8_data WHERE a = 15; -- true 83SELECT b <> '08:00:2b:01:02:03:04:05' FROM macaddr8_data WHERE a = 15; -- false 84 85SELECT ~b FROM macaddr8_data; 86SELECT b & '00:00:00:ff:ff:ff' FROM macaddr8_data; 87SELECT b | '01:02:03:04:05:06' FROM macaddr8_data; 88 89DROP TABLE macaddr8_data; 90