Efficient reverse prefix search in Postgres

Question

Assume a table of 100K Canadian postal code prefixes with population sizes:

Region code	Population
H2	10,000,000
H2Z	100,000
H2K	50,000
H2Z 1G9	500

Given a full length postal code, e.g. "H2Z 1G9" I need to return every row whose region code is a prefix of the input. For some reason I need to do this a lot, so I need effective indexing.

How should I go about this?

Calculate all prefix strings and return a UNION ALL of the respective results. — Laurenz Albe
– Laurenz Albe, Commented May 19, 2022 at 16:55
@LaurenzAlbe thank you, this actually looks to be the simplest solution. If you type it up as an answer I'll accept it. — Carl Patenaude Poulin
– Carl Patenaude Poulin, Commented May 19, 2022 at 18:04

Laurenz Albe · Accepted Answer · 2022-05-19 19:20:36Z

1

You could use a function like this:

CREATE TABLE tab (
   region text PRIMARY KEY,
   population bigint NOT NULL
);

CREATE FUNCTION find_match (t text) RETURNS SETOF tab
   LANGUAGE plpgsql AS
$$DECLARE
   s text;
BEGIN
   FOR s IN SELECT substr(t, 1, n)
            FROM generate_series(1, length(t)) AS s(n)
   LOOP
      RETURN QUERY SELECT * FROM tab
                   WHERE region = s;
   END LOOP;
END;$$;

answered May 19, 2022 at 19:20

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mike Organek · Accepted Answer · 2022-05-19 18:53:01Z

1

Would something like this work efficiently? You can check explain analyze to ensure it uses the PK on your table:

with in_postal_code as (
  select distinct rtrim((left('H2Z 1G9', gs.n))) as postal_prefix
    from generate_series(1, 7) as gs(n)
)
select cp.*
  from in_postal_code i
       join postal_code_population cp
         on cp."Region Code" = i.postal_prefix;

answered May 19, 2022 at 18:53

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

Collectives™ on Stack Overflow

Efficient reverse prefix search in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related