From: | armand pirvu <armand(dot)pirvu(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | insert on conflict |
Date: | 2017-06-27 19:40:06 |
Message-ID: | 3BE461F2-FF56-4BCD-9814-24828D2AA10D@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | DrakoRod | 2017-06-27 20:10:57 | postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting |
Previous Message | David G. Johnston | 2017-06-27 18:47:39 | Re: Which process is actually doing the WAL writes/calls XLogFlush? |