主页 / dba / 问题 / 317103
Accepted
Biller Builder
Asked: 2022-09-20 09:02:15 +0800 CST

如何使用 generate_series() 执行虚拟多插入？

  • 772

给定这样的表格：https ://dbfiddle.uk/Z8hOhnYG

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
);

CREATE TABLE profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE account_profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES accounts,
  profile_id bigint NOT NULL REFERENCES profiles
);

要求是：

  • 每个帐户必须始终至少有一个与之关联的个人资料。
  • 因此，新帐户必须始终创建新配置文件并将其关系行添加到数据库
  • 当整个操作因任何原因失败时，什么都不会创建。

所以为了批处理的目的，我想把它写成一个多插入查询，所以我想出了这个算法：

  1. 创建一系列长度与账户数相同的ID
  2. 添加帐户
  3. 加入该系列的新帐户
  4. 添加配置文件
  5. 加入该系列的新个人资料
  6. 在其系列 ID 上加入帐户和个人资料系列表并将结果插入到关系表中
  7. 返回新帐户
postgresql cte

1 个回答

  1. Best Answer

    始终生成

    你将不得不做insert-returning或使用lastval()

    经过一番讨论，我们得出了这个结论：

    WITH input_sequence AS (
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, 5)
),
new_accounts AS (
  INSERT INTO accounts
  SELECT
  FROM
    input_sequence
  RETURNING    *
),
input_accounts AS (
  SELECT
    row_number() over () as index,
    new_accounts.id
  FROM
    new_accounts
),
new_profiles AS (
  INSERT INTO profiles 
    SELECT 
    FROM 
      input_sequence
    RETURNING
    *
),
input_profiles AS (
  SELECT
    row_number() over () as index,
    new_profiles.id
  FROM
    new_profiles
),
new_account_profile_pairs AS ( 
  INSERT INTO account_profiles ( account_id, profile_id )
  SELECT
    input_accounts.id AS account_id,
    input_profiles.id AS profile_id
  FROM
    input_accounts,
    input_profiles
  WHERE 
    input_accounts.index = input_profiles.index
)
SELECT
  index, 
  id
FROM
  input_accounts
ORDER BY 
  id;

    在这里，我使用窗口函数row_bnumber() over ()为每个新插入的行分配行号，给出一个可以在最终结果集中加入的数字。

    • 1

相关问题