Hi,
The problem with the INSERT ON CONFLICT is that an insert is tried here first, which may fire triggers.
In my case there is a fairly expensive INSERT Trigger ON EACH ROW, which I would like to avoid.
Since the SELECT statement only returns records whose PK already exists, I thought an UPDATE FROM would be the better choice.
As I said, with the PK as JOIN condition it works fine too. I'm only interested in why no tid scan is used.
Dirk
--
Dirk Mika
Software Developer
|
|
mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany
|
|
fon +49 2202 2401-1197
dirk.mika@xxxxxxxxxxxxx
www.mikatiming.de
|
AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika |
Von:
Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx>
Datum: Montag, 1. Juli 2019 um 11:27
An: "pgsql-general@xxxxxxxxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Betreff: Re: Use ctid in where clause in update from statement
On 1/7/19 12:13 μ.μ., Dirk Mika wrote:
Hello,
I come from the Oracle world and am trying to convert some queries to PostgreSQL syntax. One of these queries is a MERGE statement, which
I converted into an UPDATE SET FROM WHERE construct. In the original query I use the pseudo column ROWID to match a source row with a target row.
This is a simplified version of such a query:
Postgresql supports upserts :
https://www.postgresql.org/docs/11/sql-insert.html "ON CONFLICT"
BR
Dirk
|
--
Dirk Mika
Software Developer
|
|
mika:timing GmbH
Strundepark
- Kürtener Str. 11b
51465 Bergisch Gladbach
Germany
|
|
fon +49 2202 2401-1197
dirk.mika@xxxxxxxxxxxxx
www.mikatiming.de
|
|
AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika
|
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
