Thread: Can't get upsert working.

<br clear="all" />Hello, <br /><br />Anyone here with upsert experience? <br /><br />I'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 = '2012-11-30 14:48:59.252960') AS ET returning *)<br
/>INSERTINTO 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 = '2012-11-30 14:48:59.252960') AS ET<br
/>WHEREET.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"row1" is of type integer but expression is of type record<br /> LINE 67: SELECT (ET.ROW1,<br /><br />Does anyone
hasan idea? <br /><br />wkr,<br />Bert<br clear="all" /><br />-- <br />Bert Desmet<br />0477/305361<br /> 

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

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