Why this SELECT of the recent added records doesn't work

Question

I was trying to SELECT records that were just inserted, but it doesn't seen to work.

Example:

create table tt_teste (id bigserial, descricao varchar);

with inseridos as (
  insert into tt_teste (descricao) values('Test 1') returning id
)
select *
from tt_teste
where id in (select id from inseridos);

I tried to rewrite in another way but the result is the same.

with inseridos as (
  insert into tt_teste (descricao) values('Test 2') returning id
)
select *
from inseridos i
  join tt_teste t on t.id = i.id;

The result is always empty. Even if I change the WHERE to "where 1=1 or id in (select id from inseridos)" the new records don't show up. They show up in the next run.

I am doing this because I want to SELECT and INSERT in another table with more data coming from a JOIN, but the database can't select the records just inserted. Seens some kind of concurrency issue.

Answer 1

You can do

with inseridos as (
  insert into tt_teste (descricao) values('Test 1') returning id
)
select *
from inseridos;

or if you want some other field you just inserted, then

with inseridos as (
  insert into tt_teste (descricao) values('Test 1') returning *
)
select *
from inseridos;

There shouldn't be much more to retrieve, unless you have some computed values, triggers filling some fields or some other automation which you didn't tell in your question.