Re: Making joins involving ctid work for the benefit of UPSERT

On Wed, Jul 23, 2014 at 7:32 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com> wrote:
>> Because nobody wants an operation to either insert 1 tuple or update
>> n>=1 tuples. The intention is that the predicate should probably be
>> something like WHERE unique_key = 'some_value', but you can use
>> something else. So it's kinda like saying which index you care about
>> for a particular operation, except without having to explicitly name
>> an index. But in any event you should use a predicate that uniquely
>> identifies the tuple you want to update.
>
> This seemed a nice idea when I first read it earlier today, but now I'm
> not so sure. Are you saying that it wouldn't be allowed to use an
> UPSERT with some sort of join, such that each joined row would produce
> either one insert or one update? To clarify: suppose I import some
> external data into a temp table, then run UPSERT "USING" that table so
> that the rows end up in a permanent table; some of the rows might be
> already in the permanent table, some others might not. I would hope
> that by the time UPSERT is done, all the rows are in the permanent
> table. Would that raise an error, with your proposed design?

Yeah, my syntax didn't have a mechanism for that. I agree we should
have one. I was just brainstorming.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company