1set client_min_messages = warning;
2/* reset e_f_d to 0 in PG12+ */
3set extra_float_digits = 0;
4create table prefixes (
5       prefix    text primary key,
6       name      text not null,
7       shortname text,
8       state     char default 'S',
9       check( state in ('S', 'R') )
10);
11comment on column prefixes.state is 'S: assigned - R: reserved';
12\copy prefixes from 'prefixes.fr.csv' with delimiter ';' csv quote '"'
13create table ranges as select prefix::prefix_range, name, shortname, state from prefixes ;
14create index idx_prefix on ranges using gist(prefix gist_prefix_range_ops);
15analyze ranges;
16set enable_seqscan to off;
17select * from ranges where prefix @> '0146640123';
18 prefix |      name      | shortname | state
19--------+----------------+-----------+-------
20 0146   | FRANCE TELECOM | FRTE      | S
21(1 row)
22
23select * from ranges where prefix @> '0100091234';
24 prefix |    name    | shortname | state
25--------+------------+-----------+-------
26 010009 | LONG PHONE | LGPH      | S
27(1 row)
28
29set enable_seqscan to on;
30select * from ranges where prefix @> '0146640123';
31 prefix |      name      | shortname | state
32--------+----------------+-----------+-------
33 0146   | FRANCE TELECOM | FRTE      | S
34(1 row)
35
36select * from ranges where prefix @> '0100091234';
37 prefix |    name    | shortname | state
38--------+------------+-----------+-------
39 010009 | LONG PHONE | LGPH      | S
40(1 row)
41
42select a, b, pr_penalty(a::prefix_range, b::prefix_range)
43  from (values('095[4-5]', '0[8-9]'),
44              ('095[4-5]', '0[0-9]'),
45              ('095[4-5]', '[0-3]'),
46              ('095[4-5]', '0'),
47              ('095[4-5]', '[0-9]'),
48              ('095[4-5]', '0[1-5]'),
49              ('095[4-5]', '32'),
50              ('095[4-5]', '[1-3]')) as t(a, b)
51order by 3 asc;
52    a     |   b    | pr_penalty
53----------+--------+-------------
54 095[4-5] | 0[8-9] | 1.52588e-05
55 095[4-5] | 0[0-9] | 1.52588e-05
56 095[4-5] | [0-3]  |  0.00390625
57 095[4-5] | 0      |  0.00390625
58 095[4-5] | [0-9]  |  0.00390625
59 095[4-5] | 0[1-5] |   0.0078125
60 095[4-5] | 32     |           1
61 095[4-5] | [1-3]  |           1
62(8 rows)
63
64create table numbers(number text primary key);
65insert into numbers
66  select '01' || substr(regexp_replace(md5(i::text), '[a-f]', '', 'g'), 1, 8)
67   from generate_series(1, 5000) i;
68analyze numbers;
69select count(*) from numbers n join ranges r on r.prefix @> n.number;
70 count
71-------
72  2019
73(1 row)
74
75reset client_min_messages;
76-- Debian Bug 690160 regarding the symetry of <@ and @>
77SELECT count(*) FROM ranges WHERE prefix <@ '01000';
78 count
79-------
80     9
81(1 row)
82
83SELECT count(*) FROM ranges WHERE prefix @> '01000';
84 count
85-------
86     0
87(1 row)
88
89SELECT count(*) FROM ranges WHERE '01000' <@ prefix;
90 count
91-------
92     0
93(1 row)
94
95SELECT count(*) FROM ranges WHERE '01000' @> prefix;
96 count
97-------
98     9
99(1 row)
100
101SELECT count(*) FROM ranges WHERE '010009888' @> prefix;
102 count
103-------
104     0
105(1 row)
106
107SELECT count(*) FROM ranges WHERE '010009888' <@ prefix;
108 count
109-------
110     1
111(1 row)
112
113SELECT count(*) FROM ranges WHERE prefix @> '010009888';
114 count
115-------
116     1
117(1 row)
118
119SELECT count(*) FROM ranges WHERE prefix <@ '010009888';
120 count
121-------
122     0
123(1 row)
124
125