PostgreSQL命令速查 – 基础

参考Postgres XL官方文档PostgreSQL官方文档

登入退出

# 登入psql
/opt/PostgreSQL/9.5/bin/psql -U pguser
/opt/PostgreSQL/9.5/bin/psql -h 127.0.0.1 -p 5432 -U pguser -d postgres
/opt/PostgreSQL/9.5/bin/psql "postgresql://myuser:mypwd@172.28.2.11:21002/mytestdb"

# 退出psql
\q

反斜线命令

# 显示所有反斜线命令描述
\?

# 显示SQL命令描述
\h
\h ALTER TABLE

# 设置或恢复将查询结果输出到文件或管道
\o /tmp/result.file
SELECT * FROM foo;
\o

# 执行文档中的SQL语句
\i /tmp/batch.sql

# 每隔N秒执行一次上个查询
SELECT * FROM foo;
\watch 2

# 开启或关闭执行耗时
\timing on
\timing off

# 显示当前连接信息
\conninfo

# 查看历史操作
\s

# 打开文本编辑器编写复杂语句
\e

# 退出psql
\q

# 查看编码字符集
\encoding

角色(用户USER、组GROUP是角色ROLE的别名)和权限

# 之所以不在结构上明确区分用户、组、角色,大概是为了兼顾权限可以直接分配在这三个任何一个上。在落地实施的时候可以规划适合自己的权限方案
# 例如,允许登陆的是用户,不允许登陆的当作组或角色,权限挂在角色上,用户通过继承角色获得权限
# 显示所有角色
\du
\du+
SELECT * FROM pg_roles;

# 显示能够登录的角色
SELECT * FROM pg_user;

# 创建不能登录的角色
CREATE ROLE mfsso;

# 修改使角色能够登录
ALTER ROLE mfsso LOGIN;

# 创建能登陆的角色
CREATE ROLE mfsso LOGIN PASSWORD 'mfssopwd';
CREATE USER jack;

# 修改用户密码
\password dbuser

# 删除角色
DROP ROLE mfsso;

# 修改角色名称
ALTER ROLE jack RENAME TO tom;

# 设置角色有效期限
ALTER ROLE jack VALID UNTIL '2018-02-14';
ALTER ROLE tom VALID UNTIL 'May 4 12:00:00 2015 +1';
ALTER ROLE peter VALID UNTIL 'infinity';

# 切换当前角色
SET ROLE jack;
SET ROLE NONE;
RESET ROLE;

# 修改角色属性
ALTER ROLE jack WITH LOGIN SUPERUSER CREATEROLE CREATEDB PASSWORD 'jackpwd' CONNECTION LIMIT 100;

# 赋予角色在database、schema、table上的权限,GRANT ... TO public表示赋予所有角色
GRANT CREATE,CONNECT,TEMPORARY,TEMP ON DATABASE mytestdb TO jack;
GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;
GRANT CONNECT ON DATABASE test TO father;
GRANT USAGE ON SCHEMA myschema TO public;
GRANT SELECT ON public.emp TO father;
GRANT UPDATE,INSERT ON postgres TO jack;

# 撤销权限
REVOKE ALL ON mytestdb FROM jack;

# 角色继承,可以继承父角色的权限(不包含CREATEDB这类的角色属性)
# 可以从逻辑上将角色分为权限组和用户两类,权限组被赋予各种权限且不可登陆,用户继承多个权限组且可登陆
CREATE ROLE jackson INHERIT IN ROLE jack;
GRANT existfather TO existson;
GRANT user_query TO jack;

# 修改角色参数,仅对登录生效,SET ROLE和SET SESSION AUTHORIZATION不受影响
ALTER ROLE jack SET maintenance_work_mem = 100000;
ALTER ROLE jack IN DATABASE mytestdb SET client_min_messages = DEBUG;
ALTER ROLE jack IN DATABASE mytestdb SET client_min_messages FROM CURRENT;
ALTER ROLE jack RESET client_min_messages;
ALTER ROLE jack RESET ALL;

表空间(指定文件存放的磁盘目录,创建数据库时通过设置表空间来隔离IO)

# 查看表空间
SELECT * from pg_tablespace;

# 查看表空间大小
SELECT pg_tablespace_size('pg_default');

# 创建表空间
CREATE TABLESPACE my_tablespace LOCATION '/data/pgxl_data/other';

# 删除表空间
DROP TABLESPACE my_tablespace;

数据库

# 显示所有可见数据库
\l
SELECT * FROM pg_database;

# 创建数据库
CREATE DATABASE mytestdb TEMPLATE template0;
CREATE DATABASE mytestdb2 ENCODING 'utf-8' LC_COLLATE 'zh_CN.utf8' LC_CTYPE 'zh_CN.utf8';
CREATE DATABASE sales OWNER salesuser TABLESPACE salesspace CONNECTION LIMIT 100;

# 删除数据库
DROP DATABASE mytestdb2;

# 切换数据库
\c mytestdb
\c mytestdb pguser

# 重命名数据库
ALTER DATABASE mytestdb RENAME TO mytestdb3;

# 修改数据库拥有者
ALTER DATABASE mytestdb OWNER TO pguser2;

# 修改数据库表空间
ALTER DATABASE mytestdb SET TABLESPACE new_tablespace;

# 修改并生效部分参数
ALTER DATABASE mytestdb SET enable_indexscan TO off;
ALTER DATABASE mytestdb RESET enable_indexscan;
ALTER DATABASE mytestdb RESET ALL;

模式(为了减少同一database里对象命名冲突而设计的组,缺省为public)

# 创建模式
CREATE SCHEMA myschema;
# 创建并将该模式关联到指定的角色,以myrole登录将直接在myschema模式下创建对象
CREATE SCHEMA myschema AUTHORIZATION myrole;

# 删除模式
DROP SCHEMA myschema;

# 查询和设置模式搜索路径
SHOW search_path;
SET search_path TO myschema, public;
SET search_path TO myschema;

# 显示所有表
\dt
\dt+
\dp
SELECT * FROM pg_tables WHERE schemaname = 'public';

# 查看表结构
\d mytable01
SELECT * FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'mytable01';

# 新建表:分片属性(DISTRIBUTE BY)为Postgres XL扩展
CREATE TABLE user_info (
    id             serial8         primary key,   --主键
    name           varchar(32)     not null,  --非空约束
    sex            char(2)         default 'F' check (sex in ('F', 'M')),
    age            int4            constraint user_info_c_positive_age check (age > 0 and age < 100),  --条件约束
    height         decimal(3,2),
    is_student     bool            default false,  --默认值
    information    text,
    create_time    timestamp       default now()
) 
--DISTRIBUTE BY REPLICATION
--DISTRIBUTE BY ROUNDROBIN
--DISTRIBUTE BY MODULO (id)
DISTRIBUTE BY HASH (id);  --分片

CREATE TABLE IF NOT EXISTS user_data (
    id             int8            DEFAULT nextval('user_data_id_seq'::regclass) NOT NULL,
    class_id       uuid,
    user_id        serial8         REFERENCES user_info(id) ON DELETE CASCADE,  --外键
    act_time       time with time zone,
    reg_date       date,
    json_data      jsonb           not null default '{}',
    --UNIQUE (class_id, user_id),  --唯一约束
    --FOREIGN KEY (user_id) REFERENCES user_info(id),
    PRIMARY KEY (class_id, user_id)  --联合主键
);

# 新建表:继承表,查询、更新、删除父表默认包含子表数据,配合触发器或规则可以实现数据水平分表
CREATE TABLE user_data_detail(
    --state          char(2)
    CHECK ( reg_date >= DATE '2016-02-01' AND reg_date < DATE '2016-03-01')
) INHERITS (user_data);

CREATE TABLE user_data_detail2() INHERITS (user_data, user_info);

# 新建表:临时表,会话结束后自动删除,可选择在事务结束后删除数据或者删除表,默认保留数据
CREATE TEMP TABLE tmp_note (
    id               uuid,
    xml_data         xml,
    status           int2          default 0,
    score            int8
)
--ON COMMIT DELETE ROWS
ON COMMIT DROP;

# 新建表:不写入WAL的表,通常是临时表。可以提升写效率
CREATE TEMP UNLOGGED TABLE tmp_note (
    id               uuid
);

# 新建表:指定模式
CREATE TABLE myschema."Payment" (
    id               int8          DEFAULT nextval('"Order_id_Seq"'::regclass) NOT NULL,
    "productId"      text          NOT NULL,  --对象名含大写字母需要双引号包装
    price            money         NOT NULL,
    "payResult"      int2          NOT NULL,
    "receiptData"    text,
    token            text          UNIQUE,
    during           tsrange,
    EXCLUDE USING GIST (during WITH &&)       --排除约束,行之间该值不能含有相同的范围
);

# 复制表:复制表字段和表中的数据,不复制索引、约束等。
CREATE TABLE mytb2 AS SELECT * FROM mytb1;
# 复制表:复制表字段,可选是否包含索引、约束等,不复制数据。
CREATE TABLE mytb3 IF NOT EXISTS (LIKE mytb1); 
CREATE TABLE mytb4 (LIKE mytb1 INCLUDING defaults INCLUDING constraints INCLUDING indexes);

# 删除表
DROP TABLE IF EXISTS tmp_note;
DROP TABLE user_message CASCADE;

# 修改表:重命名表
ALTER TABLE IF EXISTS "Payment" RENAME TO payment;
# 修改表:增加字段
ALTER TABLE user_info ADD email VARCHAR(40);
ALTER TABLE user_info ADD COLUMN description text CHECK(description <> '');
# 修改表:修改字段默认值
ALTER TABLE user_info ALTER COLUMN birth SET NOT NULL;
ALTER TABLE user_info ALTER COLUMN birth DROP NOT NULL;
ALTER TABLE user_info ALTER COLUMN is_student SET default true;
ALTER TABLE user_info ALTER COLUMN is_student DROP default;
# 修改表:修改字段类型,如果有隐含类型则自动转换,否则必须使用using指定转换规则
ALTER TABLE user_info ALTER COLUMN birth TYPE text;
ALTER TABLE user_info ALTER id TYPE varchar;
ALTER TABLE user_info ALTER id TYPE int USING id::int;
# 修改表:重命名字段
ALTER TABLE user_info RENAME COLUMN birth TO birthday;
# 修改表:删除字段
ALTER TABLE user_info DROP COLUMN email;
ALTER TABLE user_info DROP COLUMN description CASCADE;
# 修改表:增加主键
ALTER TABLE user_info ADD primary key(sid);
# 修改表:增加外键
ALTER TABLE user_info ADD FOREIGN KEY(sid) REFERENCES user_data(sid) ON update cascade ON delete cascade;
ALTER TABLE user_info ADD FOREIGN KEY(sid) REFERENCES user_data(sid) ON update restrict;
# 修改表:删除外键
ALTER TABLE user_info DROP CONSTRAINT user_info_id_fkey;
# 修改表:增加约束
ALTER TABLE user_info ADD CONSTRAINT unique_user_info_name UNIQUE(name);
ALTER TABLE user_info ADD CHECK(name <> '');

结构描述

COMMENT ON DATABASE mytestdb IS 'Test Database';
COMMENT ON TABLE user_info IS  '用户信息表'; 
COMMENT ON COLUMN user_info.name IS '用户名称';
COMMENT ON SEQUENCE "Order_id_Seq" IS NULL;

序列

# 查询序列
SELECT * FROM information_schema.sequences WHERE sequence_schema = 'public';

# 新建序列
CREATE SEQUENCE "Order_id_Seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

# 删除序列
DROP SEQUENCE public."Order_id_Seq";

# 修改序列
ALTER SEQUENCE "Order_id_Seq" RESTART WITH 105;

# 返回序列当前值
SELECT currval('sequence_name');

# 序列游标向前移动一位,并返回值
SELECT nextval('sequence_name');

索引

# 查看索引
\di
SELECT * FROM pg_index;
SELECT * FROM pg_indexes;
SELECT * FROM pg_stat_user_indexes;

# 创建索引:B-tree索引
CREATE INDEX idx_name ON table_name(column_name);
# 创建索引:Hash索引
CREATE INDEX idx_name ON table_name USING hash (column_name);
# 创建索引:复合索引
CREATE INDEX idx_name ON table_name (column_name1, column_name2);
# 创建索引:唯一索引
CREATE UNIQUE INDEX ON table_name(column_name);
# 创建索引:条件索引
CREATE INDEX ON table_name(lower(column_name));
CREATE INDEX ON table_name((first_name || ' ' || last_name));
CREATE INDEX ON user_info(age) WHERE NOT (age > 18 AND age < 30);

# 重建索引
REINDEX INDEX idx_name;
REINDEX INDEX table_name;
REINDEX INDEX db_name;

# 删除索引
DROP INDEX idx_name;

# 查看索引定义
SELECT b.indexrelid FROM pg_class a, pg_index b WHERE a.oid = b.indrelid AND a.relname = 'user_info';
SELECT pg_get_indexdef(indexrelid);
SELECT pg_get_indexdef('idx_name'::regclass);

视图

# 查看视图
\dv
\dv+
\dp viewortable
SELECT * FROM pg_views WHERE schemaname = 'public';
SELECT * FROM information_schema.views WHERE table_schema = 'public';

# 创建视图
CREATE VIEW view_1 AS SELECT c1, c2, c3 FROM table1;
CREATE VIEW view_2 AS SELECT c1, d1 FROM table1, table2 WHERE table1.t2_id = table2.id;
CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;

# 创建物化视图,与普通视图区别是它持有数据,通常比普通视图查询效率高,可添加索引,但数据实时性不高;与表的区别是它可按照创建时的规则刷新数据。
CREATE MATERIALIZED VIEW mat_view AS SELECT * FROM user_info;

# 刷新物化视图
REFRESH MATERIALIZED VIEW mat_view;

# 删除视图
DROP VIEW view_1;

# 查看视图定义
SELECT pg_get_viewdef('view_1');

批量执行

# 通过psql执行一条SQL语句
/opt/PostgreSQL/9.5/bin/psql -U pguser -c "\l"
/opt/PostgreSQL/9.5/bin/psql -h 127.0.0.1 -p 5432 -U myuser -d mytestdb -c "SELECT * FROM foo;"

# 通过psql执行sqls.file文档中的一批SQL语句
echo 'SELECT * FROM foo;
INSERT INTO foo2 VALUES (1,"abc");' > sqls.file
/opt/PostgreSQL/9.5/bin/psql -U pguser -d mytestdb -f sqls.file

# 通过\i执行sqls.file文档中的一批SQL语句
\i ~/sql/sqls.file

# 执行文件中的sql并传入参数
echo 'SELECT * FROM tb1 WHERE id=:id;' > test.sql
/opt/PostgreSQL/9.5/bin/psql postgres postgres -v id=2 -f /home/postgres/test.sql
/opt/PostgreSQL/9.5/bin/psql postgres postgres -v id=2 -c '\i /home/postgres/test.sql'

导出导入

/usr/local/pgxl/bin/pg_dumpall -h 172.31.32.61 -p 21001 -U postgres --clean --if-exists --inserts | gzip > monsters_all.sql.gz &
/usr/local/pgxl/bin/pg_dumpall -h 172.31.32.61 -p 21001 -U postgres --clean --if-exists -s | gzip > monsters_all_schema.sql.gz &
/usr/local/pgxl/bin/pg_dump -h 172.31.32.61 -p 21001 -U postgres -ab -j 4 -F d -f mfsso mfsso &

gunzip -c monsters_all.sql.gz | /usr/local/pgxl/bin/psql -h 172.31.32.61 -p 21001 -U postgres >import.log 2>&1 &
gunzip -c monsters_all_schema.sql.gz | /usr/local/pgxl/bin/psql -h 172.31.32.61 -p 21001 -U postgres >import.log 2>&1 &
/usr/local/pgxl/bin/pg_restore -h 172.31.32.61 -p 21001 -U postgres -a -j 4 -f mfsso -d mfsso >import.log 2>&1 &
Creative Commons License

本文基于署名-非商业性使用-相同方式共享 4.0许可协议发布,欢迎转载、使用、重新发布,但请保留文章署名wanghengbin(包含链接:https://wanghengbin.com),不得用于商业目的,基于本文修改后的作品请以相同的许可发布。

发表评论