时间戳

首页 > 解决方案 > CTE中的Postgres SELECT或INSERT？

postgresql - CTE中的Postgres SELECT或INSERT？

问题描述

我正在使用 Postgres 12.2。

我尝试填写一种事件表，其中有一列需要获取某物的 id。

在我创建的示例中，事件存储在purchases表中，而 id 需要是公司的 id，取自companies表。由于没有固定的公司集，因此公司表需要“随我增长”;)。

所以设置是：

CREATE TABLE 
    companies 
    ( 
        company_id  SERIAL NOT NULL, 
        NAME        CHARACTER VARYING(256) NOT NULL, 
        PRIMARY KEY (company_id), 
        CONSTRAINT companies_unique_name UNIQUE (NAME) 
    )
;
CREATE TABLE 
    purchases
    ( 
        purchase_id    SERIAL NOT NULL, 
        purchase_date  DATE DEFAULT CURRENT_DATE NOT NULL, 
        amount         INTEGER NOT NULL,
        company_id     INTEGER NOT NULL, 
        PRIMARY KEY (purchase_id) 
    )
;

因此，当我现在想要插入事件“Purchased 5 items from 'company1'”时，我需要通过在companies表中查找“company1”或通过在companies.

我可以这样做：

WITH EXISTING_COMPANY AS (
        SELECT company_id FROM companies WHERE name = 'company1'
)
   , NEW_COMPANY AS (
        INSERT INTO companies (name) VALUES ('company1')
        ON CONFLICT(name) DO NOTHING
        RETURNING company_id
)
   , GET_COMPANY_ID AS (
        SELECT COALESCE(
            (SELECT company_id FROM EXISTING_COMPANY),
            (SELECT company_id FROM NEW_COMPANY)
   ) AS company_id
)
INSERT INTO purchases(amount, company_id)
VALUES (5, (select company_id from GET_COMPANY_ID))
;

CTEEXISTING_COMPANY将为我提供现有“company1”或null.

CTENEW_COMPANY会给我一个新创建的公司“company1”的 id 或null

CTEGET_COMPANY_ID最终将通过使用coalesce尝试获取现有 id，如果失败，则获取新 id。

虽然这可行，但它的缺点是我需要两次提供公司名称，并且我需要为每家公司提供新的 CTE，因为我不知道如何将公司名称传递给我的 CTE。

标签： postgresqlcommon-table-expression

解决方案

您可以为新名称提供values()另一个 CTE 中的子句。您也不需要existing_companyCTE，因为这也可以在get_companycte 内完成：

WITH input(name) as (
  values ('company1')
), new_company AS (

  INSERT INTO companies (name) 
  select i.name 
  from input i
  ON CONFLICT (name) DO NOTHING
  RETURNING company_id

), get_company_id AS (
  select company_id
  from new_company

  union all

  select company_id
  from companies
  where name in (select name from input)
    and not exists (select * from new_company)
)
INSERT INTO purchases(amount, company_id)
select 5, company_id 
from get_company_id
;

这也可以扩展到处理多个公司和金额：

WITH input(amount, name) as (
  values 
     (5, 'company1'), 
     (6, 'company2')
), new_company AS (
  INSERT INTO companies (name) 
  select name 
  from input
  ON CONFLICT (name) DO NOTHING
  RETURNING company_id, name
), get_company_id AS (

  select company_id, name
  from new_company
  union all

  select c.company_id, c.name
  from companies c
  where c.name in (select i.name from input i)
  and not exists (select * 
                  from new_company nc
                  where nc.company_id = c.company_id)
)
INSERT INTO purchases(amount, company_id)
select i.amount, g.company_id 
from get_company_id g
  join input i on i.name = g.name
;

推荐阅读