0

I have a database with the phone rates. Rather simple one.

Code     Price
----     -----
1        0.005
18       0.002
1888     0.000

Now I have a phone number arriving to the database: 18127551221. I want to do a query and get the price of the call.

I want to be able to find longest possible match in the "Code" column. Length of the Code can be up to 6 symbols so I want something versatile.

Any ideas?

  • You forgot to provide your version of Postgres and the table definition showing data types and constraints (CREATE TABLE statement). – Erwin Brandstetter Sep 5 '17 at 11:44
1

You can use the simple query provided by Werter. It gets the job done. But performance deteriorates with a rising number of rows in table phone_rate because the query cannot use indexes very efficiently.

If you have more than a trivial number of "phone rates", since ...

Length of the Code can be up to 6 symbols

.. there is a way to optimize performance.

Given this table definition:

CREATE TABLE phone_rate(
   code text PRIMARY KEY
 , price numeric NOT NULL
);

Create 6 partial indexes:

CREATE INDEX phone_rate_code_idx6 ON phone_rate(code) WHERE length(code) = 6;
CREATE INDEX phone_rate_code_idx5 ON phone_rate(code) WHERE length(code) = 5;
CREATE INDEX phone_rate_code_idx4 ON phone_rate(code) WHERE length(code) = 4;
CREATE INDEX phone_rate_code_idx3 ON phone_rate(code) WHERE length(code) = 3;
CREATE INDEX phone_rate_code_idx2 ON phone_rate(code) WHERE length(code) = 2;
CREATE INDEX phone_rate_code_idx1 ON phone_rate(code) WHERE length(code) = 1;

Together they are just as big as a single full index.

Now we can make perfect use of very fast index-only scans. I wrapped the query in an SQL function for convenience:

CREATE OR REPLACE FUNCTION f_matching_phone_rate(_number text)
  RETURNS text AS
$func$
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 6
   AND    left(_number, 6) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 5
   AND    left(_number, 5) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 4
   AND    left(_number, 4) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 3
   AND    left(_number, 3) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 2
   AND    left(_number, 2) = p.code

   UNION ALL 
   SELECT p.code FROM phone_rate p
   WHERE  length(p.code) = 1
   AND    left(_number, 1) = p.code
   LIMIT 1
$func$  LANGUAGE sql STABLE;

Call:

SELECT f_matching_phone_rate('18127551221');

Assuming given numbers are always longer than the shortest code. Else add to each SELECT:

AND    length(number) >= 6  -- etc.

Since all 6 subqueries have identical structure you could easily loop in a plpgsql function. Shorter code, but it won't be faster:

CREATE OR REPLACE FUNCTION f_matching_phone_rate2(_number text, OUT code text) AS
$func$
DECLARE
    i int := 6;
BEGIN
   LOOP
      SELECT INTO code  p.code
      FROM   phone_rate p
      WHERE  length(p.code) = i
      AND    left(_number, i) = p.code;

      EXIT WHEN FOUND;
      i := i - 1;
      EXIT WHEN i < 1;
   END LOOP;
END
$func$  LANGUAGE plpgsql STABLE;

Same call.

Closely related, with explanation and links:

  • Bravo! Thank you very much. Worked like a charm. – N'urk Sep 5 '17 at 23:34
1

Something like this should work:

SELECT *  
FROM prices  
WHERE '18127551221' LIKE "Code" || '%'  
ORDER BY "Code" DESC 
LIMIT 1  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.