Re: Use ctid in where clause in update from statement

I know I can join using the pk, but in oracle using the rowid is faster so I wanted to know, if this is possible in PostgreSQL as well.

Dirk

--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 01.07.19, 11:36 schrieb "Thomas Kellerer" <spam_eater(at)gmx(dot)net>:

> 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.
>
> In the PostgreSQL version I use the column ctid for this. The above query becomes:
>
> *UPDATE*test_large d
> *SET* grp = s.grp
> *FROM* (*SELECT* ctid, test_large.*
> *FROM* test_large
> *WHERE* grp = 1) s
> *WHERE* d.ctid = s.ctid;

Why don't you join on the primary key column?
The ctid comparison is typically quite slow.

Thomas