Fwd: Problem with a "complex" upsert

From: Mario De Frutos Dieguez <mariodefrutos(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Fwd: Problem with a "complex" upsert
Date: 2018-06-21 14:11:09
Message-ID: CAFYwGJ3Cpb2T+177rEopU7_aQE3W03UoqNZXdP5erCtSRgDGVA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-bugs

I'm trying to do an upsert to an updatable view with the following SQL
query:

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

The View is:

View "acs2014_5yr.b01003"
Column | Type | Collation | Nullable | Default |
Storage | Description
-----------+-----------------------+-----------+----------+-
--------+----------+-------------
geoid | character varying(40) | | | |
extended |
b01003001 | double precision | | | |
plain |
View definition:
SELECT seq0003.geoid,
seq0003.b01003001
FROM acs2014_5yr.seq0003;

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.

Looks like it's trying to use the geoid value in the b01003001 field.

I've tried using the source insert table data but the server crashes:

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

SELECT (left(acs.geoid, 7) || bi.blockid) geoid, (b01003001 *
(percentage*100.0))::float 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("acs2014_5yr"."b01003".
b01003001);

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Any clues? Could be a bug? I see something similar here
https://www.postgresql.org/message-id/CAEzk6fdzJ3xYQZGbcuYM2rBd2BuDk
UksmK=mY9UYYDugg_GgZg(at)mail(dot)gmail(dot)com and it was a bug

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Geoff Winkless 2018-06-21 14:40:51 Re: Problem with a "complex" upsert
Previous Message Mario De Frutos Dieguez 2018-06-21 14:07:57 Problem with a "complex" upsert

Browse pgsql-bugs by date

  From Date Subject
Next Message Geoff Winkless 2018-06-21 14:40:51 Re: Problem with a "complex" upsert
Previous Message Mario De Frutos Dieguez 2018-06-21 14:07:57 Problem with a "complex" upsert