postgres: INSERT in with clause returns no rows?

Question

Running postgres 10, the following code returns 0 rows:

drop table if exists foo;
create table foo(t text);
with x as (
  insert into foo values ('t') returning 't' as t)
select foo.* from foo join x on foo.t = x.t;

I would expect it to return 1 row. Can someone explain what is going on?

interesting behavior, it is displaying row count 1 if I execute the query twice. And continuously displays one row less from total number of rows. — YogeshR
– YogeshR, Commented Oct 12, 2017 at 18:51

klin · Accepted Answer · 2017-10-12 19:20:07Z

1

The table foo is empty as the inserted row will be visible when the (whole) command completes.

See this statement in the documentation (and the following example):

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables.

answered Oct 12, 2017 at 19:20

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

postgres: INSERT in with clause returns no 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