1-- 2-- macaddr 3-- 4CREATE TABLE macaddr_data (a int, b macaddr); 5INSERT INTO macaddr_data VALUES (1, '08:00:2b:01:02:03'); 6INSERT INTO macaddr_data VALUES (2, '08-00-2b-01-02-03'); 7INSERT INTO macaddr_data VALUES (3, '08002b:010203'); 8INSERT INTO macaddr_data VALUES (4, '08002b-010203'); 9INSERT INTO macaddr_data VALUES (5, '0800.2b01.0203'); 10INSERT INTO macaddr_data VALUES (6, '0800-2b01-0203'); 11INSERT INTO macaddr_data VALUES (7, '08002b010203'); 12INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203'); -- invalid 13ERROR: invalid input syntax for type macaddr: "0800:2b01:0203" 14LINE 1: INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203'); 15 ^ 16INSERT INTO macaddr_data VALUES (9, 'not even close'); -- invalid 17ERROR: invalid input syntax for type macaddr: "not even close" 18LINE 1: INSERT INTO macaddr_data VALUES (9, 'not even close'); 19 ^ 20INSERT INTO macaddr_data VALUES (10, '08:00:2b:01:02:04'); 21INSERT INTO macaddr_data VALUES (11, '08:00:2b:01:02:02'); 22INSERT INTO macaddr_data VALUES (12, '08:00:2a:01:02:03'); 23INSERT INTO macaddr_data VALUES (13, '08:00:2c:01:02:03'); 24INSERT INTO macaddr_data VALUES (14, '08:00:2a:01:02:04'); 25SELECT * FROM macaddr_data; 26 a | b 27----+------------------- 28 1 | 08:00:2b:01:02:03 29 2 | 08:00:2b:01:02:03 30 3 | 08:00:2b:01:02:03 31 4 | 08:00:2b:01:02:03 32 5 | 08:00:2b:01:02:03 33 6 | 08:00:2b:01:02:03 34 7 | 08:00:2b:01:02:03 35 10 | 08:00:2b:01:02:04 36 11 | 08:00:2b:01:02:02 37 12 | 08:00:2a:01:02:03 38 13 | 08:00:2c:01:02:03 39 14 | 08:00:2a:01:02:04 40(12 rows) 41 42CREATE INDEX macaddr_data_btree ON macaddr_data USING btree (b); 43CREATE INDEX macaddr_data_hash ON macaddr_data USING hash (b); 44SELECT a, b, trunc(b) FROM macaddr_data ORDER BY 2, 1; 45 a | b | trunc 46----+-------------------+------------------- 47 12 | 08:00:2a:01:02:03 | 08:00:2a:00:00:00 48 14 | 08:00:2a:01:02:04 | 08:00:2a:00:00:00 49 11 | 08:00:2b:01:02:02 | 08:00:2b:00:00:00 50 1 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 51 2 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 52 3 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 53 4 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 54 5 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 55 6 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 56 7 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00 57 10 | 08:00:2b:01:02:04 | 08:00:2b:00:00:00 58 13 | 08:00:2c:01:02:03 | 08:00:2c:00:00:00 59(12 rows) 60 61SELECT b < '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true 62 ?column? 63---------- 64 t 65(1 row) 66 67SELECT b > '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- false 68 ?column? 69---------- 70 f 71(1 row) 72 73SELECT b > '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false 74 ?column? 75---------- 76 f 77(1 row) 78 79SELECT b <= '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true 80 ?column? 81---------- 82 t 83(1 row) 84 85SELECT b >= '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- false 86 ?column? 87---------- 88 f 89(1 row) 90 91SELECT b = '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- true 92 ?column? 93---------- 94 t 95(1 row) 96 97SELECT b <> '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true 98 ?column? 99---------- 100 t 101(1 row) 102 103SELECT b <> '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false 104 ?column? 105---------- 106 f 107(1 row) 108 109SELECT ~b FROM macaddr_data; 110 ?column? 111------------------- 112 f7:ff:d4:fe:fd:fc 113 f7:ff:d4:fe:fd:fc 114 f7:ff:d4:fe:fd:fc 115 f7:ff:d4:fe:fd:fc 116 f7:ff:d4:fe:fd:fc 117 f7:ff:d4:fe:fd:fc 118 f7:ff:d4:fe:fd:fc 119 f7:ff:d4:fe:fd:fb 120 f7:ff:d4:fe:fd:fd 121 f7:ff:d5:fe:fd:fc 122 f7:ff:d3:fe:fd:fc 123 f7:ff:d5:fe:fd:fb 124(12 rows) 125 126SELECT b & '00:00:00:ff:ff:ff' FROM macaddr_data; 127 ?column? 128------------------- 129 00:00:00:01:02:03 130 00:00:00:01:02:03 131 00:00:00:01:02:03 132 00:00:00:01:02:03 133 00:00:00:01:02:03 134 00:00:00:01:02:03 135 00:00:00:01:02:03 136 00:00:00:01:02:04 137 00:00:00:01:02:02 138 00:00:00:01:02:03 139 00:00:00:01:02:03 140 00:00:00:01:02:04 141(12 rows) 142 143SELECT b | '01:02:03:04:05:06' FROM macaddr_data; 144 ?column? 145------------------- 146 09:02:2b:05:07:07 147 09:02:2b:05:07:07 148 09:02:2b:05:07:07 149 09:02:2b:05:07:07 150 09:02:2b:05:07:07 151 09:02:2b:05:07:07 152 09:02:2b:05:07:07 153 09:02:2b:05:07:06 154 09:02:2b:05:07:06 155 09:02:2b:05:07:07 156 09:02:2f:05:07:07 157 09:02:2b:05:07:06 158(12 rows) 159 160DROP TABLE macaddr_data; 161