psql 输出结果导入到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; 

5、生成的tank.csv格式如下

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

防止止忘记
tank.csv 文件要有写入权限

Copy (SELECT departments.name AS 部门,users.name AS 员工 ,COUNT(*) as 总数 FROM customers INNER JOIN users ON users.id = customers.user_id JOIN departments ON users.department_id = departments.id GROUP  BY users.name,departments.name) To '/home/lola/test.csv' With CSV DELIMITER ',';

COPY (SELECT departments.name AS 部门,users.name AS 员工 ,COUNT(*) as 总数 FROM customers INNER JOIN users ON users.id = customers.user_id JOIN departments ON users.department_id = departments.id GROUP  BY users.name,departments.name) to '/home/lola/test11.csv' with csv header;