Postgres Array Prefix Matching

postgresql, sql

Solution

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

Problem

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] | ```

Original source