时间戳

首页 > 解决方案 > 如何在基于选择的插入期间继续序列号 - Postgres

sql - 如何在基于选择的插入期间继续序列号 - Postgres

问题描述

我正在使用 PostgreSQL 数据库。我有两个表，如下所示

在此处输入图像描述

我想通过引用 Table_1 中的记录在 Table_2 中添加缺少的 person_ids。

如果您看到上面的表格，您会注意到 table_1 中的 person_id = 2,4 在 table_2 中丢失了。

尽管我可以在论坛的帮助下做到这一点，但问题在于非空约束。

表2 创建表定义如下图

CREATE TABLE Table_2(
   SNO INTEGER (20) UNIQUE NOT NULL,
   PERSON_ID INTERGER (20) NOT NULL,
   date_1 DATE NOT NULL,
   date_2 DATE NOT NULL,
   DEPT VARCHAR (10) NOT NULL
);

在此处输入图像描述

这是我尝试过的

INSERT into Table_2 (person_id,date_1,date_2,Dept) (select distinct person_id,TO_DATE('1900-01-01', 'YYYY-MM-DD'),TO_DATE('2900-12-30', 'YYYY-MM-DD'),'F' from Table_1 where person_id not in (select distinct person_id from Table_2))

这会导致错误，如下所示

`ERROR: null value in column "SNO" violates not-null constraint
DETAIL: Failing row contains (null, 2, 1900-01-01, 2900-12-30, F).
SQL state: 23502`

我希望我的输出如下所示。请注意，我的真实数据有超过 5 万条记录，新添加的记录应按原样继续排列。

在此处输入图像描述

标签： sqlpostgresqlselectinsertwhere-clause

解决方案

在表具有整数主键的大多数情况下，它是使用以下方式分配的：

  • serial
  • generated always as identity
  • 序列号，可能使用触发器自动设置
  • 表示由业务定义的东西

您的桌子似乎缺少这一点。我建议修理桌子，但如果你不能，你可以尝试：

insert into Table_2 (sno, person_id, date_1, date_2, Dept)
    select t2.max_sno + row_number() over (order by t1.person_id),
           t1.person_id, date '1900-01-01', date '2900-12-30', 'F' 
    from Table_1 t1 cross join
         (select max(t2.sno) as max_sno from table_2 t2) t2
    where not exists (select 1
                      from table_2 t2
                      where t2.person_id = t1.person_id
                     );

我真的不推荐这种方法，除非作为一种解决方法。正确的做法是把桌子修好。

笔记：

  • 使用简单的日期常量。这使用date关键字，但您也可以使用'2020-01-01'::date
  • NOT EXISTS更可取，NOT IN因为它处理NULL值。
  • 整个WHERE子句并不是防止重复的最佳方法。更好的方法是使用on conflict（甚至两者一起使用）。

推荐阅读