postgresql问题小结

postgresql的copy命令

参考链接：https://www.cnblogs.com/alianbog/p/5621660.html

 

1、将整张表拷贝至标准输出

test=# copy tbl_test1 to stdout;
1 HA 12
2 ha 543

 

2、将表的部分字段拷贝至标准输出，并输出字段名称，字段间使用','分隔

test=# copy tbl_test1(a,b) to stdout delimiter ',' csv header;
a,b
1,HA
2,ha

 

3、将查询结果拷贝至标准输出

test=# copy (select a,b from tbl_test1 except select e,f from tbl_test2 ) to stdout delimiter ',' quote '"' csv header;
a,b
2,ha

 

将标准输入拷贝至表中需要注意几点

1.字段间分隔符默认使用【Tab】键

2.换行使用回车键

3.结束使用反斜线+英文据点(\.)

4.最好指定字段顺序，要不然可能会错位赋值

 

4、将标准输入拷贝至表中

test=# copy tbl_test1(a,b,c) from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1 公举 公主
>> 2 万岁 万万岁
>> \.
COPY 2
test=# select * from tbl_test1 ;
a | b | c
---+------+--------
1 | HA | 12
2 | ha | 543
1 | 公举 | 公主
2 | 万岁 | 万万岁
(4 rows)

 

5、从标准输入拷贝至表中，并将标准输入第一行作为字段名(和表中不符也没关系，copy会自动忽略第一行)，字段分隔符为','

test=# copy tbl_test1(a,b,c) from stdin delimiter ',' csv header;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> a,b,c
>> 3,你好,hello
>> 4,超人,super
>> \.
COPY 2
test=# select * from tbl_test1 ;
a | b | c
---+------+--------
1 | HA | 12
2 | ha | 543
1 | 公举 | 公主
2 | 万岁 | 万万岁
3 | 你好 | hello
4 | 超人 | super
(6 rows)

 

以上是表与标准输出和标准输入间的相互拷贝，表与文件的拷贝和以上完全相同，只是将标准输出和标准输入换成文件。需要注意的是：

1.数据库用户必须有文件所在的路径的写权限。

2.如果表存在中文字符，导出至csv文件时需要设置编码为GBK，否则使用excel打开是中文显示乱码。

3.将文件导入表中时仍要考虑编码问题

 

6、将表拷贝至csv文件中

test=# copy tbl_test1 to '/tmp/tbl_test1.csv' delimiter ',' csv header;
COPY 6

 

使用excel打开文件，中文显示为乱码

 

 

7、将表以GBK编码拷贝至csv文件中

test=# copy tbl_test1 to '/tmp/tbl_test1.csv' delimiter ',' csv header encoding 'GBK';
COPY 6

 

使用excel打开，中文显示正常

 

 

8、将刚才导出的文件再次拷贝至表中，使用默认编码UTF8 

test=# copy tbl_test1(a,b,c) from '/tmp/tbl_test1.csv' delimiter ',' csv header;
ERROR: invalid byte sequence for encoding "UTF8": 0xb9
CONTEXT: COPY tbl_test1, line 4

 

 

 

9、将刚才导出的文件再次拷贝至表中，使用GBK编码

test=# copy tbl_test1(a,b,c) from '/tmp/tbl_test1.csv' delimiter ',' csv header encoding 'GBK';
COPY 6

 

 10、postgresql中没有ifnull函数，可以使用coalesce达到效果

 

coalesce函数传入列名作为参数，查询结果中，当该列名的字段值为null时，会返回下一个参数的值，以此类推，如果每个参数都为null，结果返回null

 

SELECT 
COALESCE(wav."name",'（未知）') AS "name",
"count"(wat."id") AS "value"
FROM 
wdy_asset_types wat LEFT JOIN wdy_asset_venders wav ON wat.vender_id=wav."id"
WHERE 
EXTRACT(YEAR FROM wat.created_at)=2017
GROUP BY wav."name"