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