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