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

On Wed, Mar 16, 2011 at 7:32 AM, Robert Poor <rdpoor(at)gmail(dot)com> wrote:

> 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?

How about:

INSERT INTO weather (station_id, date, temperature )
SELECT A.station_id, A.date, A.temperature
FROM ( VALUES(2257, '2001-01-01', 22.5),
(2257, '2001-01-02', 25.3) ) AS A ( station_id, date, temperature)
LEFT JOIN weather AS B
ON ( A.station_id, A.date ) = ( B.station_id, B.date )
WHERE B.station_id IS NULL;

--
Regards,
Richard Broersma Jr.