Re: Use ctid in where clause in update from statement - Mailing list pgsql-general

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