用COPY命令从csv文件中导入数据

开始

csv 文件的内容：

id    name    departno    age
1    gao    10    30
2    jian    11    35
3    tom    11    30

导入前：

postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid;
 relpages | reltuples | relfilenode | reltype | typname 
----------+-----------+-------------+---------+---------
(0 rows)

postgres=# 

导入之前，必须要建立好表的结构

postgres=# create table gaotab(id integer,name varchar(20),departno integer,age integer);
CREATE TABLE
postgres=# 
postgres=# 
postgres=# COPY gaotab from '/soft/test.csv' with csv header;COPY 3
postgres=# select * from gaotab;
 id | name | departno | age 
----+------+----------+-----
  1 | gao  |       10 |  30
  2 | jian |       11 |  35
  3 | tom  |       11 |  30
(3 rows)

postgres=# 

导入已经成功

导入后再看：

postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid;
 relpages | reltuples | relfilenode | reltype | typname 
----------+-----------+-------------+---------+---------
        0 |         0 |       16384 |   16386 | gaotab
(1 row)

postgres=# 



postgres=# analyze gaotab;
ANALYZE
postgres=# select a.relpages, a.reltuples, a.relfilenode,a.reltype,b.typname from pg_class a, pg_type b where a.relname like 'gaotab%' and a.reltype=b.oid;
 relpages | reltuples | relfilenode | reltype | typname 
----------+-----------+-------------+---------+---------
        1 |         3 |       16384 |   16386 | gaotab
(1 row)

postgres=# 

[作者：技术者高健@博客园  mail: luckyjackgao@gmail.com ]

结束