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

1. psql 导入/导出数据

psql支持文件数据导入到数据库，也支持数据库表数据导出到文件中。 COPY命令和\copy 命令都支持这两类操作，但两者有如下区别：

COPY 命令是SQL命令，\copy 命令是元命令.
COPY 命令必须具有SUPERUSER超级权限（将数据通过stdin/stdout方式导入导出情况除外），而 \copy 元命令不需要SUPERUSER权限。
COPY 命令读取或写入数据库服务端主机上的文件，而 \copy 元命令是从psql客户端主机读取或写入文件。
从性能方面看，大数据量导出文件或大文件数据导入数据库，COPY 比 \copy 性能高。

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;
1 a
2 b
3 c

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_subss" to '/mnt/postgres/11/backup/cloud_subs.csv' with csv header;
postgres#copy "pr-records" to '/mnt/postgres/11/backup/pr-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
-------
  1000
(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。

OIDS

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

FREEZE

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

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

DELIMITER

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

NULL

声明代表一个空值的字符串。默认是文本格式的\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能够识别下列特殊反斜杠字符：

字符形式	字符含义
`\b`	反斜杠 (ASCII 8)
`\f`	进纸 (ASCII 12)
`\n`	换行符 (ASCII 10)
`\r`	回车符 (ASCII 13)
`\t`	水平制表符 (ASCII 9)
`\v`	垂直制表符 (ASCII 11)
`\digits`	反斜杠后面跟着一到三个八进制数，表示ASCII值为该数的字符
`\xdigits`	反斜杠`\x`后面跟着一个或两个十六进制位声明指定数值编码的字符

目前，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里导入数据。

注意: CSV格式可以识别和生成引号包围的回车和换行的CSV文件。因此这些文件并不像文本模式的文件那样严格地每条记录一行。

注意: 许多程序生成奇怪的并且有时候不正确的CSV文件，所以这个文件格式更像一种惯用格式，而不是一种标准。因此你可能碰到一些不能使用这个机制输入的文件，而COPY也可能生成一些其它程序不能处理的文件。

二进制格式

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也不是预期的字段的数目，那么阅读器应该报错。这样就提供了对丢失与数据同步的额外检查。

例子

下面的例子把一个表拷贝到客户端，使用竖线(|)作为域分隔符：

COPY country TO STDOUT (DELIMITER '|');

从文件中拷贝数据到country表中：

COPY country FROM '/usr1/proj/bray/sql/country_data';

把'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

兼容性

SQL标准里没有COPY语句。

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

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ 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

posted @ 2019-12-19 16:40 TonyBen2018 阅读(2413) 评论(0) 编辑收藏举报

刷新页面返回顶部