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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
set client_min_messages = warning;
/* reset e_f_d to 0 in PG12+ */
set extra_float_digits = 0;
create table prefixes (
prefix text primary key,
name text not null,
shortname text,
state char default 'S',
check( state in ('S', 'R') )
);
comment on column prefixes.state is 'S: assigned - R: reserved';
\copy prefixes from 'prefixes.fr.csv' with delimiter ';' csv quote '"'
create table ranges as select prefix::prefix_range, name, shortname, state from prefixes ;
create index idx_prefix on ranges using gist(prefix gist_prefix_range_ops);
analyze ranges;
set enable_seqscan to off;
select * from ranges where prefix @> '0146640123';
prefix | name | shortname | state
--------+----------------+-----------+-------
0146 | FRANCE TELECOM | FRTE | S
(1 row)
select * from ranges where prefix @> '0100091234';
prefix | name | shortname | state
--------+------------+-----------+-------
010009 | LONG PHONE | LGPH | S
(1 row)
set enable_seqscan to on;
select * from ranges where prefix @> '0146640123';
prefix | name | shortname | state
--------+----------------+-----------+-------
0146 | FRANCE TELECOM | FRTE | S
(1 row)
select * from ranges where prefix @> '0100091234';
prefix | name | shortname | state
--------+------------+-----------+-------
010009 | LONG PHONE | LGPH | S
(1 row)
select a, b, pr_penalty(a::prefix_range, b::prefix_range)
from (values('095[4-5]', '0[8-9]'),
('095[4-5]', '0[0-9]'),
('095[4-5]', '[0-3]'),
('095[4-5]', '0'),
('095[4-5]', '[0-9]'),
('095[4-5]', '0[1-5]'),
('095[4-5]', '32'),
('095[4-5]', '[1-3]')) as t(a, b)
order by 3 asc;
a | b | pr_penalty
----------+--------+-------------
095[4-5] | 0[8-9] | 1.52588e-05
095[4-5] | 0[0-9] | 1.52588e-05
095[4-5] | [0-3] | 0.00390625
095[4-5] | 0 | 0.00390625
095[4-5] | [0-9] | 0.00390625
095[4-5] | 0[1-5] | 0.0078125
095[4-5] | 32 | 1
095[4-5] | [1-3] | 1
(8 rows)
create table numbers(number text primary key);
insert into numbers
select '01' || substr(regexp_replace(md5(i::text), '[a-f]', '', 'g'), 1, 8)
from generate_series(1, 5000) i;
analyze numbers;
select count(*) from numbers n join ranges r on r.prefix @> n.number;
count
-------
2019
(1 row)
reset client_min_messages;
-- Debian Bug 690160 regarding the symetry of <@ and @>
SELECT count(*) FROM ranges WHERE prefix <@ '01000';
count
-------
9
(1 row)
SELECT count(*) FROM ranges WHERE prefix @> '01000';
count
-------
0
(1 row)
SELECT count(*) FROM ranges WHERE '01000' <@ prefix;
count
-------
0
(1 row)
SELECT count(*) FROM ranges WHERE '01000' @> prefix;
count
-------
9
(1 row)
SELECT count(*) FROM ranges WHERE '010009888' @> prefix;
count
-------
0
(1 row)
SELECT count(*) FROM ranges WHERE '010009888' <@ prefix;
count
-------
1
(1 row)
SELECT count(*) FROM ranges WHERE prefix @> '010009888';
count
-------
1
(1 row)
SELECT count(*) FROM ranges WHERE prefix <@ '010009888';
count
-------
0
(1 row)
|
