Postgres - Selecting a row after RETURNING multiple rows

Question

My query is

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning parent_id, comment_id, thread_id

And it returns 3 rows

null | 1 | 1BZbAR
1    | 2 | 1BZbAR
2    | 3 | 1BZbAR

How do I select the row where parent_id is null?

I have tried:

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning MIN(parent_id), comment_id, thread_id

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning HAVING parent_id is null, comment_id, thread_id

DELETE FROM event_comments WHERE thread_id = '1BZbAR'
returning parent_id, comment_id, thread_id as t
SELECT * FROM t WHERE parent_id is null

However all of them give them syntax errors. What should I do?

Tim Biegeleisen · Accepted Answer · 2019-12-31 06:04:05Z

2

You should be able to subquery your current delete statement, or put it into a CTE and query that:

WITH cte AS (
    DELETE
    FROM event_comments
    WHERE thread_id = '1BZbAR'
    RETURNING parent_id, comment_id, thread_id
)

SELECT parent_id, comment_id, thread_id
FROM cte
WHERE parent_id IS NULL;

answered Dec 31, 2019 at 6:04

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

Collectives™ on Stack Overflow

Postgres - Selecting a row after RETURNING multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related