Upserting all excluded values

Поиск
Список
Период
Сортировка
От hari.prasath
Тема Upserting all excluded values
Дата
Msg-id 1564a271471.eaf9175b13121.5308900525883500038@zohocorp.com
обсуждение исходный текст
Список pgsql-general
Дерево обсуждения
Hi all,
     Is there any way to do insert on conflict update all the null rows with the excluded values.

For ex:

=>table1 will looks like                                    =>table2 will looks like

Column |  Type   | Modifiers                                    Column |  Type   | Modifiers 

--------+---------+-----------                                   --------+---------+-----------

 pk_t   | integer | not null                                         pk_t1  | integer | not null

 c1     | integer |                                                      col1   | integer | 

 c2     | integer |                                                      col2   | integer | 

Indexes:                                                                  Indexes:

    "t_pkey" PRIMARY KEY, btree (pk_t)                            "t1_pkey" PRIMARY KEY, btree (pk_t1)


and for having left join result of table1 and table2 i have one view in the form of table name newtable

Column |  Type   | Modifiers | Storage | Stats target | Description 

--------+---------+-----------+---------+--------------+-------------

 pk_t   | integer |           | plain   |              | 

 c1     | integer |           | plain   |              | 

 pk_t1  | integer |           | plain   |              | 

 col1   | integer |           | plain   |              | 

Indexes:

    "mvjt_pk_t_idx" UNIQUE, btree (pk_t)

    "mvjt_c1_idx" btree (c1)


and for upserting i am using

>>insert into mvjt select * from t left join t1 on t.pk_t = t1.pk_t1 and pk_t1 in (select pk_t1 from log_t1) ON CONFLICT (pk_t) DO Update set
pk_t1 = EXCLUDED.pk_t1, col1 = EXCLUDED.col1;

The above query is with n attributes(here n will be 2 columns pk_t1 and col1). 

Is there any simple method to update the conflict value to the table with lesser query.?



cheers
- Harry

В списке pgsql-general по дате отправления: