Postgres介绍

本章我们将介绍postgres相关的一些知识,包括:

  • 部署pgAmin管理工具
  • database和schema
  • 基本的DML语句
  • Role和user等

postgres数据库相关命令

退出:

\q

获取当前使用的数据库:

 SELECT current_database();

切换数据库:

\c database_name;

例如切换到 dvdrental数据库 :

\c dvdrental;
You are now connected to database "dvdrental" as user "postgres".

列出所有的数据库:

\l

创建新的数据库:

CREATE DATABASE [IF NOT EXISTS] db_name;

删除数据库:

DROP DATABASE [IF EXISTS] db_name;

list操作

列出所有schema:

\dn

列出所有 views :

\dv

列出当前数据库的所有数据表:

\dt

列出数据表,并获取详细信息

\dt+

获取某张表的详细信息:

\d+ table_name

以更美观的形式输出查询结果:

\x

获取所有用户:

\du

Role相关操作

创建新 role :

CREATE ROLE role_name;

创建新role,并指定 usernamepassword:

CREATE ROLE username NOINHERIT LOGIN PASSWORD password;

切换当前session的role:

SET ROLE new_role;

数据表(table)相关命令

创建表:

CREATE [TEMP] TABLE [IF NOT EXISTS] table_name(
   pk SERIAL PRIMARY KEY,
   c1 type(size) NOT NULL,
   c2 type(size) NULL,
   ...
)

例如:

CREATE TABLE accounts (
	user_id serial PRIMARY KEY,
	username VARCHAR ( 50 )  NOT NULL,
	password VARCHAR ( 50 ) NOT NULL
	)

表中新增一列:

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;

表中删除一列:

ALTER TABLE table_name DROP COLUMN column_name;

对某列重命名:

ALTER TABLE table_name RENAME column_name TO new_column_name;

对某列设置(或删除)默认值:

ALTER TABLE table_name ALTER COLUMN [SET DEFAULT value | DROP DEFAULT]

为表添加主键:

ALTER TABLE table_name ADD PRIMARY KEY (column,...);

移除主键:

ALTER TABLE table_name 
DROP CONSTRAINT primary_key_constraint_name;

为表重命名:

ALTER TABLE table_name RENAME TO new_table_name;Code language: SQL (Structured Query Language) (sql)

删除表:

 DROP TABLE [IF EXISTS] table_name CASCADE;

为表添加索引:

CREATE [UNIQUE] INDEX index_name
ON table (column,...)

视图(view)相关操作

创建view:

CREATE OR REPLACE view_name AS
query;

创建recursive view:

CREATE RECURSIVE VIEW view_name(column_list) AS
SELECT column_list;

创建物化视图(MATERIALIZED VIEW):

CREATE MATERIALIZED VIEW view_name
AS
query
WITH [NO] DATA;

如果需要创建时加载数据,使用 WITH DATA 选项;否则使用 WITH NO DATA,此时视图标记为不可读,即在加载数据之前不能查询物化视图。


刷新物化视图:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

当刷新物化视图时,PostgreSQL 锁定整个表,因此你不能查询表。可以通过使用 CONCURRENTLY 关键字避免锁表:

REFRESH MATERIALIZED VIEW CONCURRENTLY view_name;

使用 CONCURRENTLY 关键字选项,PostgreSQL 创建物化视图的临时更新版本,通过对比两个版本执行对差异部分执行 插入和更新操作,更新物化视图同时可以执行查询操作。

使用CONCURRENTLY 选项的条件需要物化视图有唯一索引。注意CONCURRENTLY 选项仅从 PostgreSQL 9.4 版本才引入。

删除view:

DROP VIEW [ IF EXISTS ] view_name;

删除物化视图:

DROP MATERIALIZED VIEW view_name;

重命名:

ALTER VIEW view_name RENAME TO new_name;