How Can I Insert Data into Three Postgres Tables Simultaneously Using a Single Query?

Use Postgres to insert data into three tables simultaneously

Suppose you need to insert data into three tables simultaneously using a single query. The table structure is as follows:

<code class="language-sql">CREATE TABLE sample (
  id        bigserial PRIMARY KEY,
  lastname  varchar(20),
  firstname varchar(20)
);

CREATE TABLE sample1(
  user_id    bigserial PRIMARY KEY,
  sample_id  bigint REFERENCES sample,
  adddetails varchar(20)
);

CREATE TABLE sample2(
  id      bigserial PRIMARY KEY,
  user_id bigint REFERENCES sample1,
  value   varchar(10)
);</code>

You need to ensure the primary key for each insert and then use it in subsequent tables. However, executing a single query for each insert does not allow immediate reuse of key values.

Solution: Data modification CTE

In order to insert data into three tables simultaneously, you can use a data modification CTE (Common Table Expression). They establish dependencies between consecutive insertions:

<code class="language-sql">WITH ins1 AS (
  INSERT INTO sample(firstname, lastname)
  VALUES ('fai55', 'shaggk')
  RETURNING id AS sample_id
), ins2 AS (
  INSERT INTO sample1 (sample_id, adddetails)
  SELECT sample_id, 'ss' FROM ins1
  RETURNING user_id
)
INSERT INTO sample2 (user_id, value)
SELECT user_id, 'ss2' FROM ins2;</code>

The first CTE, ins1, inserts into the sample table and returns the generated key (sample_id). Then, ins2 inserts into sample1 using the sample_id from ins1 and returns the user_id. Finally, the third INSERT inserts into sample2 using user_id.

Provide data in advance

It is usually more efficient to define the data rows in the CTE beforehand:

<code class="language-sql">WITH data(firstname, lastname, adddetails, value) AS (
  VALUES
    ('fai55', 'shaggk', 'ss', 'ss2'),
    ('fai56', 'XXaggk', 'xx', 'xx2')
)
...
-- (查询的其余部分保持不变)</code>

Remember to consider factors such as uniqueness constraints, type conversions, and handling concurrent writes to ensure data integrity.

The above is the detailed content of How Can I Insert Data into Three Postgres Tables Simultaneously Using a Single Query?. For more information, please follow other related articles on the PHP Chinese website!