Is it possible to get DISTINCT rows from RETURNING clause?

Question

We're using PostgreSQL v8.2.3.

Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause?

MYTABLE columns are:

1. APRIMARYKEYCOLUMN
2. ABOOLEANCOLUMN
3. EMAIL
4. COLUMN1
5. COLUMN2
6. COLUMN3

UPDATE using RETURNING clause query:

UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL =
MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3;

Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3.

I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be solved/controlled at query-level. Any different ideas/suggestions are appreciated.

Answer 1

No you can't. RETURNING is great but it's not the same as a normal SELECT statement. RETURNING is record based, not set based. DISTINCT works on a set.

Answer 2

This is an old question but since it came up as the first answer in a search and that answer is out of date I'm sharing.

Using common table expressions (CTEs) which has been available since PG version 8.4 you can update/delete and get a distinct result.

WITH updater AS
(
  UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3
)
SELECT DISTINCT EMAIL, COLUMN1, COLUMN2, COLUMN3
FROM updater;