From: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
---|---|
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 15:45:01 |
Message-ID: | AANLkTinh0pJELgzfsVN6=h9-NJZdORnuG8MF+meRqb8Y@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Poor | 2011-03-16 16:23:45 | Re: what is the PostgreSQL idiom for "insert or update"? |
Previous Message | Kenneth Marshall | 2011-03-16 14:48:18 | Re: what is the PostgreSQL idiom for "insert or update"? |