Thread: joining an array with a table or...?

I've a list of emails and a list of users (with emails).

If the list of emails was already inside a table

create table mails (
 mail varchar(64)
);

create table users (
  name varchar(127),
  mail varchar(64)
);

I'd do:
select coalesce(u.mail, m.mail) from mails left join users on
u.mail=m.mail;

Now mails are into a php array and they may be in the range of 5000
but generally less.

The final query will be something like
insert into mailqueue (qid, uid, mail, ...) select ...

and since some fields are pseudo random sequences computed from a
serial, it would be "clean" to do it just in one query.

Any clean technique?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Mon, 26 Oct 2009 14:15:26 +0100
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> I've a list of emails and a list of users (with emails).
>
> If the list of emails was already inside a table
>
> create table mails (
>  mail varchar(64)
> );
>
> create table users (
>   name varchar(127),
>   mail varchar(64)
> );
>
> I'd do:
> select coalesce(u.mail, m.mail) from mails left join users on
> u.mail=m.mail;
>
> Now mails are into a php array and they may be in the range of 5000
> but generally less.
>
> The final query will be something like
> insert into mailqueue (qid, uid, mail, ...) select ...
>
> and since some fields are pseudo random sequences computed from a
> serial, it would be "clean" to do it just in one query.

> Any clean technique?

To make it more concrete I came up with:

select coalesce(u.mail,j.mail) from (
  select (array['m@example1.com','m@example2.com'])[i] as mail
   from generate_series(1,2) i) j
   left join users u on upper(u.mail)=upper(j.mail);

but I sincerely dislike it.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Mon, Oct 26, 2009 at 11:05 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
> To make it more concrete I came up with:
>
> select coalesce(u.mail,j.mail) from (
>  select (array['m@example1.com','m@example2.com'])[i] as mail
>   from generate_series(1,2) i) j
>   left join users u on upper(u.mail)=upper(j.mail);

how about this:
select coalesce(u.mail,j.mail) from
(
 values ('m@example1.com'), ('m@example2.com')
) j(mail)
 left join users u on upper(u.mail)=upper(j.mail);

merlin

On Mon, 26 Oct 2009 14:56:26 -0400
Merlin Moncure <mmoncure@gmail.com> wrote:

> On Mon, Oct 26, 2009 at 11:05 AM, Ivan Sergio Borgonovo
> <mail@webthatworks.it> wrote:
> > To make it more concrete I came up with:
> >
> > select coalesce(u.mail,j.mail) from (
> >  select (array['m@example1.com','m@example2.com'])[i] as mail
> >   from generate_series(1,2) i) j
> >   left join users u on upper(u.mail)=upper(j.mail);
>
> how about this:
> select coalesce(u.mail,j.mail) from
> (
>  values ('m@example1.com'), ('m@example2.com')
> ) j(mail)
>  left join users u on upper(u.mail)=upper(j.mail);

Yours is between 4 to 10 times faster excluding time on client side
to escape the strings.

I'll play a bit with client code to see if the advantage is kept.

It looks nicer too.

Currently I'm testing with very few match between input array and
user table.
Will this have different impact on the 2 methods?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On Mon, Oct 26, 2009 at 2:48 PM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
> On Mon, 26 Oct 2009 14:56:26 -0400
> Merlin Moncure <mmoncure@gmail.com> wrote:
>
>> On Mon, Oct 26, 2009 at 11:05 AM, Ivan Sergio Borgonovo
>> <mail@webthatworks.it> wrote:
>> > To make it more concrete I came up with:
>> >
>> > select coalesce(u.mail,j.mail) from (
>> >  select (array['m@example1.com','m@example2.com'])[i] as mail
>> >   from generate_series(1,2) i) j
>> >   left join users u on upper(u.mail)=upper(j.mail);
>>
>> how about this:
>> select coalesce(u.mail,j.mail) from
>> (
>>  values ('m@example1.com'), ('m@example2.com')
>> ) j(mail)
>>  left join users u on upper(u.mail)=upper(j.mail);
>
> Yours is between 4 to 10 times faster excluding time on client side
> to escape the strings.
>
> I'll play a bit with client code to see if the advantage is kept.
>
> It looks nicer too.
>
> Currently I'm testing with very few match between input array and
> user table.
> Will this have different impact on the 2 methods?

nope, in both cases the server has to build a set first before
executing the join, so it comes down to doing the awkward 'create then
expand the array' vs. 'direct set build method'.  The array method
wouldn't be quite as bad if php had the ability to operate directly
with postgresql arrays...

merlin