Upsert Functionality using CTEs

From: Tim Uckun <timuckun(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Upsert Functionality using CTEs
Date: 2013-02-11 01:23:33
Message-ID: CAGuHJrM8qTfjP4kGMPBu6dDAMaKS-HfNDefWZsODb2HWVv2TGw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am using a query like this to try and normalize a table.

WITH nd as (select * from sales order by id limit 100),

people_update as (update people p set first_name = nd.first_name from
nd where p.email = nd.email returning nd.id),

insert into people (first_name, email, created_at, updated_at)
select first_name, email , now(), now()
from nd
left join people_update using(id) where
people_update.id is null),

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message james.sewell 2013-02-11 02:51:49 archive_mode=on on a Slave in streaming replication mode
Previous Message Kevin Grittner 2013-02-11 00:32:50 Re: Can you create aliases in the psql shell?