使用PostgreSQL copy命令binary格式从高版本导出低版本导入

PostgreSQL 12rc1 copy命令 binary格式导出data1表

postgres=# insert into data1 values(1,'aa');
INSERT 0 1
postgres=# copy data1 to 'd:\data1_bin.bin' binary;
COPY 1
postgres=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 12rc1, compiled by Visual C++ build 1900, 64-bit
(1 row)


postgres=#

PostgreSQL 11.5 copy命令 binary格式导入data1表

postgres=# create table data1(c1 numeric,c2 varchar(10));
CREATE TABLE
postgres=# copy data1 from  'd:\data1_bin.bin' binary;
COPY 1
postgres=# select * from data1;
 c1 | c2
----+----
  1 | aa
(1 行记录)


postgres=# select version();
                          version
------------------------------------------------------------
 PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit
(1 行记录)


postgres=#

PostgreSQL 10.1 copy命令 binary格式导入data1表

postgres=# copy s_lei.data1 from 'd:\data1_bin.bin';--->>>必须带binary格式，否则报如下错误
ERROR:  invalid input syntax for type numeric: "PGCOPY"
背景:  COPY data1, line 1, column c1: "PGCOPY"
postgres=# copy s_lei.data1 from 'd:\data1_bin.bin' binary;
COPY 1
postgres=# select * from s_lei.data1;
 c1 | c2
----+----
  1 | aa
(1 行记录)


postgres=# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 10.1 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
(1 行记录)


postgres=#