psql使用技巧

psql cli设置

?

help，查看命令文档，下面列举几个我常用的命令：

1、pset命令

pset NAME VALUE     set table output option

设置表格的输出格式，我用到过的设置有：

关闭默认的分页

pset pager off    

设置表格的输出结果自动换行，对于内容特别大的字段，很有用。

pset format wrapped

2、copy

把sql输出结果copy到一个文件中

copy (select * from $table) to '/tmp/1.csv';

从csv文件中导入数据到表中

copy $table_name from '/tmp/1.csv';

3、执行sql文件

 i FILE                execute commands from file

执行文件1.sql

i /tmp/1.sql

4、connect命令

connect {DBNAME|- USER|- HOST|- PORT|- | conninfo}
                         connect to new database (currently `view_new`)

切换db

connect $dbname

一些导数据的命令

postgresql的sql语句与mysql不同的地方：

1、in subquery

2、时间戳转换成date

to_timestamp(double precision)	

3、schema

一个database下有多个schema，每个schema下有多张表

list schemas:

select nspname from pg_catalog.pg_namespace;
      nspname       
--------------------
 pg_toast
 pg_temp_1
 pg_toast_temp_1
 pg_catalog
 public
 information_schema

4、创建用户、并分配权限

CREATE USER $username WITH PASSWORD ' $password ';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO $username;

给用户赋予了所有表的读权限，但是，这个读权限，只对库中当前所有的表有效，当新增表的时候，又会提示没有权限，就需要执行下面两条命令：

alter default privileges in schema public grant all on tables to $username;
alter default privileges in schema public grant all on sequences to $username;

5、修改last_value

6、timestamp compare

类型为timestamp的字段比较：

7、报错： psql version和postgresql server version不匹配

我安装了两个postgresql，而psql和server指向的是不同的版本，可以通过location找到所有安装的psql版本

8、centos启动postgresql:

// 切换到postgres用户
su postgres
systemctl start postgresql-9.3.service

9、导出表结构

pg_dump -t res_county_source --schema-only view_new -U postgres

10、psql查看执行时间长的sql，并kill掉

11、postgresql日志、慢查询日志、日志位置

12、性能优化

13、查看表占用的空间

What's the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)

14、update子查询语法

update out_worker_log  set pk = sub.pk from (select pk,hash_id from out_worker) as sub where out_worker_log.hash_id=sub.hash_id;

参考updating table rows in postgres using subquery