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
Date: Thu, 21 Jun 2018 13:05:53 -0400
Message-ID: <18795.1529600753@sss.pgh.pa.us> (raw)
In-Reply-To: <CAFYwGJ3Cpb2T+177rEopU7_aQE3W03UoqNZXdP5erCtSRgDGVA@mail.gmail.com>
References: <CAFYwGJ0xfzy8jaK80hVN2eUWr6huce0RU8AgU04MGD00igqkTg@mail.gmail.com>
<CAFYwGJ3Cpb2T+177rEopU7_aQE3W03UoqNZXdP5erCtSRgDGVA@mail.gmail.com>
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
Attachments:this-test-case-doesnt-fail.sql (text/plain, 1.0K) download
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mariodefrutos@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Fwd: Problem with a "complex" upsert
In-Reply-To: <18795.1529600753@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox