[snip] > With Oracle we do it with: delete ,tname> where <cond> and rownum < > Y; > Can we have the same goody on Postgres?
The attached message is Tom's response to a similar question, in any case it would work fine in your case too (assuming you have postgres 8.2). HTH, Csaba.
--- Begin Message ---Stephan Szabo <[EMAIL PROTECTED]> writes: > Unfortunately the stuff that makes a ctid=<value> nice doesn't seem to be > used when you're doing an in. Yeah, see the header comments in tidpath.c: * There is currently no special support for joins involving CTID; in * particular nothing corresponding to best_inner_indexscan(). Since it's * not very useful to store TIDs of one table in another table, there * doesn't seem to be enough use-case to justify adding a lot of code * for that. Of course, that argument is wrong for a self-join, which is what this would essentially be. So maybe it would be worth doing sometime. Still, the issue doesn't come up very often. [ thinks for a bit ... ] Actually, you can do it as of 8.2 or so, by abusing the ScalarArrayOp stuff: turn the subquery into an array. An example in the regression database: regression=# explain update tenk1 set ten=ten+1 regression-# where ctid = any (array(select ctid from tenk1 limit 10)); QUERY PLAN ------------------------------------------------------------------------- Tid Scan on tenk1 (cost=0.46..40.71 rows=10 width=250) TID Cond: (ctid = ANY ($0)) InitPlan -> Limit (cost=0.00..0.46 rows=10 width=6) -> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=6) (5 rows) It even seems to get the cost estimate right... regards, tom lane
--- End Message ---
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
