Data insertion into multiple tables with one query using postgres

Question

I want to insert data into multiple related tables in one go - in a single transaction. I am using Postgres and Dapper ORM. Here is my query:

WITH userins AS (
    INSERT INTO public."user"
(
    "FirstName", 
    "LastName", 
    "Email", 
    "PasswordHash", 
    "Address", 
    "City", 
    "State", 
    "Zip", 
    "Phone", 
    "IsEnabled", 
    "IsVerified", 
    "IsDeleted",  
    "CreatedDate", 
)
    VALUES (@FirstName, @LastName, @Email, @PasswordHash, @Address, @City, @State, @Zip, @Phone, @IsEnabled, ?IsVerified, ?IsDeleted, @CreatedDate);
    RETURNING id AS user_id
   )
, clientins AS (
    INSERT INTO public.client( "Description", "PlanCustomerId", "IsActive", "IsDeleted", "CreatedBy", "CreatedDate", userid)
                           VALUES(@Description, @PlanCustomerId, @IsActive,@IsDeleted, user_id, @CreatedDate,user_id) RETURNING id as client_id;
   )
   , clientsubins
   (
    INSERT INTO public.client_subscription(
    "PlanId", 
    "IsActive",
    "StartDate", 
    "EndDate", 
    "IsDeleted", 
    "CreatedBy", 
    "CreatedDate", 
    clientid, 
    subscriptionid)
    VALUES (@PlanId, @IsActive, @StartDate, @EndDate, @IsDeleted, user_id, @CreatedDate, client_id, @subscriptionid);
   )
   RETURNING client_id

Will this query work or what I need to change?

Answer 1

The answers you have received are good, however I will give my recommendation. Since you are dealing with a block of transactions, it is better to include that as part of a function or stored procedure. For exammple, I prefer to assign the returning id in a variable, that way I can use it across the execution. If everything is ok, I commit and return the user_id or I can also change the return type to boolean if required. If there is an exception, I catch it and return -1, indicating it was not successful, here you can also perform other options, such as registering in a table the cause of the exception. If one table fails, all is rolled back:

CREATE OR REPLACE FUNCTION test_function(p_name CHAR(50), p_other_params CHAR(50)) 
RETURNS INT AS
$$
DECLARE
  new_user_id INT;
  new_purchase_id INT;
BEGIN
    INSERT INTO public.user(name)
    VALUES(p_name) 
    RETURNING public.user.id INTO new_user_id;
    
    INSERT INTO public.purchase(user_id)
    VALUES(new_user_id) 
    RETURNING public.purchase.id INTO new_purchase_id;
    
     INSERT INTO public.purchase_detail(purchase_id)
    VALUES(new_purchase_id);
    
    RAISE EXCEPTION 'Exception when inserting user: %', p_name; --remove to test when there is no issue
    --commited if reaches this point
    RETURN new_user_id;
    
    EXCEPTION WHEN OTHERS THEN
    --ROLLED back if exception
    RETURN -1; -- this value indicates the app it failed
END;
$$
LANGUAGE plpgsql;
          
 select * from test_function('SomeName', 'OtherParameter');
 select id, name from public.user;
 select id, user_id from public.purchase;
 select id, purchase_id from public.purchase_detail;

This is the schema:

CREATE TABLE public.user(  name CHAR(50)); 
CREATE TABLE public.purchase( user_id int);
CREATE TABLE public.purchase_detail( purchase_id INT);
ALTER TABLE public.user ADD COLUMN id SERIAL PRIMARY KEY;
ALTER TABLE public.purchase ADD COLUMN id SERIAL PRIMARY KEY;
ALTER TABLE public.purchase_detail ADD COLUMN id SERIAL PRIMARY KEY;

Check this fiddle in action, and remember to remove the RAISE Exception line to test when there is no exception. Note: For some reason, in fiddle the $$ should be replaces by quotes, and therefore single quotes inside are double quoted.

More on exception handling (40.6.6. Trapping Errors).

Answer 2

Most importantly, you need to SELECT from earlier CTEs to get to the values resulting from the RETURNING clause:

WITH userins AS (
   INSERT INTO public."user"
          ("FirstName", "LastName", "Email", "PasswordHash", "Address", "City", "State", "Zip", "Phone", "IsEnabled", "IsVerified", "IsDeleted", "CreatedDate") --  no dangling ,
   VALUES (@FirstName , @LastName , @Email , @PasswordHash , @Address , @City , @State , @Zip , @Phone , @IsEnabled , @IsVerified , @IsDeleted , @CreatedDate)  --  no ;
   RETURNING id AS userid
   )
 , clientins AS (
   INSERT INTO public.client
         ("Description", "PlanCustomerId", "IsActive", "IsDeleted", "CreatedBy", "CreatedDate", userid)
   SELECT @Description , @PlanCustomerId , @IsActive , @IsDeleted , userid     , @CreatedDate , userid
   FROM   userins
   RETURNING id AS clientid, userid  -- no ;
   )
INSERT INTO public.client_subscription
      ("PlanId", "IsActive","StartDate", "EndDate", "IsDeleted", "CreatedBy", "CreatedDate", clientid , subscriptionid)
SELECT @PlanId , @IsActive , @StartDate, @EndDate , @IsDeleted , userid     , @CreatedDate , clientid,  @subscriptionid
FROM   clientins
RETURNING clientid;

You also had a couple of syntax errors. And I unified the spelling of clientid etc.

Each CTE (and the outer INSERT) depends on the one resulting row from the preceding CTE. This way, nothing is inserted if the first INSERT does not insert (and return) anything.

Since this is a single statement, it runs inside a single transaction automatically. Any error cancels the whole operation.

Even works in your outdated Postgres 9.3.

See:

• Insert data in 3 tables at a time using Postgres