PostgreSQL:将记录插入到主表引用的其他表中。

huangapple go评论60阅读模式
英文:

PostgreSQL: Insert records into other tables that have a reference from the main table

问题

在执行以下查询后,我想要实现以下效果:

INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')

accound_id 和初始值会自动插入到所有具有相同结构的其他表中。

以下是我拥有的所有表格:

CREATE TABLE IF NOT EXISTS accounts(
	unique_id uuid,
	account_id BIGINT NOT NULL UNIQUE GENERATED ALWAYS AS IDENTITY,
	PRIMARY KEY (unique_id, account_id)
);

CREATE TABLE IF NOT EXISTS currency_golden_credit (
	ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
    balance INT DEFAULT 0 NOT NULL,
	CONSTRAINT fk_account FOREIGN KEY (ref_id) 
	  REFERENCES accounts (account_id)
      ON DELETE CASCADE
	  ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS currency_iron_credit (
	ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
    balance INT DEFAULT 0 NOT NULL,
	CONSTRAINT fk_account FOREIGN KEY (ref_id) 
	  REFERENCES accounts (account_id)
      ON DELETE CASCADE
);

我没有找到任何解决方案,并尝试了以下查询,但它也被证明是不正确的:

with rows as (
  INSERT INTO accounts (unique_id) VALUES
	('c78f139a-7d71-429b-b55c-0d8fad481959')
	RETURNING account_id
) 

INSERT INTO currency_golden_credit (ref_id) SELECT account_id
INSERT INTO currency_iron_credit (ref_id) SELECT account_id
FROM rows;
英文:

I want to achieve such that after executing the query:

INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')

the accound_id and initial value are automatically inserted into all other tables that have a common structure.

Here are all the tables I have:

CREATE TABLE IF NOT EXISTS accounts(
	unique_id uuid,
	account_id BIGINT NOT NULL UNIQUE GENERATED ALWAYS AS IDENTITY,
	PRIMARY KEY (unique_id, account_id)
);

CREATE TABLE IF NOT EXISTS currency_golden_credit (
	ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
    balance INT DEFAULT 0 NOT NULL,
	CONSTRAINT fk_account FOREIGN KEY (ref_id) 
	  REFERENCES accounts (account_id)
      ON DELETE CASCADE
	  ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS currency_iron_credit (
	ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
    balance INT DEFAULT 0 NOT NULL,
	CONSTRAINT fk_account FOREIGN KEY (ref_id) 
	  REFERENCES accounts (account_id)
      ON DELETE CASCADE
);

I didn't find any solution and tried the query, but it also turned out to be incorrect:

with rows as (
  INSERT INTO accounts (unique_id) VALUES
	('c78f139a-7d71-429b-b55c-0d8fad481959')
	RETURNING account_id
) 

INSERT INTO currency_golden_credit (ref_id) SELECT account_id
INSERT INTO currency_iron_credit (ref_id) SELECT account_id
FROM rows;

答案1

得分: 0

正如 @Frank Heikens 所提到的,您需要审查您正在使用的模型。您的尝试在正确的方向上。但是,您需要修改查询以确保从主表插入生成的 account_id 用于随后插入其他表中。

WITH rows AS (
  INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
  RETURNING account_id
) 
INSERT INTO currency_golden_credit (ref_id) SELECT account_id FROM rows
INSERT INTO currency_iron_credit (ref_id) SELECT account_id FROM rows;

在此查询中,我在插入 accounts 表后使用了 RETURNING 子句来检索生成的 account_id。WITH 子句将返回的 account_id 分配给 rows CTE(公共表达式)。

然后,我使用来自 rows CTE 的 account_id 通过 SELECT 语句将后续的 INSERT 语句添加到其他表 currency_golden_credit 和 currency_iron_credit 中。

英文:

As @Frank Heikens mentioned, you need to review the model you are working with.Your attempt is on the right track. However, you need to modify the query to ensure that the account_id generated from the main table insertion is used in the subsequent inserts into the other tables.

WITH rows AS (
  INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
  RETURNING account_id
) 
INSERT INTO currency_golden_credit (ref_id) SELECT account_id FROM rows
INSERT INTO currency_iron_credit (ref_id) SELECT account_id FROM rows;

In this query below, I used the RETURNING clause after the insertion into the accounts table to retrieve the generated account_id. The WITH clause assigns the returned account_id to the rows CTE (Common Table Expression).

Then, I added the subsequent INSERT statements into the other tables currency_golden_credit and currency_iron_credit using a SELECT statement from the rows CTE to obtain the account_id.

huangapple
  • 本文由 发表于 2023年6月29日 21:01:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/76581325.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定