Thread

Can't get upsert working.

Bert <biertie@gmail.com> — 2012-11-30T17:00:32Z

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

Re: Can't get upsert working.

Jasen Betts <jasen@xnet.co.nz> — 2012-12-02T09:56:19Z

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

Re: Can't get upsert working.

Richard Huxton <dev@archonet.com> — 2012-12-04T08:36:53Z

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