有没有一种明智的方法可以将 csv 导入到一列每行有多个值的帖子中?

Question

我是关系数据库的新手，不确定在以下情况下该怎么做。我有 2 个表，其中一个有一个 id 主键，另一个表也引用了该主键。

表 1：

CREATE TABLE table1 (
id int,
x int,
y int,
PRIMARY KEY (id)
);

表 2：

CREATE TABLE table2 (
t1_id int,
id int,
w int,
z int,
PRIMARY KEY (id),
FOREIGN KEY (t1_id) REFERENCES table1(id)
);

对于这两个表，我都使用copy导入数据，例如：

copy table1 from 'data/table1.csv' delimiter ',' csv header;

问题是，虽然填充 table1 的 csv 中的id列包含所有整数，但 table2 的 csvt1_id列中的某些值是用分号分隔的多个 ID，例如1062;1553.

我不确定在Postgresql数据库中表示此类数据的最佳方法是什么。我应该创建某种类型的第三个中间表吗？我需要考虑这样一个事实，即 table2 数据中的外键是指 table1 中的唯一主键，但每行可能有多个(或零个(。

Answer 1

我不能保证这是有效的，但是您可以将t1_id列转换为整数数组而不是整数，然后在插入之前调用触发器函数来检查值。

这样的事情应该有效：

CREATE TABLE table2 (
t1_id int[],
id int,
w int,
z int,
PRIMARY KEY (id)
);
CREATE OR REPLACE FUNCTION table2_insert_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
included_items int[];
BEGIN
select array_agg (id)
into included_items
from table1
where id = any (NEW.t1_id);
if cardinality (NEW.t1_id) = cardinality (included_items) then
return NEW;
else
raise exception 'Id(s) not found in table1';
end if;
END;
$function$;
create trigger insert_table2_trigger before insert
on table2 for each row execute procedure table2_insert_trigger();

如果 table1 包含 id 1、2、3 和 4，这将起作用：

insert into table2 values (array[1,2], 1, 2, 3);

这将失败：

insert into table2 values (array[1,5], 1, 2, 3);

SQL 错误 [P0001]：错误：在表 1 中找不到 ID，其中：PL/pgSQL 函数 table2_insert_trigger(( 第 13 行在 RAISE

同样，我不能发誓效率，但请尝试一下，看看它是否有效。