sql - PostgreSQL `COPY TO` 命令是否(或可以)保证特定的行顺序？

Question

我知道 PostgreSQL 9 允许 COPY (SELECT ...) ... 语法，但我需要一个与 PostgreSQL 7 兼容的解决方案。

例如，COPY TO 命令是按照写入表的顺序输出数据，还是使用特定索引(或主键)？

Answer 1

COPY 从物理表的开头到结尾逐页线性读取表。

如果表是用 COPY 或一系列 INSERT 创建的，没有任何 UPDATE 或 DELETEs 那么这就是它的写入顺序 - 尽管 PostgreSQL 不保证这一点，并且表的顺序在技术上是未定义的。

如果您需要从古老的 PostgreSQL 生成类似 CSV 的有序输出，我建议使用外部程序(例如，Python + psycopg2 脚本)连接并执行 SELECT .. . ORDER BY，读取结果，将它们转换成所需的格式，并将它们写入输出文件。

尽管不能保证有效，但另一个选项是CLUSTER 所需排序键上的原始输入表。这会将表格重写为所需的物理顺序，然后 COPY 将在扫描表格时发出。

到目前为止，最好的选择是一开始就不要使用 PostgreSQL 的史前化石版本。

在评论中解释后编辑:

您根本不需要COPY，只需要一个效率不高的提取工具/脚本。您可以使用任何现有的 ETL 工具(Pentaho Kettle、Talend Studio、CloverETL 等)，或者只是一个简单的脚本。为了提高速度，如果您热衷于 C 语言，即使是一个简单的 libpq 程序也是如此。

这是一个简单的 Python 示例:

#!/usr/bin/env python

import os
import sys
import psycopg2
import csv

csvf = open("csv.out","wb")
csvw = csv.writer(csvf, dialect='excel-tab')

conn = psycopg2.connect("dbname=regress")
curs = conn.cursor()

# Read blocks of 1000 records from the DB
curs.itersize = 1000

curs.execute("SELECT id, name FROM devices ORDER BY id");
csvw.writerow(("id", "name"))
for row in curs:
    csvw.writerow(row)

csvf.close()

当然，它是单线程的、基本的，并且不会自动计算出列名，但它给了你想法。如果您想要自动列名，请参阅 psycopg2 dictionary cursors ...或使用直接提供所有元数据的 libpq 或 PgJDBC 之类的东西。

libpq 版本可能需要一两个小时的工作。