【技术学习】postgresql学习笔记--基础篇 - copy

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

1.1 使用COPY导入导出数据

postgres#copy test from '/tmp/test.csv';
COPY 3
postgres#copy test to '/tmp/test.csv';
COPY 3
postgres# copy test to stdout;
postgres# copy test to '/tmp/test.csv' with csv header;
COPY 3
postgres# copy (select * from test) to '/tmp/test.csv'
COPY 3

postgres#copy "cloud_subscribers" to '/mnt/postgres/11/backup/cloud_subscribers.csv' with csv header;
postgres#copy "sxacc-provisioning-records" to '/mnt/postgres/11/backup/sxacc-provisioning-records.csv' with csv header;

1.2 样例

1.2.1 copy命令导入数据示例：tb2是表名,delimiter ',' 表示按逗号分隔字段数据

postgresql=# copy tb2 from '/mnt/postgresql/weibo.1000'delimiter ',';
COPY 1000
postgresql=# select count(*)from tb2;
 count
-------
(1 row)

1.2.2 copy 命令导入导出数据为csv格式

#split with tab 
postgresql=# COPY tb2 TO '/mnt/postgresql/weibo.csv';
COPY 1000
postgresql=#  COPY tb2 from '/mnt/postgresql/weibo.csv';
COPY 2000

#split with ,
postgresql=# COPY tb2 TO '/mnt/postgresql/weibo.csv' csv header;
COPY 1000
postgresql=#  COPY tb2 from '/mnt/postgresql/weibo.csv';
COPY 2000


copy testdata from '/test/testdata.csv' delimiter as',' csv quote as '"'

1.2.3 copy命令导出指定字段数据在控制台

postgresql=# COPY tb2 (t1,t2,t3) TO STDOUT;
21317568596      1270505818
21317568149      2302617224
21317568470      1297983318
21317568110      2069993004 2302781822
21317568354      362106137 
21317568308      1450475836
21317568584      83103917   
21317568208      1844532765 1713926427
21317568603      1227221083 2478474742
21317568151      1430992492 1253397461
21317567390      1037539510

1.2.4 copy命令导出数据为压缩文件

postgresql=# COPY tb2 TO PROGRAM 'gzip >/mnt/postgresql/weibo.1000.gz';
COPY 2000

1.2.5 copy命令导入导出文件为二进制

#导出
postgresql=# copy binary tb2 to '/mnt/postgresql/binary';
COPY 8000
#导入
postgresql=# copy binary tb2 from'/mnt/postgresql/binary';
COPY 8000

1.3 COPY 命令

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]
where option can be one of:
    FORMAT format_name
    OIDS [ boolean ]
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

table_name

现存表的名字(可以有模式修饰)。。

column_name

可选的待拷贝字段列表。如果没有声明字段列表，那么将使用所有字段。

query

一个必须用圆括弧包围的 SELECT 或 VALUES 命令，其结果将被拷贝。

filename

输入或输出文件的路径名。输入文件名可以是绝对或是相对的路径，但输出文件名必须是绝对路径。 Windows用户可能需要使用 E'' 字符串和双反斜线作为路径名称。

PROGRAM

需执行的程序名。在 COPY FROM 命令中，输入是从程序的标准输出中读取，而在 COPY TO 中，命令的输出会作为程序的标准输入。

注意，程序一般是在命令行界面下执行，当用户需要传递一些变量给程序时，如果这些变量的来源不是可靠的，用户必须小心过滤处理那些对命令行界面来说是有特殊意义的字符。基于安全的原因，最好是使用固定的命令字符串，或者至少是应避免直接使用用户输入（应先过滤特殊字符）。

STDIN

声明输入是来自客户端应用。

STDOUT

声明输入将写入客户端应用。

boolean

声明用户所选的选项是否应该被开启或者关闭。您可以写 TRUE 、 ON 或 1 来启用这个选项，并且用 FALSE 、 OFF 或 0 来关闭它。 boolean 值也可以被省略，此时系统使用缺省值 TRUE 。

FORMAT

选择被读或者写的数据格式： text 、 csv （逗号分隔值），或者 binary 。默认是 text 。

声明为每行记录都拷贝内部对象标识(OID)。（如果为一个 query 拷贝或者没有 OIDS 的表声明了OIDS选项，则抛出一个错误。）

FREEZE

请求拷贝那些已冻结的数据，就类似使用 VACUUM FREEZE 的效果。这主要于用于初始化时加载数据时的性能考虑。仅在表记录初始创建或是在当前子事务中被清理的记录会补冻结，没有游标会打开，事务中也没有数据快照。

注意此时其他的事务会立刻看见刚加载的数据。这不符合MVCC正常的可见性规则，用户应注意这可能带来的潜在问题。

DELIMITER

指定分隔每一行记录中的列的字符。默认是文本格式的制表符， CSV 格式的逗号。必须有一个独立的一字节的字符。在使用 binary 格式时这个选项是不允许的。

声明代表一个空值的字符串。默认是文本格式的 \N ， CSV 格式的一个未被引用的空字符串。即使是文本格式您可能也更偏向于空串，例如您不想从空字符串中区分空值。在使用 binary 格式时这个选项是不允许的。

注意: 在使用 COPY FROM 的时候，任何匹配这个字符串的字符串将被存储为NULL值，所以你应该确保你用的字符串和 COPY TO 相同。

HEADER

声明文件包含一个带有文件中每列名称的标题行。在输出时，第一行包含表中的列名，在输入时，第一行是被忽略的。该选项仅仅在使用 CSV 格式时是允许的。

QUOTE

指定引用数据的引用字符。默认的是双引号。这一定是一个1字节的字符。该选项仅仅在使用 CSV 格式时允许。

ESCAPE

声明应该出现在一个匹配 QUOTE 值的数据字符之前的字符。默认与 QUOTE 值相同（所以若它出现在数据中，则引用字符是翻一倍）。这一定是一个1字节的字符。该选项只有在使用 CSV 格式时允许。

FORCE_QUOTE

强制引用在每个指定列的所有非 NULL 值。 NULL 从不被引用。如果声明了 * ，非 NULL 值将在所有列中被引用。这个选项仅仅在 COPY TO 中并且仅仅在使用 CSV 格式时允许。

FORCE_NOT_NULL

默认情况下空字符串是空的，这意味着空值将会被读作长度为零的字符串而不是空值，即使当他们不被引用。这个选项仅仅在 COPY FROM 中并且仅仅在使用 CSV 格式时允许。

ENCODING

声明文件的编码集是 encoding_name 。如果这个选项省略，则系统使用当前的用户编码集。阅读下面的注意事项以了解更多内容。

Outputs

当 COPY 命令执行成功后，会在屏幕上显示

 COPY count

式样内容，这里 count 是已拷贝成功的记录数。

COPY 只能用于表，不能用于视图。当然也可以用于 COPY (SELECT * FROM viewname ) TO ...

COPY 仅仅处理已指定的特定表；它将不复制数据到子表或从子表中复制数据。因此比如 COPY table TO 显示与 SELECT * FROM ONLY table 相同的数据。但是 COPY (SELECT * FROM table ) TO ... 可以用于转储在继承层次结构的所有数据。

你对任何要 COPY TO 出来的数据必须有查询的权限，对任何要 COPY FROM 入数据的表必须有插入权限。对列在命令中的字段拥有列权限也是必须的。

COPY 命令里面的文件必须是由服务器直接读或写的文件，而不是由客户端应用读写。因此，它们必须位于数据库服务器上或者可以被数据库服务器所访问，而不是客户端程序。它们必须被运行 PostgreSQL服务器的用户可读或写，而不是客户端程序。由 PROGRAM 选项指定的命令必须是由服务器来执行的，而不是客户端程序，必须是由 PostgreSQL所属的用户。 COPY 在指定一个程序或是命令时只允许数据库超级用户来执行，因为它允许读写任意服务器有权限访问的文件。

不要混淆 COPY 和 psql应用程序中的 \copy 指令。 \copy 调用 COPY FROM STDIN 或 COPY TO STDOUT ，然后把数据抓取/存储到一个 psql客户端可以访问的文件中。因此，使用 \copy 的时候，文件访问权限是由客户端应用程序而不是服务器端决定的。

建议在 COPY 里的文件名字总是使用绝对路径。在 COPY TO 的时候是由服务器强制进行的，但是对于 COPY FROM ，你的确可从一个相对路径的文件里读取。该路径将解释为相对于服务器的工作目录(通常是数据目录)，而不是客户端的工作目录。

执行一个 PROGRAM 选项指定的命令有可能还会受到操作系统的存取权限控制，如在SELinux下。

COPY FROM 在执行时会触发目标表上所有触发器和检查约束。不过，不会执行规则。

COPY 输入和输出会被 DateStyle 参数影响。为了和其它 PostgreSQL不同服务器间进行数据转移(它们可能是非缺省 DateStyle 设置)，应该在使用 COPY TO 前把 DateStyle 参数值设置为 ISO 。另外也建议在导出数据时，不要将 IntervalStyle 参数设置为 sql_standard 。因为负的区间值可能会被对 IntervalStyle 有不同设置的服务器误解。

输入数据通过 ENCODING 参数或是当前客户端编码来解译，输出数据也是通过 ENCODING 参数或是为当前客户端的编码来编码，即使数据不经过客户端的，仍会通过服务器直接将数据从文件中读出或者写入到文件中去。

COPY 在第一个错误处停下来。这些在 COPY TO 中不应该导致问题，但在 COPY FROM 时目标表会已经接收到早先的行，这些行将不可见或不可访问，但是仍然会占据磁盘空间。如果你碰巧拷贝大量数据文件的话，这些东西积累起来可能会占据相当大的磁盘空间。你可以调用 VACUUM 来恢复那些磁盘空间。

当使用 text 格式时，读写的文件是一个文本文件，每行代表表中一个行。行中的列(字段)用分隔符分开。字段值本身是由与每个字段类型相关的输出函数生成的字符串，或者是输入函数可接受的字符串。数据中使用特定的NULL字符串表示那些值为NULL的字段。如果输入文件的任意行包含比预期多或者少的字段，那么 COPY FROM 将抛出一个错误。如果声明了 OIDS 选项，那么OID将作为第一个字段读写，放在所有用户字段前面。

数据的结束可以用一个只包含反斜杠和句点( \. )的行表示。如果从文件中读取数据，那么数据结束的标记是不必要的，因为文件结束符可以起到相同的作用；但是在3.0之前的客户端协议里，如果在客户端应用之间拷贝数据，那么必须要有结束标记。

反斜杠字符( \ )可以用于 COPY 数据，来引用那些可能会被当作行或列分隔符的数据字符。特别是以下字符，若以一列值的一部分出现则必须在前面加上反斜杠：反斜杠、换行符、回车以及当前的分隔符字符。

声明的空字符串被 COPY TO 不加任何反斜杠发送；与之相对， COPY FROM 在删除反斜杠之前拿它的输入与空字符串比较。因此，像 \N 这样的空字符串不会和实际数据值 \N 之间混淆(因为后者会表现成 \\N )。

COPY FROM 能够识别下列特殊反斜杠字符：

目前， COPY TO 绝不会发出一个八进制或者十六进制反斜杠序列，但是它的确使用了上面列出的其它字符用于控制字符。

任何其他未在上表中提及的斜字符将会用来表示其本身。然而，也要注意不必要的情况添加反斜杠。因为这可能意外地生成一个匹配数据结束标记( \. )或者空字符串 (默认为 \N ）的字符串。这些字符串将在任何其他反斜杠处理做完之前确认。

强烈建议产生 COPY 数据的应用程序将数据换行符和回车分别转换为 \n 和 \r 序列。目前，可以由反斜杠和回车代表一个数据回车，并且由反斜杠和换行符代表一个数据换行。然而，这些表示法在将来的版本中可能无法接受。 COPY 文件在不同操作系统之间转移时，它们也非常容易被误解读，（例如：从Unix 系统移到Windows系统，或者反过来）。

COPY TO 将在每行的结尾用一个Unix风格的换行符( " \n ")。运行在Windows上的服务器会输出的回车换行符( " \r\n ")，但只是用于 COPY 到服务器文件里；为了在不同平台之间一致， COPY TO STDOUT 总是发送 " \n "而不管服务器平台是什么。 COPY FROM 可以处理那些以回车符、换行符、回车/换行符作为行结束的数据。为了减少在数据中出现的未转义的新行或者回车导致的错误，如果输入的行结尾不像上面这些符号， COPY FROM 会发出警告。

CSV 格式

这个格式用于输入和输出逗号分隔数值( CSV )文件格式，许多其它程序都用这个文件格式，比如电子表格。这个模式下生成并识别逗号分隔的CSV转义机制，而不是使用 PostgreSQL标准文本的转义模式。

每条记录的值都是用 DELIMITER 字符分隔的。如果数值本身包含分隔字符、 QUOTE 字符、 NULL 字符串、回车符、换行符，那么整个数值用 QUOTE 字符前缀和后缀(包围)，并且数值里任何 QUOTE 字符或 ESCAPE 字符都前导转义字符。你也可以使用 FORCE_QUOTE 在输出非 NULL 的指定字段值时强制引号包围。

CSV 格式没有标准的办法区分一个 NULL 值和一个空字符串。 PostgreSQL的 COPY 通过引号包围来处理这些。一个当作 NULL 输出的 NULL 参数值是没有引号包围的，而匹配非 NULL 字符串的参数值是用引号包围的。比如，使用缺省设置时，一个 NULL 是写做一个无引号包围的空字符串，而一个空字符串数值写做双引号包围( "" )。读取数值也遵循类似的规则。你可以使用 FORCE_NOT_NULL 来避免为特定字段进行 NULL 比较。

因为对于 CSV 格式而言，反斜杠不是特殊字符，数据的结束标志( \. ) 可以作为数据值出现。为了避免任何可能的歧意，一个单独的 \. 数据值在输出中将被自动使用引号包围；在输入中，如果被引号界定，那么将不会当作数据结束标志。如果你要加载其它程序创建的、有未用引号界定字段的文件，并且其中含有 \. 值，你就必须用引号进行界定。

注意: 在 CSV 模式下，所有字符都是有效的。一个被空白包围的引号界定数值，或者任何非 DELIMITER 字符，都会被包含这些字符。如果你给 CSV 行填充空白的系统里导入数据到定长字段，那么可能会导致错误。如果出现这种情况，你可能需要先处理一下 CSV 文件，删除结尾空白，然后再向 PostgreSQL里导入数据。

二进制格式

binary 形式的选项会使得所有的数据被存储/读作二进制格式而不是文本。这比文本和 CSV 格式的要快一些，但是一个二进制格式文件在机器架构和 PostgreSQL版本之间的可移植性比较差。另外，二进制格式是对数据类型有一定要求的；例如，不能从 smallint 列中输出二进制数据并将二进制数据读入 integer 列，尽管在文本格式下那会运行良好。

binary 文件格式包含一个文件头，0或更多包含行数据的元组，以及一个文件尾。头和数据按照网络字节顺序。

注意: 7.4版本之前的 PostgreSQL版本使用的是不同的二进制文件格式。

文件头由15个字节的固定域组成，后面跟着一个变长的头扩展区。固定域是：

11字节的序列 PGCOPY\n\377\r\n\0 —，请注意字节零是签名必须的一部分。（使用这个签名是为了能够很容易看出文件是否已经被一个非8位安全的转换器给破坏了。这个签名会被行尾转换过滤器、删除字节零、删除高位、奇偶变化而改变。）

32位整数掩码表示该文件格式的重要方面。位是从 0( LSB )到 31( MSB ) 编码的，请注意这个域是以网络字节顺序存储的(高位在前)，后继的整数都是如此。位16-31是保留用做关键文件格式信息的；如果阅读器发现一个不认识的位出现在这个范围内，那么它应该退出。位0-15都保留为标志向后兼容的格式使用；阅读器可以忽略这个范围内的不认识的位。目前只定义了一个标志位，而其它的必须是零：

Bit 16

如果为1，那么在数据中包括了OIDS；如果为0，则没有。

头扩展范围长度

32位整数，以字节计的头剩余长度，不包括自身。目前，它是零，后面紧跟第一条记录行。对该格式的更多修改都将允许额外的数据出现在头中。阅读器应该忽略任何它不知道该如何处理的头扩展数据。

头扩展数据用来保存自定义数据序列块。这个标志域无意告诉阅读器扩展区的内容是什么。头扩展的具体设计内容留给以后的版本使用。

这样设计就允许向后兼容的头扩展(增加头扩展块或设置低位序标志位)以及非向后兼容的修改(设置高位标志位以标识这样的修改，并且根据需要向扩展区域增加支持数据)。

每条行都以一个16位整数计数开头，该计数是行中字段的数目(目前，在一个表里的每行都有相同的计数，但可能不会永远这样)。然后后面不断出现行中的各个字段，字段先是一个32位的长度字，后面跟着很多的字段数据。长度字并不包括自己，并且可以为零。一个特例是：-1表示一个NULL字段值。在NULL情况下，后面不会跟着数值字节。

在数据域之间没有对齐填充或者任何其它额外的数据。

目前，一个二进制格式文件里的所有数据值都假设是二进制格式的(格式代码为一)。预计将来的扩展可能增加一个头域，允许为每个字段声明格式代码。

为了判断实际行数据的正确二进制格式，你应该阅读 PostgreSQL源代码，特别是该字段数据类型的 *send 和 *recv 函数 (这些函数可以在源代码的 src/backend/utils/adt/ 目录找到)。

如果在文件中包括了OIDs，那么该OID域立即跟在字段计数字后面。它是一个普通的字段，只不过它没有包括在字段计数。但它包括长度字，这样就允许方便的处理4字节和8字节的OIDs，并且如果某个家伙允许OIDs是可选的话，那么还可以把OIDs显示成空。

文件尾包括保存着-1的一个16位整数字。这样就很容易与一条行的域计数字相区分。

如果一个域计数字既不是-1也不是预期的字段的数目，那么阅读器应该报错。这样就提供了对丢失与数据同步的额外检查。

把'A'开头的国家名拷贝到一个文件里：

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

可以通过将输出数据通过管道方式重定向至一个外部压缩程序的方式将数据拷贝至一个压缩文件中：

COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

下面是一个可以从 STDIN 中拷贝数据到表中的例子：

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

注意，每行里的空白实际上是一个水平制表符。

下面的是同样的数据，以二进制形式输出。这些数据是用Unix工具 od -c 过滤之后输出的。该表有三个字段；第一个是 char(2) ，第二个是 text ，第三个是 integer 。所有的行在第三个域都是一个null值。

0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
0000100   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 377 377
0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
0000200   M   B   A   B   W   E 377 377 377 377 377 377

[ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE NOT NULL column_name [, ...] ] ] ] COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ] [ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE QUOTE { column_name [, ...] | * } ] ] ]

请注意：在这个语法中， BINARY 和 CSV 是作为独立的关键字，而不是作为 FORMAT 选项的一个参数。

PostgreSQL7.3以前使用下面的语法，现在仍然支持：

COPY [ BINARY ] table_name [ WITH OIDS ]
    FROM { 'filename' | STDIN }
    [ [USING] DELIMITERS 'delimiter' ]
    [ WITH NULL AS 'null string' ]
COPY [ BINARY ] table_name [ WITH OIDS ]
    TO { 'filename' | STDOUT }
    [ [USING] DELIMITERS 'delimiter' ]
    [ WITH NULL AS 'null string' ]

http://www.postgres.cn/docs/9.3/sql-copy.html

https://www.cnblogs.com/xiaodf/p/5027196.html