51工具盒子# postgresql的常见命令 {#postgresql的常见命令}
本文介绍postgresql的常用命令。因为postgresql和mysql无论是在命令操作上，还是在sql语法上，都有些区别，所以本文总结下postgresql的常用命令。
注意，必须要切换到数据库用户下才能访问数据库: 切换方式详见
若您的数据库表是在自定义的schema下，那么进行数据库操作之前，需要先切换到指定schema下。schema类似于同一个数据库的不同命名空间，切换到不同的命名空间，可以查看到不同的表。演示过程如下:
novel=# set search_path to public;
SET
novel=# \d+
没有找到任何关系.
novel=# set search_path to novel_user;
SET
novel=# \d+
novel_user | chapter | 数据表 | postgres | permanent | 8192 bytes | 章节
novel_user | novel | 数据表 | postgres | permanent | 8192 bytes | 小说
novel=#
# 1. 用户管理 {#_1-用户管理}
# 1.1 创建用户 {#_1-1-创建用户}
示例如下:
CREATE USER novel_user WITH PASSWORD 'novel123';
建议创建用户的时候，顺便创建一个同名的schema，命令为create schema novel_user;。
novel=# create schema novel_user;
CREATE SCHEMA
novel=#
# 1.2 列出所有用户 {#_1-2-列出所有用户}
执行命令\dg
postgres=# \dg
角色列表
角色名称 | 属性 | 成员属于
------------+--------------------------------------------+----------
novel_user | | {}
postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
postgres=#
# 2. 权限管理 {#_2-权限管理}
进一步的权限管理的常用操作请前往用户、角色、权限 (opens new window)
# 2.1 修改数据库的所属用户 {#_2-1-修改数据库的所属用户}
超级管理员创建好数据库后，需要修改owner为对应的用户，否则该用户无权登录。
-
修改owner
命令:alter database novel owner to novel_user;postgres=# alter database novel owner to novel_user; ALTER DATABASE postgres=#
-
使用owner用户登录数据库
PGPASSWORD=密码 psql -U 用户名 数据库名
参考文档: https://www.cnblogs.com/fengwenqian/p/8204751.html
# 2.2 分配某个表的权限 {#_2-2-分配某个表的权限}
示例如下:
GRANT ALL ON novel TO novel_user;
# 2.3 撤销某个表的权限 {#_2-3-撤销某个表的权限}
示例如下:
REVOKE ALL ON novel FROM novel_user;
# 2.4 查询某用户的表权限 {#_2-4-查询某用户的表权限}
select * from information_schema.table_privileges where grantee='novel_user';
novel=# select * from information_schema.table_privileges where grantee='novel_user';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+------------+---------------+--------------+------------+----------------+--------------+----------------
postgres | novel_user | novel | public | novel | INSERT | NO | NO
postgres | novel_user | novel | public | novel | SELECT | NO | YES
postgres | novel_user | novel | public | novel | UPDATE | NO | NO
postgres | novel_user | novel | public | novel | DELETE | NO | NO
postgres | novel_user | novel | public | novel | TRUNCATE | NO | NO
postgres | novel_user | novel | public | novel | REFERENCES | NO | NO
postgres | novel_user | novel | public | novel | TRIGGER | NO | NO
postgres | novel_user | novel | public | chapter | INSERT | NO | NO
postgres | novel_user | novel | public | chapter | SELECT | NO | YES
postgres | novel_user | novel | public | chapter | UPDATE | NO | NO
postgres | novel_user | novel | public | chapter | DELETE | NO | NO
postgres | novel_user | novel | public | chapter | TRUNCATE | NO | NO
postgres | novel_user | novel | public | chapter | REFERENCES | NO | NO
postgres | novel_user | novel | public | chapter | TRIGGER | NO | NO
(14 行记录)
novel=#
# 3. 查询数据库列表 {#_3-查询数据库列表}
使用命令\l
novel=# \l
novel | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
novel=#
# 4. 切换数据库 {#_4-切换数据库}
语法: \c 数据库名
postgres=# \c novel
您现在已经连接到数据库 "novel",用户 "postgres".
novel=#
# 5. 创建表 {#_5-创建表}
使用标准sql语句即可。
和mysql的区别:
-
注释写法不同 postgresql中添加注释的语法如下:
COMMENT ON TABLE 表名 IS '表的注释内容'; COMMENT ON COLUMN 表名.字段名 IS '字段的注释内容';
将"表名、字段名、注释内容"替换为具体内容即可。
- 支持的字段类型不同 如mysql中的longtext类型、datetime类型等，在progresql中对应的类型名称不同。
示例:
create table novel(
id bigint primary key,
title varchar(100),
author varchar(50),
summary text,
chapter_count int,
word_count varchar(10),
cover_src_url varchar(300),
novel_src_url varchar(300),
created_at timestamp null,
updated_at timestamp null,
deleted_at timestamp null
);
COMMENT ON TABLE novel IS '小说';
COMMENT ON COLUMN novel.title IS '标题';
COMMENT ON COLUMN novel.author IS '作者';
COMMENT ON COLUMN novel.summary IS '简介';
COMMENT ON COLUMN novel.chapter_count IS '章节数量';
COMMENT ON COLUMN novel.word_count IS '字数';
COMMENT ON COLUMN novel.cover_src_url IS '封面源地址';
COMMENT ON COLUMN novel.novel_src_url IS '小说源地址';
COMMENT ON COLUMN novel.created_at IS '创建时间';
COMMENT ON COLUMN novel.updated_at IS '更新时间';
COMMENT ON COLUMN novel.deleted_at IS '删除时间';
# 6. 修改数据库或表 {#_6-修改数据库或表}
- 修改字段的名字
alter table 表名 rename 字段旧名 to 字段新名; - 删除某字段
ALTER TABLE 表名 DROP COLUMN 字段名; - 修改某字段的类型
ALTER TABLE 表名 ALTER COLUMN 字段名 TYPE 数据类型; - 修改表名
ALTER TABLE 表名 RENAME TO 新表名; - 修改数据库名称
ALTER DATABASE 数据库名称 RENAME TO 数据库新名;
# 7. 列出当前数据库的表 {#_7-列出当前数据库的表}
使用命令\d或\d+(推荐 )。
\d+会额外输出表的注释。
novel=# \d
public | chapter | 数据表 | postgres
public | novel | 数据表 | postgres
novel=# \d+
public | chapter | 数据表 | postgres | permanent | 8192 bytes | 章节
public | novel | 数据表 | postgres | permanent | 8192 bytes | 小说
novel=#
# 8. 查询表结构 {#_8-查询表结构}
语法: \d 表名或\d+ 表名(推荐 )。
\d+ 表名会额外输出字段的注释。
novel=# \d novel
id | bigint | | not null |
title | character varying(100) | | |
author | character varying(50) | | |
summary | text | | |
chapter_count | integer | | |
word_count | character varying(10) | | |
cover_src_url | character varying(300) | | |
novel_src_url | character varying(300) | | |
created_at | timestamp without time zone | | |
updated_at | timestamp without time zone | | |
deleted_at | timestamp without time zone | | |
novel=# \d+ novel
id | bigint | | not null | | plain | |
title | character varying(100) | | | | extended | | 标题
author | character varying(50) | | | | extended | | 作者
summary | text | | | | extended | | 简介
chapter_count | integer | | | | plain | | 章节数量
word_count | character varying(10) | | | | extended | | 字数
cover_src_url | character varying(300) | | | | extended | | 封面源地址
novel_src_url | character varying(300) | | | | extended | | 小说源地址
created_at | timestamp without time zone | | | | plain | | 创建时间
updated_at | timestamp without time zone | | | | plain | | 更新时间
deleted_at | timestamp without time zone | | | | plain | | 删除时间
novel=#
# 9. 模式(schema) {#_9-模式-schema}
postgresql和mysql对于schema的含义完全不同。mysql中的schema和database是相同的含义，只是多了个名字而已。
schema的意义: 允许多个用户使用同一个数据库而互相隔离。
一个数据库可以有多个schema，不同schema中的表允许表重名。
建议为每个用户创建一个属于自己的schema，名字可以和用户名相同。
系统的默认模式为public: 若对数据库操作的时候没有指定schema，则会默认使用public模式。
在golang的orm框架gorm的连接字符串中配置schema的示例:
gorm.Open("postgres", "host=localhost user=novel_user password=novel123 dbname=novel search_path=public sslmode=disable")
# 9.1 列出所有的模式 {#_9-1-列出所有的模式}
使用命令select schema_name from information_schema.schemata;或select nspname from pg_catalog.pg_namespace;
novel=# select schema_name from information_schema.schemata;
pg_toast
pg_catalog
public
information_schema
novel_user
novel=# select nspname from pg_catalog.pg_namespace;
pg_toast
pg_catalog
public
information_schema
novel_user
novel=#
# 9.2 查询当前的模式搜索路径 {#_9-2-查询当前的模式搜索路径}
命令: show search_path
novel=# show search_path;
"$user", public
novel=#
# 9.3 设置当前的模式搜索路径 {#_9-3-设置当前的模式搜索路径}
命令: SET search_path TO 模式名称;
novel=# show search_path;
public
novel=# SET search_path TO novel_user;
SET
novel=# show search_path;
novel_user
novel=#
# 10. With字句 {#_10-with字句}
WITH子句是postgresql特有的特性，在mysql中没有。主要用于将复杂的大型查询分解为简单的sql，以便于阅读。
# 11. 导出数据 {#_11-导出数据}
导出某数据库的表结构和数据, 示例如下:
pg_dump novel > /tmp/novel_data.sql
仅导出数据, 示例如下:
pg_dump novel > /tmp/novel_data.sql
若只导出结构,则补充参数
--schema-only若只导出数据,则补充参数--data-only
导出postgresql数据库的数据到mysql:
- 导出命令pg_dump增加一项参数
--column-inserts, 使得将默认的COPY语句替换为INSERT INTO语句. - 删除生成的sql文件开头部分的无用语句.
- 删除生成的sql文件中的
schema部分, 默认为public. - 将修改后的sql文件导入到mysql
默认情况下, postgresql导出的sql语句是COPY语句,而不是INSERT INTO语句.所以语法上的差异导致不方便迁移.
# 12. 参考资料 {#_12-参考资料}
更详细的使用方法详见postgresql (opens new window)。
