我想用单一的SQL语句
insert into T (...) select ... from T where ...
复制大量的数据集。 我的问题是，有N：从表M-关系T到其它表，并且这些必须得复制。 我怎样才能做到这一点，如果我不知道是哪个原始数据集属于哪个复制数据集？ 让我通过实例演示。
之前数据库的内容：
表T ：
ID | COL1 | COL2
-----------------
1 | A | B
2 | C | D
N：M-表引用表中，U从表T（表中未示出U）：
T | U
---------
1 | 100
1 | 101
2 | 100
2 | 102
我的复制操作，其中[???]是我不知道的部分：
insert into T (COL1, COL2) select COL1, COL2 from T
insert into NM (T, U) select [???]
数据库后的内容：
表T ：
ID | COL1 | COL2
-----------------
1 | A | B
2 | C | D
3 | A | B
4 | C | D
N：M-表：
T | U
---------
1 | 100
1 | 101
2 | 100
2 | 102
3 | 100
3 | 101
4 | 100
4 | 102
注意：
- 我有成千上万的数据集（而不是两个）
- 我想用“插入......选择”，以获得更好的性能
如果你足够幸运的运行当前的PostgreSQL 9.1，有使用新的一个命令一个优雅和快速的解决方案修改数据的热膨胀系数 。
与MySQL没有这样的运气不支持通用表表达式（CTE） ，更不用说修改数据的CTE。
假设(col1, col2)最初是唯一的：
查询1
- 您可以轻松地挑选任意切片从表在这种情况下。
- 没有找到序列号
t.id将被浪费。
WITH s AS (
SELECT id, col1, col2
FROM t
-- WHERE some condition
)
,i AS (
INSERT INTO t (col1, col2)
SELECT col1, col2 -- I gather from comments that id is a serial column
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
- 使用窗口函数
row_number()使非唯一的行是唯一的。 -
INSERT没有孔排t.id空间就像在上面的查询。
WITH s AS (
SELECT id, col1, col2
, row_number() OVER (PARTITION BY col1, col2) AS rn
FROM t
-- WHERE some condition
)
,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) -- match exactly one id per row
JOIN tu ON tu.t = s.id;
问题3
- 这是基于@ypercube已经提供了同样的想法，但都在一个查询。
- 如果在数字空间孔当前
t.id ，序列号将相应烧毁新行。 - 不要忘记重置您的序列之外的新的最大或者你会得到重复键错误，在新的插入
t的是绘制默认的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版本的升级替代品，而不热膨胀系数和窗函数。
步骤1.锁（都）表或确保只有这个脚本运行。 禁用FK检查。
第2步：使用这两个INSERT语句，顺序如下：
INSERT INTO NM
(T, U)
SELECT
T + maxID, U
FROM
NM
CROSS JOIN
( SELECT MAX(ID) AS maxID
FROM T
) AS m
INSERT INTO T
(ID, COL1, COL2)
SELECT
ID+maxID, COL1, COL2
FROM
T
CROSS JOIN
( SELECT MAX(ID) AS maxID
FROM T
) AS m
第3步重新启用FKS。
