Re: left outer join and values()

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
Thread:
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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Harding 2007-06-01 04:22:35 Re: Design Table & Search Question
Previous Message Tom Lane 2007-06-01 03:35:24 Re: invalid memory alloc after insert with c trigger function