Re: what is the PostgreSQL idiom for "insert or update"?
| От | Richard Broersma |
|---|---|
| Тема | Re: what is the PostgreSQL idiom for "insert or update"? |
| Дата | |
| Msg-id | AANLkTinh0pJELgzfsVN6=h9-NJZdORnuG8MF+meRqb8Y@mail.gmail.com обсуждение исходный текст |
| Ответ на | what is the PostgreSQL idiom for "insert or update"? (Robert Poor <rdpoor@gmail.com>) |
| Ответы |
Re: what is the PostgreSQL idiom for "insert or update"?
|
| Список | pgsql-novice |
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.
В списке pgsql-novice по дате отправления: