Query matching patterns based on an offline table

Question

I'm new to PostgreSQL. I am currently working on a 3rd party PostgreSQL database, and I have limited READ permissions, so I'm unable to create a temp table, or create "user defined" variables. Moreover, I found that dealing with regular expressions is more difficult in Postgres than MySQL.

Question:
I have the following dummy table:

Code   |  Name
11199  |  a
22299  |  b
33399  |  c
44499  |  a
55599  |  c

Now I have an offline list (can't create/modify tables in this DB) of numbers. I need to select the names whose code starts with these numbers - but the return code should be the code in the query statement.

Example list of numbers:

Desired query result:

code  |   name
1     |   a
2     |   b
3     |   c
4     |   a
5     |   c

I could think of ways to accomplish it if I had more db permissions (such as inserting the list to a table in the database, plus using temp tables + if I could use regex like in mysql), but I don't know where to start on Postgres with this. Help? :)

[Ain't no such thing as "Postgre"](http://wiki.postgresql.org/wiki/Identity_Guidelines). — Erwin Brandstetter, Oct 20 '13 at 21:48
From the link you provided: "Postgres is also accepted as an alternative name." ;) — Rani, Oct 20 '13 at 21:54
Can there be multiple matches per number in your list? And do you want multiple rows in the result for that? — Erwin Brandstetter, Oct 20 '13 at 21:54
That's correct. Notice the difference between "Postgre" and "Postgres"? — Erwin Brandstetter, Oct 20 '13 at 21:55

score 3 · Answer 1 · answered Oct 20 '13 at 21:04

3

something like this:

-- using cte as your list of numbers
with cte as (
   select unnest(array[1, 2, 3, 4, 5]) as Number
)
select c.Number, t.name
from Table1 as t
    inner join cte as c on t.code::text like c.Number::text || '%'

sql fiddle demo

answered Oct 20 '13 at 21:04

Roman Pekar

107,110
28
195
197

Kind sir, I owe you a beer! :) Thank you, it worked like a charm! – Rani Oct 20 '13 at 21:51

Erwin Brandstetter · Answer 2 · 2023-08-01T19:45:55.660

Revisit your assumptions. Regular expressions are a basic, standard feature in Postgres. There are also a number of built-in functions using regex.

But you don't need a regular expression here. Use the standard SQL LIKE operator. See:

Pattern matching with LIKE, SIMILAR TO or regular expressions

You can provide the list as array and unnest() as demonstrated by Roman or directly with an ad-hoc table using a VALUES expression. I am assuming, that multiple rows can match and you only want a single result per number in your list:

SELECT n.nr, array_agg(t.name) AS names
FROM  (VALUES (1), (2), (3), (4), (5)) n(nr)
JOIN   tbl t ON t.code LIKE (n.nr || '%')
GROUP  BY 1;

Advanced solutions for big list

If you have a (big) file holding numbers (and the necessary privileges!), you could utilize more advanced techniques like:

COPY to a temp table
pg_read_file() (for special purposes, needs superuser privileges)
A foreign data wrapper on the file (file_fdw)

Read data from a text file inside a trigger

Query matching patterns based on an offline table

2 Answers2

Advanced solutions for big list