Postgres Array Prefix Matching

Question

I have an array search in Postgres hat matches at least one tag as this:

SELECT * FROM users WHERE tags && ['fun'];

| id | tags      |
| 1  | [fun,day] | 
| 2  | [fun,sun] |

It is possible to match on prefixes? Something like:

SELECT * FROM users WHERE tags LIKE 'f%';

| id | tags      |
| 1  | [fun,day] | 
| 2  | [fun,sun] |
| 3  | [far]     | 
| 4  | [fin]     |

roman · Accepted Answer · 2013-07-10 05:28:48Z

5

try this

create table users (id serial primary key, tags text[]);

insert into users (tags)
values
  ('{"fun", "day"}'),
  ('{"fun", "sun"}'),
  ('{"test"}'),
  ('{"fin"}');

select *
from users
where exists (select * from unnest(tags) as arr where arr like 'f%')

SQL FIDDLE EXAMPLE

answered Jul 10, 2013 at 5:28

roman

118k30 gold badges205 silver badges209 bronze badges

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

4 Comments

bma Over a year ago

That EXISTS clause is incorrect -- look at the results. It is missing a reference to the outer query to associate the rows. Try adding AND id = u.id (is "u" is the alias for the outer table). Fiddle updated: sqlfiddle.com/#!12/a6940/10/0

roman Over a year ago

I have not much expirience in PostgreSQL (mostly in SQL server), but I'm pretty sure you're wrong about it, and BTW my query returns correct result in sqlfiddle. Exists is already working on current row tags, so your id = u.id. SQLFIDDLE is down at the moment, so I'll check it later.

roman Over a year ago

basically my query could be rewritten as select * from users as u where exists (select * from unnest(u.tags) as arr where arr like 'f%')

bma Over a year ago

Hmmm, looks like you are correct about your SQL Fiddle example. I must have misused it when I tested it. My apologies.

bma · Accepted Answer · 2013-07-10 02:53:02Z

3

Here's a working example that should get you more or less what you're after. Note that I am not claiming that this approach will scale...

create table users (
id      serial primary key,
tags    text[] not null
);

insert into users (tags) values
('{"aaaa","bbbb","cccc"}'::text[]),
('{"badc","dddd","eeee"}'::text[]),
('{"gggg","ffbb","attt"}'::text[]);

select *
from (select id,unnest(tags) arr from users) u
where u.arr like 'a%';

 id | arr  
----+------
  1 | aaaa
  3 | attt

edited Jul 10, 2013 at 2:53

answered Jul 10, 2013 at 2:45

bma

9,8562 gold badges35 silver badges22 bronze badges

Collectives™ on Stack Overflow

Postgres Array Prefix Matching

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related