psql切换schema_psql使用技巧

Setting up psql, the PostgreSQL CLI​tapoueh.org

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命令

\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}

connect to new database (currently "view_new")

切换db

\connect $dbname

一些导数据的命令

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

1、in subqueryPostgreSQL IN operator with subquery poor performance​stackoverflow.com

2、时间戳转换成date

to_timestamp(double precision)9.9. Date/Time Functions and Operators​www.postgresql.org

3、schemaDocumentation: 9.1: Schemas​www.postgresql.org

一个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;Documentation: 9.0: GRANT​www.postgresql.org

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

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

alter default privileges in schema public grant all on sequences to $username;Grant privileges on future tables in PostgreSQL?​stackoverflow.com

5、修改last_valuePostgres manually alter sequence​stackoverflow.com

6、timestamp compare

类型为timestamp的字段比较：https://stackoverflow.com/questions/31433747/postgres-where-clause-compare-timestamp​stackoverflow.com

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掉https://medium.com/little-programming-joys/finding-and-killing-long-running-queries-on-postgres-7c4f0449e86d​medium.com

11、postgresql日志、慢查询日志、日志位置Error Reporting and Logging​www.postgresql.org

12、性能优化https://yq.aliyun.com/articles/166​yq.aliyun.comPgSQL · 性能优化 · 如何潇洒的处理每天上百TB的数据增量​mysql.taobao.orghttp://blog.163.com/digoal@126/blog/static/163877040201221382150858/​blog.163.com

13、查看表占用的空间

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;