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