From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mario De Frutos Dieguez <mariodefrutos@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: Problem with a "complex" upsert
In-reply-to: 
 <CAFYwGJ3Cpb2T+177rEopU7_aQE3W03UoqNZXdP5erCtSRgDGVA@mail.gmail.com>
References: 
 <CAFYwGJ0xfzy8jaK80hVN2eUWr6huce0RU8AgU04MGD00igqkTg@mail.gmail.com>
 <CAFYwGJ3Cpb2T+177rEopU7_aQE3W03UoqNZXdP5erCtSRgDGVA@mail.gmail.com>
Comments: In-reply-to Mario De Frutos Dieguez <mariodefrutos@gmail.com>
	message dated "Thu, 21 Jun 2018 16:11:09 +0200"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <18657.1529600576.0@sss.pgh.pa.us>
Date: Thu, 21 Jun 2018 13:05:53 -0400
Message-ID: <18795.1529600753@sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <18657.1529600576.1@sss.pgh.pa.us>

Mario De Frutos Dieguez <mariodefrutos@gmail.com> writes:
> I'm trying to do an upsert to an updatable view with the following SQL
> query:
> ...
> If I don't get any conflict everything works as intended but if we hit a
> conflict then I get the following error message:
> ERROR:  attribute 2 of type record has the wrong type
> DETAIL:  Table has type character varying, but query expects double
> precision.

When filing a bug report, it's a good idea to provide both a self-
contained test case and a mention of what PG version you're using.

I guess from the ROW() syntax you used here, which isn't accepted pre-v10,
that you're using 10.0 or later, but that's not specific enough.

I tried to duplicate this problem using the attached script, but it
works for me.

FWIW, that error message definitely looks like a bug, but I can't
tell whether it's an already-fixed bug or there's some triggering
detail you didn't mention.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/plain; name="this-test-case-doesnt-fail.sql";
	charset="us-ascii"
Content-ID: <18657.1529600576.2@sss.pgh.pa.us>
Content-Description: this-test-case-doesnt-fail.sql

drop schema if exists tiger2015 cascade;
drop schema if exists acs2014_5yr cascade;

create schema tiger2015;

create table tiger2015.blocks_interpolation (
  blockid text,
  blockgroupid text,
  percentage float8
);

create schema acs2014_5yr;

create table acs2014_5yr.seq0003 (
 geoid character varying(40) primary key,
 b01003001 double precision
);

insert into acs2014_5yr.seq0003 values ('15000US020200001013', 42);
insert into tiger2015.blocks_interpolation values
('020200001013', '020200001013', 99);


create view acs2014_5yr.b01003 as
 SELECT seq0003.geoid,
    seq0003.b01003001
   FROM acs2014_5yr.seq0003;

INSERT INTO "acs2014_5yr"."b01003" (geoid, b01003001)

SELECT (left(acs.geoid, 7) || bi.blockid) geoid, (b01003001 *
(percentage*100.0)) b01003001
FROM "tiger2015".blocks_interpolation bi
INNER JOIN "acs2014_5yr"."b01003" acs ON bi.blockgroupid =
substr(acs.geoid,8)
WHERE acs.geoid = '15000US020200001013' AND char_length(substr(acs.geoid,
8)) = 12
ON CONFLICT (geoid) DO UPDATE SET (b01003001) = ROW(EXCLUDED.b01003001);

------- =_aaaaaaaaaa0--