Oracle/DB2/Postgresql/Mysql/Hadoop/Greenplum/Postgres-xl/Mongodb
分类: Mysql/postgreSQL
2013-01-19 22:35:49
Postgresql数据库导入和导出CSV格式
1、生成csv格式文件样式
2 | tank | 11 | 31 |
3 | zhang | 11 | 32 |
4 | tom | 10 | 33 |
5 | gao | 11 | 34 |
6 | tank | 12 | 35 |
7 | zhang | 13 | 36 |
8 | tom | 14 | 37 |
9 | gao | 15 | 38 |
10 | tank | 16 | 39 |
11 | zhang | 17 | 40 |
12 | tom | 18 | 41 |
2、建立好表结构
create table tanktab(id integer,name varchar(20),departno integer,age integer);
3、导入postgresql数据库表tanktab中(注意导入用户必须要有超级管员权限 OS:环境语言(LANG=zh_CN.GBK)否则导出中文乱码)
[postgres@db ~]$ echo $LANG
zh_CN.GBK
COPY tanktab from '/u02/tank/123456.csv' with csv header;
4、从postgresql数据库表tanktab中导出csv格式文件//(OS:环境语言(LANG=zh_CN.GBK)否则导出中文乱码)
[postgres@db ~]$ echo $LANG
zh_CN.GBK
数据库编码:
tank=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | tank | tank | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) tank=#
COPY (select * from tanktab) to '/u02/tank/tank.csv' with csv header;
id | name | departno | age |
2 | tank | 11 | 31 |
3 | zhang | 11 | 32 |
4 | tom | 10 | 33 |
5 | gao | 11 | 34 |
6 | tank | 12 | 35 |
7 | zhang | 13 | 36 |
8 | tom | 14 | 37 |