1

I have a table like:

id          name
001to005    ABC
006to210    PQR
211to300    XYZ

This is not the final table i can make it any how i want...so i would like to lookup on this data on id and extract name like if id is in range of 001-005 then ABC and if id is in range 006-010 .... then name XYZ.

My approach would be, store id as regular expression in table like this:

id                 name
[0][0][1-5]        ABC
[0-2][0-9][0-9]    PQR
[2-3][0-9][0-9]    XYZ

and then query: 

select * from table where '004' ~ id

This query will return ABC which is correct but when range gets bigger my input value can lie on both 2nd and 3rd row. For Eg:

select * from table where '299' ~ id

this query will result in 2 rows,so my question is what reg exp to use to make it more restrictive or is there any other approach to solve this:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user3253227
  • 41
  • 3

1 Answers1

3

Do not store regular expressions for simple ranges, that would be extremely expensive and cannot use an index: every single expression in the table would have to be evaluated for every query to satisfy conditions.

You could use range types like @a_horse commented. But while you don't need the added functionality for range types this simple layout is smaller and faster:

CREATE TABLE tbl (
   id_lo int NOT NULL
 , id_hi int NOT NULL
 , name text NOT NULL
);

INSERT INTO t VALUES
  (  1,   5, 'ABC')
, (  6, 210, 'PQR')
, (211, 300, 'XYZ');

CREATE UNIQUE INDEX foo ON t (id_lo, id_hi DESC);

Two integer occupy 8 bytes, int4range value occupies 17 bytes. Size matters in tables and indexes.

Query:

SELECT * FROM tbl
WHERE  4 BETWEEN id_lo AND id_hi;

Lower (id_lo) and upper (id_hi) bounds are included in the range like your sample data suggests.
Note that range types exclude the upper bound by default.

Also assuming that leading zeros are insignificant, so we can operate with plain integer.

Related:

To enforce distinct ranges in the table:

You still don't need a range type in the table for this:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228