Adding a LEFT JOIN on a INSERT INTO....RETURNING

Question

My query Inserts a value and returns the new row inserted

INSERT INTO 
     event_comments(date_posted, e_id, created_by, parent_id, body, num_likes, thread_id)
     VALUES(1575770277, 1, '9e028aaa-d265-4e27-9528-30858ed8c13d', 9, 'December 7th', 0, 'zRfs2I')

 RETURNING comment_id, date_posted, e_id, created_by, parent_id, body, num_likes, thread_id

I want to join the created_by with the user_id from my user's table.

SELECT * from users WHERE user_id = created_by

Is it possible to join that new returning row with another table row?

Answer 1

Consider using a WITH structure to pass the data from the insert to a query that can then be joined.

Example:

-- Setup some initial tables
create table colors (
    id SERIAL primary key,
    color VARCHAR UNIQUE
);

create table animals (
    id SERIAL primary key,
    a_id INTEGER references colors(id),
    animal VARCHAR UNIQUE
);

-- provide some initial data in colors
insert into colors (color) values ('red'), ('green'), ('blue');

-- Store returned data in inserted_animal for use in next query
with inserted_animal as (
    -- Insert a new record into animals
    insert into animals (a_id, animal) values (3, 'fish') returning *
) select * from inserted_animal 
     left join colors on inserted_animal.a_id = colors.id;

-- Output
-- id | a_id | animal | id | color
-- 1  | 3    | fish   | 3  | blue

Explanation: A WITH query allows a record returned from an initial query, including data returned from a RETURNING clause, which is stored in a temporary table that can be accessed in the expression that follows it to continue work on it, including using a JOIN expression.