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

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

From: Robert Poor <rdpoor(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: what is the PostgreSQL idiom for "insert or update"?
Date: 2011-03-16 14:32:13
Message-ID: AANLkTimOeSsqDtUs2w46+NKKqMrxZrGnhR-z+DbrmPxp@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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!

Responses

pgsql-novice by date

Next:From: Kenneth MarshallDate: 2011-03-16 14:48:18
Subject: Re: what is the PostgreSQL idiom for "insert or update"?
Previous:From: Tom LaneDate: 2011-03-16 14:22:27
Subject: Re: diference between calling a function in select clause and from clause

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