this-test-case-doesnt-fail.sql

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