Re: Use ctid in where clause in update from statement

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

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@mikatiming.de 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