Skip site navigation (1) Skip section navigation (2)

Re: what is the PostgreSQL idiom for "insert or update"?

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: Robert Poor <rdpoor(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: what is the PostgreSQL idiom for "insert or update"?
Date: 2011-03-16 14:48:18
Message-ID: 20110316144818.GC11541@aart.is.rice.edu (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, Mar 16, 2011 at 07:32:13AM -0700, Robert Poor wrote:
> In my application, I receive large blocks of external data that needs
> to be extracted / translated / loaded into the db, and many of these
> data are duplicates of what's already there.
> 
> Consequently, I would like to do efficient "bulk loading" of tables
> using multi-row INSERT commands, ignoring unique records that are
> already present, where 'uniqueness' is defined by key constraints.
> 
> F'rinstance, assume:
> 
> CREATE TABLE "weather_observations" ("id" serial primary key,
> "station_id" integer, "observation_time" timestamp, "temperature_c"
> float)
> CREATE UNIQUE INDEX "observation_index" ON "weather_observations"
> ("station_id", "observation_time")
> 
> Now I'd like to be able to do multi-row inserts, but ignoring
> duplicate entries (specifically, those that would violate uniqueness
> constraint of the index):
> 
> INSERT INTO weather (station_id, date, temperature) VALUES
>   (2257, '2001-01-01', 22.5),
>   (2257, '2001-01-02', 25.3);
> 
> INSERT INTO weather (station_id, date, temperature) VALUES
>   (2257, '2001-01-02', 25.5),                 -- ignored: record already present
>   (2257, '2001-01-03', 21.0);
> 
> What's the idiom for doing this in PostgreSQL?
> 
> [As an aside, in SQLite, you can modify an INSERT statement with "OR
> IGNORE" to achieve this.]
> 
> Thanks!
> 

Here is the current documented method:

http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html

Regards,
Ken

In response to

Responses

pgsql-novice by date

Next:From: Richard BroersmaDate: 2011-03-16 15:45:01
Subject: Re: what is the PostgreSQL idiom for "insert or update"?
Previous:From: Robert PoorDate: 2011-03-16 14:32:13
Subject: what is the PostgreSQL idiom for "insert or update"?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group