Hi list,
This popped up yesterday during a discussion at the Boston PostgreSQL group meetup, and Jesper Pedersen had advised that I post it here.
Imagine this setup:
CREATE TABLE IF NOT EXISTS mytable (id BIGSERIAL PRIMARY KEY, value TEXT NOT NULL);
WITH insert_cte AS
(
INSERT
INTO mytable (value)
VALUES ('test')
RETURNING
*
)
SELECT mytable.*
FROM insert_cte
JOIN mytable
USING (id);
This query will return nothing, even though people would expect it to return the newly inserted record.
This is just a minimally reproducible example, in which you can easily work around the problem just by getting rid of the join to mytable. But during my consulting career, I've seen people try putting together more complex queries using the same pattern, and this always comes as a surprise.
I get why it's not working (because the statement is not allowed to see the tuples with its own cmin), but I was wondering if it was worth it at least to spell it out explicitly in the documentation.
Right now the documentation says:
RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query
which I don't think is covering the JOIN issue (after all, I am using the RETURNING clause to communicate with the main query).
Can we please add this example to the documentation? I can do the wording if that's something worth adding.
Thank you!