Postgres RETURNING clause with join and order

Question

I've got this query that updates some rows and returns the updated rows in the RETURNING clause. However, even though I've specified ORDER BY mycolumn in the inner query, the rows returned by RETURNING aren't ordered.

UPDATE mytable SET status = 'A'
FROM
  (
    SELECT id FROM mytable
    WHERE status = 'B'
    ORDER BY mycolumn
    LIMIT 100
    FOR UPDATE
  ) sub
  JOIN jointable j ON j.id = sub.id
WHERE mytable.id = sub.id
RETURNING *

I tried putting an ORDER BY in the outer query, like after the JOIN or after the WHERE, but I get an error in both cases. How can I make it return the rows in the desired order?

(A similar question was answered in Update Returning Order by in postgresql but that doesn't include JOINs, only ORDER.)

cybernetic87 · Accepted Answer · 2016-12-29 21:49:06Z

3

Use CTE:

WITH updated as(
    UPDATE mytable SET status = 'A'
FROM
  (
    SELECT id FROM mytable
    WHERE status = 'B'
    ORDER BY mycolumn
    LIMIT 100
    FOR UPDATE
  ) sub
  JOIN jointable j ON j.id = sub.id
WHERE mytable.id = sub.id
RETURNING *
)
select *
from updated
ORDER BY mycolumn

answered Dec 29, 2016 at 21:49

cybernetic87

2851 silver badge13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user779159 Over a year ago

I get an error ORDER BY "mycolumn" is ambiguous. If I take out the last ORDER BY, there's no error. So the inner ORDER BY is fine, it's the outer one that gives the error. I suppose it's because both tables in the join have a mycolumn. How can I get the ordering to work then?

cybernetic87 Over a year ago

Instead of RETURNING *, write down the columns you need.

cybernetic87 Over a year ago

For example: RETURNING j.id, sub.mycolumn, remember that you have to use specific aliases

Collectives™ on Stack Overflow

Postgres RETURNING clause with join and order

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related