Hi
Got question
birstdb=# \d csischema.dim_company
Table "csischema.dim_company"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
company_id | integer | not null
company_name | character varying(100) |
city | character varying(100) |
state | character varying(100) |
postal_code | character varying(100) |
country | character varying(100) |
latitude | double precision |
longitude | double precision |
update_datetime | timestamp without time zone |
company_source | character varying(1) |
Indexes:
"dim_company_pkey" PRIMARY KEY, btree (company_id)
birstdb=# \d IVEE.dim_company
Table "ivee.dim_company"
Column | Type | Modifiers
-----------------+-----------------------------+-----------
company_id | integer |
company_name | character varying(100) |
city | character varying(100) |
state | character varying(100) |
postal_code | character varying(100) |
country | character varying(100) |
latitude | double precision |
longitude | double precision |
update_datetime | timestamp without time zone |
company_source | character varying(1) |
insert into csischema.dim_company select * from IVEE.dim_company on conflict (company_id) do update
SET
company_name = EXCLUDED.company_name ,
city = EXCLUDED.city ,
state = EXCLUDED.state ,
postal_code = EXCLUDED.postal_code ,
country = EXCLUDED.country ,
latitude = EXCLUDED.latitude ,
longitude = EXCLUDED.longitude ,
update_datetime = EXCLUDED.update_datetime ,
company_source = EXCLUDED.company_source;
QUERY PLAN
---------------------------------------------------------------------------------------
Insert on dim_company (cost=0.00..188.32 rows=1232 width=1126)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: dim_company_pkey
-> Seq Scan on dim_company dim_company_1 (cost=0.00..188.32 rows=1232 width=1126)
(4 rows)
so how is it working in fact ? Isn't it working like looping in the IVEE.dim_company and for each company_id if the record does have a correspondent in csischema.dim_company then update csischema.dim_company set company_name = EXCLUDED.company_name where company_id=... ? If so isn't it supposed to use the PK for each company_id ? Or is it more like building a whole list from IVEE.dim_company and treat like a join ? Just trying to understand
Thanks
Armand
