[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    [SQL] Can't get upsert working.
From:       Bert <biertie () gmail ! com>
Date:       2012-11-30 17:00:32
Message-ID: CAFCtE1=xUHzWQcpBQ7tSxFfQwJO-Pay2jFyfWHZ5dPDuAPhnEg () mail ! gmail ! com
[Download RAW message or body]

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

[Attachment #3 (text/html)]

<br clear="all">Hello, <br><br>Anyone here with upsert experience? <br><br>I&#39;m \
trying to run this query:<br><br>WITH UPSERT AS<br>   (UPDATE oltp.ST_TRANSACTION<br> \
SET (row1,<br>       row2,<br>       row3) = (ET.row1,<br>  ET.row2,<br>
                ET.row3)<br>     FROM<br>         (SELECT *<br>           FROM \
loadoltp.ST_TRANSACTION_INSERT<br>           WHERE LOADTABLETIME = &#39;2012-11-30 \
14:48:59.252960&#39;) AS ET returning *)<br>INSERT INTO oltp.ST_TRANSACTION<br>SELECT \
(ET.row1,<br>

       ET.row2,<br>       ET.row3)<br>FROM<br>     (SELECT *<br>     FROM \
loadoltp.ST_TRANSACTION_INSERT<br>     WHERE LOADTABLETIME = &#39;2012-11-30 \
14:48:59.252960&#39;) AS ET<br>WHERE ET.tick_server_id NOT IN<br>       (SELECT \
ET.tick_server_id<br>

         FROM upsert b)<br>   AND tra_id NOT IN<br>       (SELECT ET.tra_id<br>       \
FROM upsert b)<br><br>But I always get this error message:<br>ERROR:   column \
&quot;row1&quot; is of type integer but expression is of type record<br>

LINE 67: SELECT (ET.ROW1,<br><br>Does anyone has an idea? <br><br>wkr,<br>Bert<br \
clear="all"><br>-- <br>Bert Desmet<br>0477/305361<br>



[prev in list] [next in list] [prev in thread] [next in thread]