pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan't get upsert working.
3+ messages / 3 participants
[nested] [flat]
* Can't get upsert working.
@ 2012-11-30 17:00 Bert <biertie@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Bert @ 2012-11-30 17:00 UTC (permalink / raw)
To: pgsql-sql
Hello,
Anyone here with upsert experience?
I'm trying to run this query:
WITH UPSERT AS
(UPDATE oltp.ST_TRANSACTION
SET (row1,
row2,
row3) = (ET.row1,
ET.row2,
ET.row3)
FROM
(SELECT *
FROM loadoltp.ST_TRANSACTION_INSERT
WHERE LOADTABLETIME = '2012-11-30 14:48:59.252960') AS ET returning *)
INSERT INTO oltp.ST_TRANSACTION
SELECT (ET.row1,
ET.row2,
ET.row3)
FROM
(SELECT *
FROM loadoltp.ST_TRANSACTION_INSERT
WHERE LOADTABLETIME = '2012-11-30 14:48:59.252960') AS ET
WHERE ET.tick_server_id NOT IN
(SELECT ET.tick_server_id
FROM upsert b)
AND tra_id NOT IN
(SELECT ET.tra_id
FROM upsert b)
But I always get this error message:
ERROR: column "row1" is of type integer but expression is of type record
LINE 67: SELECT (ET.ROW1,
Does anyone has an idea?
wkr,
Bert
--
Bert Desmet
0477/305361
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can't get upsert working.
@ 2012-12-02 09:56 Jasen Betts <jasen@xnet.co.nz>
parent: Bert <biertie@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jasen Betts @ 2012-12-02 09:56 UTC (permalink / raw)
To: pgsql-sql
On 2012-12-02, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2012-11-30, Bert <biertie@gmail.com> wrote:
>
>> Anyone here with upsert experience?
This guy "Depesz" does:
http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Can't get upsert working.
@ 2012-12-04 08:36 Richard Huxton <dev@archonet.com>
parent: Bert <biertie@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2012-12-04 08:36 UTC (permalink / raw)
To: Bert <biertie@gmail.com>; +Cc: pgsql-sql
On 30/11/12 17:00, Bert wrote:
> SELECT (ET.row1,
> ET.row2,
> ET.row3)
> But I always get this error message:
> ERROR: column "row1" is of type integer but expression is of type record
> LINE 67: SELECT (ET.ROW1,
>
> Does anyone has an idea?
You've bracketed the SELECT colums above, which means it's trying to
treat them as a single record-column with three fields. Hence the error
message.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-12-04 08:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-30 17:00 Can't get upsert working. Bert <biertie@gmail.com>
2012-12-02 09:56 ` Jasen Betts <jasen@xnet.co.nz>
2012-12-04 08:36 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox