Returning identity of INSERT for new INSERT with data outside of RETURNING clause

Question

I'm using PostgreSQL 12.3.

Similar to the many questions that have been asked about this, like this one, but I need to use data from another table that isn't present in the RETURNING clause.

For a contrived example, consider three tables: customers, products, and sales, and a scenario where a customer needs to be created at the point of sale, and the sales table needs to be updated with both the users and products ids.

CREATE TABLE public.customers (
    id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
) 

CREATE TABLE public.products (
    id SERIAL PRIMARY KEY,
    product TEXT NOT NULL
)

CREATE TABLE public.sales (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    product_id INTEGER NOT NULL REFERENCES products(id)
) 


INSERT INTO customers (first_name, last_name) VALUES ('Bob', 'Smith');
INSERT INTO customers (first_name, last_name) VALUES ('Jane', 'Doe');

INSERT INTO products (product) VALUES ('widget 1');
INSERT INTO products (product) VALUES ('widget 2');
INSERT INTO products (product) VALUES ('widget 3');

INSERT INTO sales (customer_id, product_id) VALUES (1, 1);
INSERT INTO sales (customer_id, product_id) VALUES (2, 1);
INSERT INTO sales (customer_id, product_id) VALUES (2, 2);

If I just need the customer id, the following wouldn't be a problem:

WITH new_customer_and_new_sale AS (

    INSERT INTO customers (first_name, last_name) VALUES ('John', 'Doe') RETURNING id
)   

INSERT INTO sales (customer_id) 

SELECT id FROM new_user_and_new_sale

Since sales has constraints not found in the returning clause, the above obviously won't work. I've tried joining tables to pull in the additional data, but haven't been able to get it to work.

Please overlook any minor issues you may have with the structure of the tables, as the data I'm working with has hundreds of columns and many foreign keys. I've tried to condense the problem into its simplest form, at the risk of seeming contrived.

Answer 1

Your example fails because you're not providing the value for product_id, which is required.

You can directly specify one like this:

WITH inserted_customer AS (
    INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
)
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, 2 FROM inserted_customer;

If you want to get the product ID from an existing product you can do it with a subquery (or a CTE if you're doing more complicated stuff).

WITH inserted_customer AS (
    INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
)
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, (SELECT id FROM products ORDER BY id DESC LIMIT 1) FROM inserted_customer;

If you want to insert the customer and the product on the fly, you can do two CTEs:

WITH inserted_customer AS (
    INSERT INTO customers (first_name, last_name) VALUES ('acacaca', 'Doe') RETURNING id
),
inserted_product AS (
    INSERT INTO products (product) VALUES ('my product') RETURNING id
)   
INSERT INTO sales (customer_id, product_id) 
SELECT inserted_customer.id, inserted_product.id
FROM inserted_customer, inserted_product;

Hope this helps!