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.