Lists: | pgsql-general |
---|
From: | Tom Allison <tom(at)tacocat(dot)net> |
---|---|
To: | General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | left outer join and values() |
Date: | 2007-05-31 23:44:55 |
Message-ID: | 7AE17EF9-9209-4D4E-A55F-802DE5FDB0F3@tacocat.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I did something like this with a single VALUES statment [eg: VALUES
((2),(3))]
and thought I could extend this to two columns....
But I'm not having any luck.
BTW - history_idx is an integer and token_idx is a bigint.
select v.history.idx, v.token_idx
from (
values ((3,1),(3,2))) as v(history_idx, token_idx)
left outer join history_token ht on v.history_idx = ht.history_idx
and v.token_idx = ht.token_idx
where ht.history_idx is null;
ERROR: operator does not exist: record = integer
LINE 4: left outer join history_token ht on v.history_idx = ht.histo...
^
HINT: No operator matches the given name and argument type(s). You
may need to add explicit type casts.
NOTE: the '^' is pointing to the '=' on Line 4
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Tom Allison <tom(at)tacocat(dot)net> |
Cc: | General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: left outer join and values() |
Date: | 2007-06-01 03:48:10 |
Message-ID: | 16240.1180669690@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Tom Allison <tom(at)tacocat(dot)net> writes:
> select v.history.idx, v.token_idx
> from (
> values ((3,1),(3,2))) as v(history_idx, token_idx)
> left outer join history_token ht on v.history_idx = ht.history_idx
> and v.token_idx = ht.token_idx
> where ht.history_idx is null;
> ERROR: operator does not exist: record = integer
> LINE 4: left outer join history_token ht on v.history_idx = ht.histo...
> ^
You've got too many parentheses --- the system thinks that "values"
specification is a single row containing two fields that are each
two-column records. I think you want
select v.history_idx, v.token_idx
from (
values (3,1),(3,2)) as v(history_idx, token_idx)
left outer join history_token ht on v.history_idx = ht.history_idx
and v.token_idx = ht.token_idx
where ht.history_idx is null;
Note the "history.idx" typo as well.
regards, tom lane
From: | Tom Allison <tom(at)tacocat(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | General PostgreSQL List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: left outer join and values() |
Date: | 2007-06-01 09:39:48 |
Message-ID: | 8642DD1C-6D40-4859-9B96-9D8DAF3CFCD0@tacocat.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Thank you for the response.
I did figure this out a few minutes after I sent this post.
Apologies for jumping the gun.
I must say, I am absolutely impressed with what pgsql's
implimentation of VALUES allows me to do.
It's kind of ridiculous how much "work" goes away in my code.
Too bad I can't do this at work (Oracle 8/9).
On May 31, 2007, at 11:48 PM, Tom Lane wrote:
>
> Tom Allison <tom(at)tacocat(dot)net> writes:
>> select v.history.idx, v.token_idx
>> from (
>> values ((3,1),(3,2))) as v(history_idx, token_idx)
>> left outer join history_token ht on v.history_idx = ht.history_idx
>> and v.token_idx = ht.token_idx
>> where ht.history_idx is null;
>> ERROR: operator does not exist: record = integer
>> LINE 4: left outer join history_token ht on v.history_idx =
>> ht.histo...
>> ^
>
> You've got too many parentheses --- the system thinks that "values"
> specification is a single row containing two fields that are each
> two-column records. I think you want
>
> select v.history_idx, v.token_idx
> from (
> values (3,1),(3,2)) as v(history_idx, token_idx)
> left outer join history_token ht on v.history_idx = ht.history_idx
> and v.token_idx = ht.token_idx
> where ht.history_idx is null;
>
> Note the "history.idx" typo as well.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster