Problem with a "complex" upsert

Mario De Frutos Dieguez <mariodefrutos@gmail.com>

From: Mario De Frutos Dieguez <mariodefrutos@gmail.com>
To: pgsql-admin@lists.postgresql.org
Date: 2018-06-21T14:07:57Z
Lists: 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/CAEzk6fdzJ3xYQZGbcuYM2rBd2BuDkUksmK=mY9UYYDugg_GgZg@mail.gmail.com
and it was a bug

Commits

  1. Fix INSERT ON CONFLICT UPDATE through a view that isn't just SELECT *.