PostgreSQL: Syntax scenarios when using CTE with multiple INSERT statements in a single transaction
I've been struggling with this for a few days now and could really use some help. I'm working on a personal project and I've been banging my head against this for hours. I'm working with a syntax behavior when trying to use a Common Table Expression (CTE) to perform multiple INSERT operations in a single transaction in PostgreSQL 14. My goal is to insert data into two related tables, but I want to do this while ensuring that the data is temporarily available for the duration of the transaction. However, when I execute the following code, I receive a syntax behavior: ```sql WITH new_data AS ( INSERT INTO orders (customer_id, order_date) VALUES (1, NOW()) RETURNING id ) INSERT INTO order_items (order_id, product_id, quantity) VALUES ((SELECT id FROM new_data), 101, 2); ``` The behavior message I get is `behavior: syntax behavior at or near "INSERT"`. I have tried moving the second INSERT outside the CTE and using a nested query, but that didn't seem to work either. I also considered using a transaction block, but I believe I should be able to achieve this with a CTE. I've also looked at the PostgreSQL documentation and examples, but none seem to clarify how to chain multiple INSERTs with a CTE like this. Is there a correct way to structure my query to achieve this without working with syntax errors? Any help would be greatly appreciated! This is part of a larger service I'm building. What's the best practice here? Am I missing something obvious? What am I doing wrong?