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

On Wed, Jul 23, 2014 at 7:32 PM, Alvaro Herrera
<alvherre@2ndquadrant.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