Re: Use ctid in where clause in update from statement

[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