Postgres bulk insert into different schemas

user_11447760 0
Viewed 1218

We currently store things into Redis for temporary aggregation and have a worker that goes and does insertion in bulk into Postgres. Is there a way that we can do bulk insert across multiple schemas in a single Insert transaction? This will remove the need to aggregate things in Redis. Or, is there a better way to aggregate the requests?

Thanks for the help in advance.

postgresql
4 Answers

It really depends on what you mean with "single insert transaction".

One single INSERT statement can only affect one specific table. However you could still BEGIN a transaction (depends on implementation), perform all of your INSERT in there and then COMMIT the transaction. This would still be more efficient than performing all the INSERTs on many transactions since it avoid redundant "hand shakings".

https://www.postgresql.org/docs/current/sql-begin.html

user_17169050

Have you tried creating an update-able view that references two tables and then bulk insert in to this view?

user_844240

Are you looking for something like this?

with data (c1, c2) as (
  values (1,2),(10,20),(30,40)
), s1_insert as (
  insert into schema_one.table_1(c1, c2)
  select c1, c2
  from data
)
insert into schema_two.table_2(col1, col2)
select c1, c2
from data;
user_3303150

If you execute a insert statement a single transaction will happen and you can only insert in a single table(So inserting across multiple schema is altogether not possible in a single transaction).

user_125663930
Related