SEO

Hi all,

Hopefully a quick question...

Why does:

nmc=> SELECT 'Y' AS local FROM domains WHERE dom_name='test.com';
local
-------
Y
(1 row)

Work but:

nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('mkelly@test.com');
local
-------
(0 rows)

Not work?

I am sure I am missing something simple.

Thanks!!

Madi

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On 9/4/07, Madison Kelly <linux@alteeve.com> wrote:
> I am sure I am missing something simple.

Yeah...

'mkelly@test.com' <> '@test.com'

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On 9/4/07, Madison Kelly <linux@alteeve.com> wrote:
> Hi all,
>
> Hopefully a quick question...
>
> Why does:
>
> nmc=> SELECT 'Y' AS local FROM domains WHERE dom_name='test.com';
> local
> -------
> Y
> (1 row)
>
> Work but:
>
> nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
> ('mkelly@test.com');
> local
> -------
> (0 rows)
>
> Not work?

because @dom_name isn't IN mkelly@test.com?

I think you're looking for pattern matching.

select 'Y' as local from domains where dom_name ilike '%test.com%'

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Madison Kelly wrote:
> nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
> ('mkelly@test.com');
> local
> -------
> (0 rows)
>
> Not work?

I don't think IN does what you think it does. It's not a substring-test,
but a set test:

SELECT 1 WHERE 'x' IN ('a','b','c','x');
SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzled<>wamble);

You could mess around with substring() and length() or I'd use LIKE.

If it's just a domain you're looking for though, might be most efficient
to strip the leading part off your value with regexp_replace().

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Rodrigo De León wrote:
> On 9/4/07, Madison Kelly <linux@alteeve.com> wrote:
>> I am sure I am missing something simple.
>
> Yeah...
>
> 'mkelly@test.com' <> '@test.com'

Well now, don't I feel silly. *sigh*

Thanks!

Madi

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Richard Huxton wrote:
> Madison Kelly wrote:
>> nmc=> SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
>> ('mkelly@test.com');
>> local
>> -------
>> (0 rows)
>>
>> Not work?
>
> I don't think IN does what you think it does. It's not a substring-test,
> but a set test:
>
> SELECT 1 WHERE 'x' IN ('a','b','c','x');
> SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzled<>wamble);
>
> You could mess around with substring() and length() or I'd use LIKE.
>
> If it's just a domain you're looking for though, might be most efficient
> to strip the leading part off your value with regexp_replace().

Yeah, that was my problem. I thought I was using the section following
the '@'. =/

I've been using Postgres for a while now, but only recently getting into
some of the fancier stuff. Until now, I've usually written the program
using PgSQL so I could manipulate the data as I needed. Now I am using
PgSQL as a backend for a few other applications so I am restricted to
using PgSQL to manipulate the data.

It's all left me feeling quite n00bish again.

I did figure out a query that worked:

SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id
AND u.usr_email||'@'||d.dom_name IN ('mkelly@test.com');

Though this may not be the most efficient. In my case, the 'usr_email'
is the LHS of the '@' sign and 'dom_name' is the domain name. If I
wanted to use (I)LIKE, how would I have matched just the domain section
of 'mkelly@test.com' in 'dom_name'?

I'll go read up, now that I've got some key words to search the docs on.

Thanks kindly!

Madi

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Madison Kelly wrote:
> SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id
> AND u.usr_email||'@'||d.dom_name IN ('mkelly@test.com');
>
> Though this may not be the most efficient. In my case, the 'usr_email'
> is the LHS of the '@' sign and 'dom_name' is the domain name. If I
> wanted to use (I)LIKE, how would I have matched just the domain section
> of 'mkelly@test.com' in 'dom_name'?

Something like:

SELECT ... FROM domains d WHERE ('%@' || d.dom_name) LIKE 'mkelly@test.com';

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend