I have a PostgreSQL tables, first contains basic info about order and second table contains products that are inside the referenced order id. My question is how to post data to this two tables in a correct way? Or should I change my scheme into something more well-established?

Tables look like this:

CodePudding user response：

There are no table design issues here, it's a normal one-to-many relationship. But just to ensure data integrity here you should use transactions in the DB. To properly protect data integrity when inserting records into multiple tables, you have two options:

1. using transactions
2. write single query (one statement) to insert into both tables at the same time

In PostgreSQL, any functions are performed with transactions, so one function = one transaction. For example:

CREATE OR REPLACE FUNCTION inser_all_order_data(
)
 RETURNS void 
 LANGUAGE plpgsql
AS $function$
declare
    orderid integer; 
begin -- begin transaction
    
    insert into orders (created_at, type, status) values (now(), 'mytype', 'mystatus')
    returning id into orderid;

    insert into ordercontent (order_id, code, name, content) values (orderid, '001', 'myname', 'some text');
    
end; -- end transaction 
$function$
;

In here, both insert statements are in the same transaction.

Example for writing single query:

with tb as ( 
    insert into orders (created_at, type, status) values ​​(now(), 'mytype', 'mystatus')
    returning id
)
insert into ordercontent (order_id, code, name, content) 
select id, '001', 'myname', 'some text' from tb;

When you write single query you needed using transactions, because one statement = one transaction.

If you don't need to insert records into both tables at the same time, then you can use insert statements as usual.