使用串行列
您的计划是为 4000 万(!)行添加一个不必要的巨大索引。你甚至不确定它是否是独一无二的。我强烈建议反对这种行动路线。添加一个serial http://www.postgresql.org/docs/current/interactive/datatype-numeric.html#DATATYPE-SERIAL列代替并完成它:
ALTER TABLE tbl ADD COLUMN tbl_id serial PRIMARY KEY;
这就是您需要做的全部。其余的事情会自动发生。更多内容请参见手册或这些密切相关的答案:
PostgreSQL主键自增在C++中崩溃 https://stackoverflow.com/questions/7769481/postgresql-primary-key-auto-increment-crashes-in-c/7769763#7769763
自动递增SQL函数 https://stackoverflow.com/questions/9875223/auto-increment-sql-function/9875517#9875517
添加一个serial
色谱柱是一次性操作,但价格昂贵。整个表必须被重写,从而在操作期间阻止更新。最好在下班时间没有并发负载的情况下完成。我引用手册在这里 http://www.postgresql.org/docs/current/interactive/sql-altertable.html#AEN64781:
添加具有非空默认值的列或更改列的类型
现有列将需要整个表和索引
重写。 [...] 表和/或索引重建可能需要花费大量时间
一张大桌子的时间量;并暂时需要尽可能多的
磁盘空间的两倍。
由于这有效地重写了整个表,因此您还可以创建一个包含序列 pk 列的新表,插入旧表中的所有行,让序列填充其序列中的默认值,删除旧表并重命名新表。这些密切相关的答案中有更多内容:
在 PostgreSQL 9.2 中更新数据库行而不锁定表 https://stackoverflow.com/questions/15770734/updating-database-rows-without-locking-the-table-in-postgresql-9-2/15771103#15771103
添加新列而不加表锁? https://stackoverflow.com/questions/10412078/add-new-column-without-table-lock/10412790#10412790
确保所有 INSERT 语句都有一个目标列表,然后附加列就不会混淆它们:
INSERT INTO tbl (col1, col2, ...) VALUES ...
Not:
INSERT INTO tbl VALUES ...
A serial
是用一个实现的integer
列(4 字节)。
主键约束是通过唯一索引和NOT NULL
对所涉及列的约束。
索引内容的存储方式与表非常相似。需要单独额外的物理存储。有关物理存储的更多信息,请参阅此相关答案:
PostgreSQL 中的计算和节省空间 https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468
您的索引将包含 2 个时间戳(2 x 8 字节)以及一个冗长的文件名(包括)。路径(~ 50 字节?) 这将使索引增大约 2.5 GB(40M x 60 .. 一些字节)并且所有操作都会变慢。
处理重复项
如何处理“导入重复项”取决于您导入数据的方式以及如何准确定义“重复项”。
如果我们谈论的是COPY http://www.postgresql.org/docs/current/interactive/sql-copy.html语句,一种方法是使用临时临时表并使用简单的方法折叠重复项SELECT DISTINCT
or DISTINCT ON
in the INSERT
命令:
CREATE TEMP TABLE tbl_tmp AS
SELECT * FROM tbl LIMIT 0; -- copy structure without data and constraints
COPY tbl_tmp FROM '/path/to/file.csv';
INSERT INTO tbl (col1, col2, col3)
SELECT DISTINCT ON (col1, col2)
col1, col2, col3 FROM tbl_tmp;
或者,也禁止与已存在的行重复:
INSERT INTO tbl (col1, col2, col3)
SELECT i.*
FROM (
SELECT DISTINCT ON (col1, col2)
col1, col2, col3
FROM tbl_tmp
) i
LEFT JOIN tbl t USING (col1, col2)
WHERE t.col1 IS NULL;
温度。表会在会话结束时自动删除。
但正确的解决方法是首先处理产生重复的错误的根源。
原问题
1)如果所有列上都有一个重复项,则根本无法添加 pk。
2)我只会接触 PostgreSQL 数据库8.1版本用一根五英尺长的杆子。它极其古老、过时且效率低下,不再受支持,并且可能存在许多未修复的安全漏洞。Postgres 官方版本控制网站。 http://www.postgresql.org/support/versioning/
@David https://stackoverflow.com/a/17089359/939860已经提供了 SQL 语句。
3 & 4) 重复密钥违规。 PostgreSQL 抛出错误也意味着整个事务被回滚。在 Perl 脚本中捕捉到这一点无法使事务的其余部分完成。例如,您必须使用 plpgsql 创建一个服务器端脚本,您可以在其中捕获异常。