【PG常用查询汇总】

1、查看用户
\du
2、查看数据库
\l
3、查看当前用户名
select * from current_user;
select user;
4、查看当前实例下的表
\dt
select tablename from pg_tables;
5、查看表结构
\d tablename
6、切换到指定数据库
\c postgres
\c postgres tbase  ----》以tbase用户切换到postgres7、查看帮助
postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send results to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

对于我来说，pg的这套用户、schema、database管理还是挺难理解的，我简答说说我的理解：1、在Oracle中只有一个database，不需要create database，新建一个用户，就会有一个schema，不同schema之间是逻辑隔离的，不同schema下可以有相同的名字的表。访问不同schema下的表，需要在表名前加username.tablename。2、在MySQL中可以建多个database，这个database实际上相当于不同的文件夹，不同的database下，可以有相同名字的表；用户可以在不同的database下建表；一般一个正常的应用账户授予某个database的所有权限即可。3、在Pg中可以新建多个database，在新建database的时候可以指定用户作为其拥有者，在database下，可以建不同的schema，这个schema是属于database和用户的；一般新建一个用户建议建一个同名的schema。如果不建schema，那么会有一个名叫public的schema，每一个用户都可以在这个public schema下建表，但是这个public schema是公共的，不同的用户不可以创建相同的名称的对象。=========建用户==========================create user test with password '123456';
 =====创建数据库并指定拥有者
create database zjtg owner test;=======授予用户创建数据库的全下alter user test with createdb;
======给指定数据库的所有权限绑定用户
grant all privileges on database zjtg to test;
======切换到testdb和test用户下创建同名schema
create schema test;
======创建schema，并指定拥有者
create schema schema_data_out authorization test;
=======创建只读用户readonly
create user readonly with password 'password';
=======授权用户使用test schema权限，需要切换到对应的database和用户下
grant usage on schema test to readonly;
=========授权readonly用户查看新创建表的select 权限，需要切换到对应的database和超级用户下，如果需要授予对表的访问权，还必须确保角色对表所在的数据库和模式具有访问权
alter default privileges in schema public grant select on tables to readonly;
=========授权用户查看schema test下的所有表（此时库的的所有表，新增表不能看到）
grant select on all tables in schema public to readonly;
=========授予指定schema 下所有数据表及序列的权限，需要切换到对应的database和超级用户下
grant all privileges on all tables in schema public to tkk123;
grant all privileges on all sequences in schema public to tkk123;

注意：上面的授权可能只对历史的一些对象授权，后期增加的对象是没有权限的，需要给个默认权限
========赋予默认数据表的权限，需要切换到对应的database和用户下
alter default privileges in schema public grant all privileges on tables to tkk123;
========赋予默认序列的权限，需要切换到对应的database和用户下
alter default privileges in schema public grant all privileges on sequences to tkk123;========创建超级用户
create user test2 superuser;
========创建普通用户  并授权
create user test createdb createrole inherit password 'test';
=======将超级用户修改为非超级用户
alter user test nosuperuser;
=======修改用户密码
alter user test2 password 'test';
=======赋予普通用户a创建数据库的权限
alter user test createdb;
========设置search_path，登录的用户会在search_path指定的schema下，查找对象set search_path = "$user", public, my_schema

1、查看某用户的系统权限

SELECT * FROM  pg_roles WHERE rolname='test';

2、查看某用户的表权限，切换到对应的database和用户下

select * from information_schema.table_privileges where grantee='postgres';

3、查看某用户的usage权限

select * from information_schema.usage_privileges where grantee='postgres';

4、查看某用户在存储过程函数的执行权限

select * from information_schema.routine_privileges where grantee='postgres'; 

5、查看某用户在某表的列上的权限

select * from information_schema.column_privileges where grantee='postgres'; 

6、查看当前用户能够访问的数据类型

select * from information_schema.data_type_privileges ; 

7、查看用户自定义类型上授予的USAGE权限

select * from information_schema.udt_privileges where grantee='postgres';

根据表查看锁
select oid from pg_class where relname='lockTableName';
select pid from pg_locks where relation='上面查出的oid';
--如果查询到了结果，表示该表被锁 则需要释放锁定
select pg_cancel_backend(上面查到的pid);=========查询某database下的进程，并杀掉会话select * from pg_stat_activity where datname='benchmarksql' and pid <> pg_backend_pid();
select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='benchmarksql' and pid <> pg_backend_pid();

• 查看执行计划

查看执行计划
explain analyze +sql

这里option可选项如下：
ANALYZE [boolean ]

VERBOSE [boolean ]

COSTS [ boolean]

BUFFERS [boolean ]

TIMING [ boolean]

SUMMARY [boolean ]

FORMAT { TEXT |XML | JSON | YAML }

注意EXPLAIN默认不执行语句,只显示估算信息，EXPLAIN ANALYZE会实际执行语句且输出真实消耗信息，当我们诊断DML语句时就要格外小心ANALYZE选项以免影响生产数据。

SELECT
	tablename 
FROM
	pg_tables 
WHERE
	tablename NOT LIKE'pg%' 
	AND tablename NOT LIKE'sql_%' 
ORDER BY
	tablename; =========查看数据库的oid=================
select oid,datname from pg_database;
============查看表的oid=========================
select oid,relname,relkind,relfilenode from pg_class where relname ='ha';

================切换日志
select pg_switch_wal();================查看服务器进程select pid from pg_stat_activity;