CODEWITHSUNDEEP
postgresql
sajith
sajith

Reputation: 2702

PostgreSQL search text in column having type array

I have a table in PostgreSQL for storing details of films,

film_id serial NOT NULL,
title text,
language text,
directors text[]

I can write a query to find all films having a director name 'ab'

SELECT * FROM films  where  'ab' like any(films.directors)

But How can I write a query to search the table to get all films having a director name starting with 'ab',this query does not giving me any result

 SELECT * FROM films  where  'ab%' like any(films.directors)

Upvotes: 0

Views: 175

Answers (3)

pozs
pozs

Reputation: 36214

There is another way too. The reason, your original plan could not work, is because the array comparison expressions can only use an operator & an array expression on the right side of the operator:

<expression> operator ANY|SOME|ALL (<array expression>)

And all the pattern matching operators work reversed (on the right side there are the pattern) -- you could use that too, but only if you have a pattern array, which you want to test again a single string. this is usually not so useful, but you can make some operators which work reversed:

CREATE OR REPLACE FUNCTION reverse_like(text, text) RETURNS BOOLEAN LANGUAGE SQL AS 'SELECT $2 LIKE $1';
CREATE OR REPLACE FUNCTION reverse_ilike(text, text) RETURNS BOOLEAN LANGUAGE SQL AS 'SELECT $2 ILIKE $1';
CREATE OR REPLACE FUNCTION reverse_similarto(text, text) RETURNS BOOLEAN LANGUAGE SQL AS 'SELECT $2 SIMILAR TO $1';
CREATE OR REPLACE FUNCTION reverse_posix_match(text, text) RETURNS BOOLEAN LANGUAGE SQL AS 'SELECT $2 ~ $1';
CREATE OR REPLACE FUNCTION reverse_posix_imatch(text, text) RETURNS BOOLEAN LANGUAGE SQL AS 'SELECT $2 ~* $1';

CREATE OPERATOR ~~> (PROCEDURE = reverse_like, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR ~~*> (PROCEDURE = reverse_ilike, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR ~~~> (PROCEDURE = reverse_similarto, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR ~> (PROCEDURE = reverse_posix_match, LEFTARG = text, RIGHTARG = text);
CREATE OPERATOR ~*> (PROCEDURE = reverse_posix_imatch, LEFTARG = text, RIGHTARG = text);

After that, you can use the ANY/SOME/ALL array comparisons:

SELECT *
FROM   films
WHERE  '^ab' ~> any(films.directors);

SELECT *
FROM   films
WHERE  'ab%' ~~> any(films.directors);

SELECT *
FROM   films
WHERE  'ab%' ~~~> any(films.directors);

SQLFiddle

Upvotes: 1

bf2020
bf2020

Reputation: 732

SELECT * 
FROM films
where directors like 'ab%';

should get you what you request. Notice how the expressions surronding the LIKE operator are switched from the order they were in.

Here is a link to the SELECT documentation for postgresql 9 which has an example of query using like as show above in my example.

ANY needs a subquery according to http://www.postgresql.org/docs/9.0/static/functions-subquery.html

Upvotes: 0

user330315
user330315

Reputation:

I can think of two different ways (none of them really elegant):

Unnest the array and then search on the elements using LIKE, if you need the result as an array again, you need to aggregate (which means adding more columns than just the directors and the film_id is going to be complicated:

select film_id, array_agg(name) as directors
from (
  SELECT film_id, unnest(directors) as name
  FROM films  
) t
where name like 'ab%'
group by film_id;

Another option (equally slow) might be to create a long string with a delimiter out of the array and then search inside that string:

select *
from films
where '###'||array_to_string(directors, '###') like '%###ab%'

Prepending the delimiter to the string ensures that every entry starts with the delimiter and the condition like '%###ab%' will only match names that actually start with ab.

Upvotes: 1

Related Questions