PostgreSQL 学习笔记

基本操作

# 进入命令行
psql -h localhost -p 5432 -U postgress dbname

# 创建数据库
CREATE DATABASE dbname;

# 查看数据库
\l

# 进入数据库
\c dbname

# 删除数据库
DROP DATABASE [ IF EXISTS ] dbname;

# 创建 table
CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( 一个或多个列 )
);

# 列出所有 table
\d

# 查看 table 信息
\d tablename
\d schema.tablename # 包含在 schema 内的 table

# 删除 table
DROP TABLE table_name, ...;

# 创建 schema
CREATE SCHEMA myschema;
# 创建 schema 同时创建 table
CREATE SCHEMA myschema.mytable (
...
);
# 创建 table 同时属于某 schema
CREATE TABLE myschema.table_name(
...
);

# 删除 schema
DROP SCHEMA myschema CASCADE; # 指定 CASCADE 代表要同时删除该 schema 内的所有对象

数据库Role/User和权限

• PostgreSQL manages database access permissions using the concept of roles. A role can be thought of as either a database user, or a group of database users, depending on how the role is set up.
• CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.
• grant 命令 https://www.postgresql.org/docs/9.5/sql-grant.html

# 列出 role
\du

create role wy_user nologin;
create role authenticator noinherit login password 'mysecretpassword'
grant wy_user to authenticator;

# 获取使用 schema 权限
grant usage on schema wyplatform to wy_user;
# 获取使用该 schema 中 sequences 的权限
grant usage, select on all sequences in schema wyplatform to wy_user;
# 获取该 schema 中所有 table 的所有权限（增删改查）
grant all on all tables in schema wyplatform to wy_user;