Re: Use ctid in where clause in update from statement

[Dirk Mika <Dirk.Mika@xxxxxxxxxxxxx>]

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.mika@xxxxxxxxxxxxx
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@xxxxxxx>:

    > 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