导入部分指定列

• 复制
  复制成功

postgres=#  copy t(f1,f2) to '/data/pgxz/t.txt'; 
postgres=#  \! cat /data/pgxz/t.txt
1       TDSQL PG
2       pg'",      xc%
3       pgxz
postgres=# truncate table t;
TRUNCATE TABLE
postgres=# copy t(f1,f2) from  '/data/pgxz/t.txt';     
COPY 3
postgres=# select * from t;
 f1 |       f2       |             f3             | f4 
----+----------------+----------------------------+----
  1 | TDSQL PG       | 2017-10-30 11:54:16.559579 |   
  2 | pg'",      xc% | 2017-10-30 11:54:16.559579 |   
  3 | pgxz           | 2017-10-30 11:54:16.560283 |   
(3 rows)
#有默认值的字段在没有导入时，会自动的将默认值付上
postgres=# \! cat /data/pgxz/t.txt 
1       \N      TDSQL PG
2       2017-10-28 18:24:05.643102      pg'",      xc%
3       2017-10-28 18:24:05.645691      pgxz
postgres=# truncate table t;
TRUNCATE TABLE
postgres=# copy t(f1,f3,f2) from '/data/pgxz/t.txt'; 
COPY 3
postgres=# select * from t;
 f1 |       f2       |             f3             | f4 
----+----------------+----------------------------+----
  1 | TDSQL PG       |                            |   
  2 | pg'",      xc% | 2017-10-28 18:24:05.643102 |   
  3 | pgxz           | 2017-10-28 18:24:05.645691 |   
(3 rows)
#字段的顺序可以任意调整，但需要与导放文件的存放顺序一致
postgres=# \! cat /data/pgxz/t.txt;
1       TDSQL PG   \N      7
2       pg'",      xc%  2017-10-28 18:24:05.643102      3
3       pgxz    2017-10-28 18:24:05.645691      \N
postgres=# copy t (f1,f2) from  '/data/pgxz/t.txt';
ERROR:  extra data after last expected column
CONTEXT:  COPY t, line 1: "1    TDSQL PG   \N      7"