On Wed, Mar 16, 2011 at 7:32 AM, Robert Poor <rdpoor@gmail.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.
