From: | Robert Poor <rdpoor(at)gmail(dot)com> |
---|---|
To: | Richard Broersma <richard(dot)broersma(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 16:31:14 |
Message-ID: | AANLkTinEFDKH20fZ5x6feNHpqg9ORE+VZMzLP1Q_v5FJ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Richard:
On Wed, Mar 16, 2011 at 08:45, Richard Broersma
<richard(dot)broersma(at)gmail(dot)com> wrote:
> 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;
That would work, though I was hoping there'd be some mechanism that
used the key to determine if the incoming record was unique or not
(rather than writing a custom query).
Are there limits to the size of the VALUES sub-query? I'm processing
about 500 records at a time, and each record is fairly wide (about 350
characters without the field names).
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Broersma | 2011-03-16 16:38:20 | Re: what is the PostgreSQL idiom for "insert or update"? |
Previous Message | Robert Poor | 2011-03-16 16:23:45 | Re: what is the PostgreSQL idiom for "insert or update"? |