CTE Insert then select with join I'm trying to figure out why this query doesn't work, the goal is to insert an item, then return its ID and use that ID to get related data from another table, however it returns an empty record set

WITH inserted as (
    INSERT INTO food_update(diary_id, food_id, value)
    VALUES($1, $3, $4) RETURNING id ) 
SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted)


However,

WITH inserted as (
        INSERT INTO food_update(diary_id, food_id, value)
        VALUES($1, $3, $4) RETURNING id ) 
(select id from inserted)


If I change it to the query above, it returns the newly inserted ID, then when I run SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted) (second part of the first query) with the newly inserted ID in a separate query it works, but they don't seem to work together. Am i not seeing something obvious, or is there a better way to do this?

I can provide the schema however it's a very general question really i'd just like to know why the insert doesn't seem to return the newly created sequence value. One other way of doing it was to use CURRVAL, though I'm not exactly sure how concurrency works with postgres so i wasn't sure if two queries ran at the same time it may get the second ones ID, though i'm pretty sure the tables meant to be locked while the query is being executed?

As requested here's a schema and a minimal example so that my issue is isolated + anyone that wants to test it can have a look. This implies there's an active database with a public schema

Schema creation + records

CREATE TABLE public.food (
    created timestamptz NULL,
    modified timestamptz NULL,
    id bigserial NOT NULL,
    "name" text NULL,
    CONSTRAINT food_pkey PRIMARY KEY (id)
);


CREATE TABLE public.food_update (
    id bigserial NOT NULL,
    created timestamptz NULL,
    modified timestamptz NULL,
    food_id int8 NULL,
    value numeric(10,2) NULL,
    CONSTRAINT food_update_pkey PRIMARY KEY (id),
    CONSTRAINT food_update_food_id_fkey FOREIGN KEY (food_id) REFERENCES food(id)
);



INSERT INTO public.food
(created, modified, id, "name")
VALUES(NULL, NULL, 1, 'TEST');


This is the query (slightly modified version of mine), and it shows that it still returns nothing.

WITH inserted as (
    INSERT INTO food_update(food_id, value)
    values($1, $2) RETURNING id ) 
SELECT * FROM food_update INNER JOIN food ON food.id = food_update.food_id where food_update.id=(select id from inserted)
I'm not sure I understand your scenario, but the id returned from the CTE is unlikely to match any other table, since it has just been invented. Would something along:

WITH inserted as (
    INSERT INTO food_update(food_id, value)
    values(1, 12) RETURNING id, food_id 
) 
SELECT * 
FROM inserted 
INNER JOIN food 
    ON food.id = inserted.food_id


do?