Re: Upsert Functionality using CTEs

On 11 February 2013 07:47, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:

> On Feb 11, 2013, at 2:23, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
>
> > This works pretty good except for when the top 100 records have
> > duplicated email address (two sales for the same email address).
> >
> > I am wondering what the best strategy is for dealing with this
> > scenario. Doing the records one at a time would work but obviously it
> > would be much slower. There are no other columns I can rely on to
> > make the record more unique either.
>
> The best strategy is fixing your data-model so that you have a unique key.
> As you found out already, e-mail addresses aren't very suitable as unique
> keys for people. For this particular case I'd suggest adding a surrogate
> key.
>
> Alternatively, you might try using (first_name, email) as your key. You'll
> probably still get some duplicates, but they should be less and perhaps few
> enough for your case.
>

I read it that he has multiple sales from the same person? In which case
pretending that the two sales were from different people isn't the correct
result at all.

I may be missing the point of the query, but wasn't it to add an entry for
each email address and (if it already exists in people) to update the
firstname field to match the firstname in the order?

Given that the results will be returned in random order I would just change
the "nd" query to SELECT DISTINCT ON (email) , but like I said I may be
missing the point.

Geoff