1--
2-- macaddr
3--
4
5CREATE TABLE macaddr_data (a int, b macaddr);
6
7INSERT INTO macaddr_data VALUES (1, '08:00:2b:01:02:03');
8INSERT INTO macaddr_data VALUES (2, '08-00-2b-01-02-03');
9INSERT INTO macaddr_data VALUES (3, '08002b:010203');
10INSERT INTO macaddr_data VALUES (4, '08002b-010203');
11INSERT INTO macaddr_data VALUES (5, '0800.2b01.0203');
12INSERT INTO macaddr_data VALUES (6, '0800-2b01-0203');
13INSERT INTO macaddr_data VALUES (7, '08002b010203');
14INSERT INTO macaddr_data VALUES (8, '0800:2b01:0203'); -- invalid
15INSERT INTO macaddr_data VALUES (9, 'not even close'); -- invalid
16
17INSERT INTO macaddr_data VALUES (10, '08:00:2b:01:02:04');
18INSERT INTO macaddr_data VALUES (11, '08:00:2b:01:02:02');
19INSERT INTO macaddr_data VALUES (12, '08:00:2a:01:02:03');
20INSERT INTO macaddr_data VALUES (13, '08:00:2c:01:02:03');
21INSERT INTO macaddr_data VALUES (14, '08:00:2a:01:02:04');
22
23SELECT * FROM macaddr_data;
24
25CREATE INDEX macaddr_data_btree ON macaddr_data USING btree (b);
26CREATE INDEX macaddr_data_hash ON macaddr_data USING hash (b);
27
28SELECT a, b, trunc(b) FROM macaddr_data ORDER BY 2, 1;
29
30SELECT b <  '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
31SELECT b >  '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- false
32SELECT b >  '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
33SELECT b <= '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
34SELECT b >= '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- false
35SELECT b =  '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- true
36SELECT b <> '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
37SELECT b <> '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
38
39SELECT ~b                       FROM macaddr_data;
40SELECT  b & '00:00:00:ff:ff:ff' FROM macaddr_data;
41SELECT  b | '01:02:03:04:05:06' FROM macaddr_data;
42
43DROP TABLE macaddr_data;
44