1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
|
--
-- macaddr
--
CREATE TABLE macaddr_data (a int, b macaddr);
INSERT INTO macaddr_data VALUES (1, '08:00:2b:01:02:03');
INSERT INTO macaddr_data VALUES (2, '08-00-2b-01-02-03');
INSERT INTO macaddr_data VALUES (3, '08002b:010203');
INSERT INTO macaddr_data VALUES (4, '08002b-010203');
INSERT INTO macaddr_data VALUES (5, '0800.2b01.0203');
INSERT INTO macaddr_data VALUES (6, '08002b010203');
INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203'); -- invalid
ERROR: invalid input syntax for type macaddr: "0800:2b01:0203"
LINE 1: INSERT INTO macaddr_data VALUES (7, '0800:2b01:0203');
^
INSERT INTO macaddr_data VALUES (8, 'not even close'); -- invalid
ERROR: invalid input syntax for type macaddr: "not even close"
LINE 1: INSERT INTO macaddr_data VALUES (8, 'not even close');
^
INSERT INTO macaddr_data VALUES (10, '08:00:2b:01:02:04');
INSERT INTO macaddr_data VALUES (11, '08:00:2b:01:02:02');
INSERT INTO macaddr_data VALUES (12, '08:00:2a:01:02:03');
INSERT INTO macaddr_data VALUES (13, '08:00:2c:01:02:03');
INSERT INTO macaddr_data VALUES (14, '08:00:2a:01:02:04');
SELECT * FROM macaddr_data;
a | b
----+-------------------
1 | 08:00:2b:01:02:03
2 | 08:00:2b:01:02:03
3 | 08:00:2b:01:02:03
4 | 08:00:2b:01:02:03
5 | 08:00:2b:01:02:03
6 | 08:00:2b:01:02:03
10 | 08:00:2b:01:02:04
11 | 08:00:2b:01:02:02
12 | 08:00:2a:01:02:03
13 | 08:00:2c:01:02:03
14 | 08:00:2a:01:02:04
(11 rows)
CREATE INDEX macaddr_data_btree ON macaddr_data USING btree (b);
CREATE INDEX macaddr_data_hash ON macaddr_data USING hash (b);
SELECT a, b, trunc(b) FROM macaddr_data ORDER BY 2, 1;
a | b | trunc
----+-------------------+-------------------
12 | 08:00:2a:01:02:03 | 08:00:2a:00:00:00
14 | 08:00:2a:01:02:04 | 08:00:2a:00:00:00
11 | 08:00:2b:01:02:02 | 08:00:2b:00:00:00
1 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
2 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
3 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
4 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
5 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
6 | 08:00:2b:01:02:03 | 08:00:2b:00:00:00
10 | 08:00:2b:01:02:04 | 08:00:2b:00:00:00
13 | 08:00:2c:01:02:03 | 08:00:2c:00:00:00
(11 rows)
SELECT b < '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
?column?
----------
t
(1 row)
SELECT b > '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- false
?column?
----------
f
(1 row)
SELECT b > '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
?column?
----------
f
(1 row)
SELECT b <= '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
?column?
----------
t
(1 row)
SELECT b >= '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- false
?column?
----------
f
(1 row)
SELECT b = '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- true
?column?
----------
t
(1 row)
SELECT b <> '08:00:2b:01:02:04' FROM macaddr_data WHERE a = 1; -- true
?column?
----------
t
(1 row)
SELECT b <> '08:00:2b:01:02:03' FROM macaddr_data WHERE a = 1; -- false
?column?
----------
f
(1 row)
DROP TABLE macaddr_data;
|
