如果你很幸运地使用当前的
PostgreSQL 9.1,那么有一个优雅且快速的解决方案,只需使用新的
数据修改CTE即可完成。
MySQL没有支持公共表达式(CTE),更不用说是数据修改CTE了。
假设(col1,col2)
最初是唯一的:
查询1
- 在这种情况下,您可以轻松地从表中选择任意片段。
t.id
没有浪费序列号。
WITH s AS (
SELECT id, col1, col2
FROM t
)
,i AS (
INSERT INTO t (col1, col2)
SELECT col1, col2
FROM s
RETURNING id, col1, col2
)
INSERT INTO tu (t, u)
SELECT i.id, tu.u
FROM tu
JOIN s ON tu.t = s.id
JOIN i USING (col1, col2);
如果
(col1, col2)
不是唯一的,我看到另外两种方法:
查询2
WITH s AS (
SELECT id, col1, col2
, row_number() OVER (PARTITION BY col1, col2) AS rn
FROM t
)
,i AS (
INSERT INTO t (col1, col2)
SELECT col1, col2
FROM s
RETURNING id, col1, col2
)
,r AS (
SELECT *
, row_number() OVER (PARTITION BY col1, col2) AS rn
FROM i
)
INSERT INTO tu (t, u)
SELECT r.id, tu.u
FROM r
JOIN s USING (col1, col2, rn)
JOIN tu ON tu.t = s.id;
查询 3
- 这基于 @ypercube 已经提供的相同思路,但是全部在一个查询中完成。
- 如果当前
t.id
的数字空间中存在空洞,则新行将相应地烧录序列号。
- 不要忘记在新的最大值之后重置您的序列,否则对于从序列中获取默认值的
id
的新插入操作,您将获得重复键错误。我将此作为最终步骤整合到命令中。这是最快和最安全的方法。
WITH s AS (
SELECT max(id) AS max_id
FROM t
)
,i AS (
INSERT INTO t (id, col1, col2)
SELECT id + s.max_id, col1, col2
FROM t, s
)
,j AS (
INSERT INTO tu (t, u)
SELECT tu.t + s.max_id, tu.u
FROM tu, s
)
SELECT setval('t_id_seq', s.max_id + s.max_id)
FROM s;
手册中关于setval()的详细信息。
测试设置
用于快速测试。
CREATE TEMP TABLE t (id serial primary key, col1 text, col2 text);
INSERT INTO t (col1, col2) VALUES
('A', 'B')
,('C', 'D');
CREATE TEMP TABLE tu (t int, u int);
INSERT INTO tu VALUES
(1, 100)
,(1, 101)
,(2, 100)
,(2, 102);
SELECT * FROM t;
SELECT * FROM tu;
最近有一个类似的问题,我提供了一个相似的答案。此外还有针对8.3版本没有公共表达式和窗口函数的替代方案。
select
- 根据需要加入多个表,这将产生一个结果表,并将其插入即可。就这样! - Tomasselect
子查询,然后尝试将其放置在插入语句下面,无需担心任何问题。 - Tomas