Re: Fwd: Problem with a "complex" upsert

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

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);