Thread: Arrays and LIKE
Done a bit of hunting and can't seem to find an answer as to if this sort of thing is possible: SELECT * FROM mail WHERE recipients ILIKE 'david%'; Where recipients is a VARCHAR(128)[] The above doesn't work but thats the sort of thing I want to do... If this is possible and can use an index as well that would be wonderful... --
David <david@vanlaatum.id.au> wrote:
> Done a bit of hunting and can't seem to find an answer as to if this sort of thing is possible:
>
> SELECT * FROM mail WHERE recipients ILIKE 'david%';
>
> Where recipients is a VARCHAR(128)[]
>
> The above doesn't work but thats the sort of thing I want to do...
> If this is possible and can use an index as well that would be wonderful...
test=*# \d foo
Tabelle »public.foo«
Spalte | Typ | Attribute
--------+--------+-----------
t | text[] |
test=*# select * from foo;
t
-----------------
{foo,bla,blub}
{xyz,bla,fasel}
(2 Zeilen)
Zeit: 0,393 ms
test=*# select * from ( select t, generate_subscripts(t, 1) AS s from foo) bla where t[s] ~ '^xy.*';
t | s
-----------------+---
{xyz,bla,fasel} | 1
(1 Zeile)
(http://www.postgresql.org/docs/8.4/interactive/arrays.html#ARRAYS-SEARCHING)
But i don't know how to create an index for that...
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On 2009-08-08, David <david@vanlaatum.id.au> wrote: > Done a bit of hunting and can't seem to find an answer as to if this sort of thing is possible: > > SELECT * FROM mail WHERE recipients ILIKE 'david%'; > > Where recipients is a VARCHAR(128)[] > > The above doesn't work but thats the sort of thing I want to do... > If this is possible and can use an index as well that would be wonderful... hmm tricky: you can't use any(recipients) ilike 'david%'
On Sat, Aug 08, 2009 at 05:04:29PM +0930, David wrote:
> Done a bit of hunting and can't seem to find an answer as to if this
> sort of thing is possible:
>
> SELECT * FROM mail WHERE recipients ILIKE 'david%';
>
> Where recipients is a VARCHAR(128)[]
It's a bit of a fiddle:
CREATE FUNCTION flipilike(text,text)
RETURNS boolean IMMUTABLE LANGUAGE SQL
AS $$ SELECT $2 ILIKE $1; $$;
CREATE OPERATOR ~~~ (
leftarg = text,
rightarg = text,
procedure = flipilike
);
PG now understands:
SELECT 'x%' ~~~ 'fred';
To be the same as:
SELECT 'fred' ILIKE 'x%';
So you can solve your original problem as:
SELECT * FROM mail WHERE 'david%' ~~~ ANY(recipients);
> The above doesn't work but thats the sort of thing I want to do...
> If this is possible and can use an index as well that would be wonderful...
No idea about that, but I'd look to a GIN index to start with. I think
you really want to stop using arrays and do it "properly" with a
relation:
CREATE TABLE mailaddrs (
msgid TEXT REFERENCES mail,
ord INTEGER,
PRIMARY KEY (msgid, ord),
type TEXT CHECK (type IN ('to','from','cc','bcc')),
address TEXT
);
CREATE INDEX mailaddrs_address_idx ON mailaddrs (address);
then you can do:
SELECT DISTINCT msgid
FROM mailaddrs
WHERE address ILIKE 'david%';
and it should do the right thing. Not sure if you have this
flexibility though.
--
Sam http://samason.me.uk/
Thanks all normally I would have gone with a linked table but since support for arrays has improved in pg lately I thoughtI would give them a go again but I guess they are still not ready for what I want. I did think of another solution overnight though that still uses arrays but also a subtable. where I add address to a anothertable with a id sequence and then store the seqid in the array then I could do the like on the subtable returning ids and use the array of ids it returns to compare to the recipient ids in the array (thisshould use a GIN index as overlapping arrays is listed on the page http://www.postgresql.org/docs/8.3/interactive/functions-array.html). This has the added bonus that I can store stats abouteach email address seen with them. (Haven't tried it yet next on the list to do) On Sat, Aug 08, 2009 at 02:10:18PM +0100, Sam Mason wrote: > On Sat, Aug 08, 2009 at 05:04:29PM +0930, David wrote: > > Done a bit of hunting and can't seem to find an answer as to if this > > sort of thing is possible: > > > > SELECT * FROM mail WHERE recipients ILIKE 'david%'; > > > > Where recipients is a VARCHAR(128)[] > > It's a bit of a fiddle: > > CREATE FUNCTION flipilike(text,text) > RETURNS boolean IMMUTABLE LANGUAGE SQL > AS $$ SELECT $2 ILIKE $1; $$; > CREATE OPERATOR ~~~ ( > leftarg = text, > rightarg = text, > procedure = flipilike > ); > > PG now understands: > > SELECT 'x%' ~~~ 'fred'; > > To be the same as: > > SELECT 'fred' ILIKE 'x%'; > > So you can solve your original problem as: > > SELECT * FROM mail WHERE 'david%' ~~~ ANY(recipients); > > > The above doesn't work but thats the sort of thing I want to do... > > If this is possible and can use an index as well that would be wonderful... > > No idea about that, but I'd look to a GIN index to start with. I think > you really want to stop using arrays and do it "properly" with a > relation: > > CREATE TABLE mailaddrs ( > msgid TEXT REFERENCES mail, > ord INTEGER, > PRIMARY KEY (msgid, ord), > type TEXT CHECK (type IN ('to','from','cc','bcc')), > address TEXT > ); > CREATE INDEX mailaddrs_address_idx ON mailaddrs (address); > > then you can do: > > SELECT DISTINCT msgid > FROM mailaddrs > WHERE address ILIKE 'david%'; > > and it should do the right thing. Not sure if you have this > flexibility though. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general --