# PostgreSQL

[TOC]

# 一、PostgreSQL 特点

PostgreSQL 的特点如下 -

PostgreSQL 可在所有主要操作系统(即 Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru64)和 Windows 等)上运行。
PostgreSQL 支持文本，图像，声音和视频，并包括用于 C/C++，Java，Perl，Python，Ruby，Tcl 和开放数据库连接(ODBC)的编程接口。
PostgreSQL 支持 SQL 的许多功能，例如复杂 SQL 查询，SQL 子选择，外键，触发器，视图，事务，多进程并发控制(MVCC)，流式复制(9.0)，热备(9.0))。
在 PostgreSQL 中，表可以设置为从“父”表继承其特征。
可以安装多个扩展以向 PostgreSQL 添加附加功能。

PostgreSQL 工具

psql：它是一个命令行工具，也是管理 PostgreSQL 的主要工具。 pgAdmin是 PostgreSQL 的免费开源图形用户界面管理工具。
phpPgAdmin

它是用 PHP 编写的 PostgreSQL 的基于 Web 的管理工具。它基于 phpMyAdmin 工具管理 MySQL 功能来开发。它可以用作 PostgreSQL 的前端工具。
pgFouine

它是一个日志分析器，可以从 PostgreSQL 日志文件创建报告。专有工具有 - Lightning Admin for PostgreSQL, Borland Kylix, DBOne, DBTools Manager PgManager, Rekall, Data Architect, SyBase Power Designer, Microsoft Access, eRWin, DeZign for Databases, PGExplorer, Case Studio 2, pgEdit, RazorSQL, MicroOLAP Database Designer, Aqua Data Studio, Tuples, EMS Database Management Tools for PostgreSQL, Navicat, SQL Maestro Group products for PostgreSQL, Datanamic DataDiff for PostgreSQL, Datanamic SchemaDiff for PostgreSQL, DB MultiRun PostgreSQL Edition, SQLPro, SQL Image Viewer, SQL Data Sets 等等.
Navicat for PostSQL

感觉更好用，但是收费

# 二、下载与安装

PostgreSQL 下载

# 修改启动的服务名

默认是自动启动的，可以改为手动，默认启动命令是 net start postgresql-64-10 太长了，想改改, 如下

win+r 输入 regedit
依次打开 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CurrentControlSet\Services\postgresql-64-10 （这里是你的版本名称）把这个文件重命名一下（我这里改成了 postgresql）
重启之后，在命令行输入 net start postgresql 就可以启动服务了 net stop postgresql 关闭服务

# 修改配置文件

修改 postgresql 中的 listen_addresses = ‘*’ //表示允许所有机器访问
在 pg_hba.conf 文件最下面添加允许访问的 IP 等
如果远程连接不上，1. 可以关闭防火墙。或者 2.控制面板->windows...防火墙->高级设置，然后点击左侧"入站规则"，点击右侧"新建规则", 依次是：端口， TCP ，在特定本地端口输入 5432，然后下一步下一步点完就行了。当然其中有些东西是你自己看着选择的。

关于第 2 点配置 ip

	host	DATABASE	USER	ADDRESS	METHOD
		数据库	用户	ip	加密方式
全部可连:	host	all	all	all	md5
192.168 网段下可连:	host	all	all	192.168.1.0/24	md5
192.168.1.80 可连	host	all	all	192.168.1.80/32	md5

配置 ip 需要注意的地方

以上内容需要注意以下两点：

数据库名与用户名注意大小写，PostgreSQL 是区分大小的；　　　 2. CIDR-ADDRESS（用户的 IP 范围）的值的格式是：IP/数值数值取值范围为：0,8,16,24,32 　即表示掩码 bit 的个数；　　　　例如：　　　　　 32 -> 192.168.1.1/32 表示必须是来自这个 IP 地址的访问才合法；　　　　　 24 -> 192.168.1.0/24 表示只要来自 192.168.1.0 ~ 192.168.1.255 的都合法；　　　　　 16 -> 192.168.0.0/16 表示只要来自 192.168.0.0 ~ 192.168.255.255 的都合法；　　　　　 8 -> 192.0.0.0/16 表示只要来自 192.0.0.0 ~ 192.255.255.255 的都合法；　　　　　 0 -> 0.0.0.0/0 表示全部 IP 地址都合法，/左边的 IP 地址随便了只要是合法的 IP 地址即可；

# 三、系统对象

# 用户/角色

创建用户语法: CREATE USER name [ [ WITH ] option [ ... ] ]

option 可以是以下部分，注意，这不是全部，查看全部可以在 SQL Shell(psql)中使用 \h create user 命令

SUPERUSER| NOSUPERUSER --超级用户
| CREATEDB| NOCREATEDB	--创建库
| CREATEROLE | NOCREATEROLE	--创建角色(用户)
| INHERIT | NOINHERIT  --继承
| LOGIN | NOLOGIN 	--登陆权限
| CONNECTION LIMIT connlimit  --设置链接次数
| [ ENCRYPTED ] PASSWORD 'password'  -- 设置密码

1
2
3
4
5
6
7

创建用户

-- 创建普通用户并设置密码
CREATE USER "test" PASSWORD '123456';
-- 创建超级用户
CREATE USER "test" PASSWORD '123456' SUPERUSER;
--创建用户并授权
CREATE USER "test" PASSWORD '123456' CREATEDB CREATEROLE;

1
2
3
4
5
6

修改用户

--看命令去吧 \h alter user
ALTER USER role_specification [ WITH ] 选项 [ ... ]
eg. ALTER USER "test" WITH SUPERUSER

1
2
3

删除用户

\h drop user
DROP USER [ IF EXISTS ] 名称 [, ...]
eg. DROP USER IF EXISTS "test"

1
2
3

查看

postgres=# select * from pg_user;  --查看所有用户
postgres=# select * from pg_roles;  --查看所有角色

1
2

# 表空间

postgersql 中表空间实际上就是给表指定一个存储目录

创建

postgres-# \h create tablespace
命令：       CREATE TABLESPACE
描述：       建立新的表空间
语法：
CREATE TABLESPACE 表空间的名称
    [ OWNER { 新的属主 | CURRENT_USER | SESSION_USER } ]
    LOCATION '目录'
    [ WITH ( 表空间_选项 = 值 [, ... ] ) ]

eg. CREATE TABLESPACE "test_tbspace" OWNER "test" LOCATION 'e:\tbspaces'

1
2
3
4
5
6
7
8
9
10

修改

命令：       ALTER TABLESPACE
描述：       更改表空间的定义
语法：
ALTER TABLESPACE 名称 RENAME TO 新的名称
ALTER TABLESPACE 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }
ALTER TABLESPACE 名称 SET ( 表空间_选项 = 值 [, ... ] )
ALTER TABLESPACE 名称 RESET ( 表空间_选项 [, ... ] )

1
2
3
4
5
6
7

查看

-- 列出所有表空间
postgres=# \db
表空间列表
名称     |  拥有者  | 所在地
--------------+----------+---------
pg_default   | postgres |
pg_global    | postgres |
test_tbspace | test     | E:\tbspaces

-- 查看表空间下的表信息
select * from pg_tables where tablespace = 'test_tbspace';

1
2
3
4
5
6
7
8
9
10
11

系统自带表空间

表空间pg_default是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间。对应存储目录%{PostgreSQL}%data/base/
表空间pg_global用来存放系统字典表；对应存储目录%{PostgreSQL}%/data/global/

1
2

将表从一个表空间移到另一个表空间

ALTER TABLE name SET TABLESPACE new_tablespace

删除
```
DROP TABLESPACE [ IF EXISTS ] 名称
```
1

参考地址

# 数据库

创建

postgres-# \h create database
CREATE DATABASE 名称
    [ [ WITH ] [ OWNER [=] 用户名 ]
           [ TEMPLATE [=] 模版 ]
           [ ENCODING [=] 字符集编码 ]
           [ LC_COLLATE [=] 排序规则 ]
           [ LC_CTYPE [=] 字符分类 ]
           [ TABLESPACE [=] 表空间的名称 ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] 连接限制 ]
           [ IS_TEMPLATE [=] istemplate ] ]

eg. CREATE DATABASE testdb OWNER = "test" ENCODING = "UTF-8" TABLESPACE = "test_tbspace";

1
2
3
4
5
6
7
8
9
10
11
12
13

修改数据库

postgres-# \h alter database
命令：       ALTER DATABASE
描述：       更改一个数据库
语法：
ALTER DATABASE 名称 [ [ WITH ] 选项 [ ... ] ]

选项可以是

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT 连接限制
    IS_TEMPLATE istemplate

ALTER DATABASE 名称 RENAME TO 新的名称  -- 修改数据库的名

ALTER DATABASE 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }

ALTER DATABASE 名称 SET TABLESPACE 新的表空间	--修改数据库的表空间

ALTER DATABASE 名称 SET 配置参数 { TO | = } { 值 | DEFAULT }
ALTER DATABASE 名称 SET 配置参数 FROM CURRENT
ALTER DATABASE 名称 RESET 配置参数
ALTER DATABASE 名称 RESET ALL	-- 重置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

删除数据库

DROP DATABASE [ IF EXISTS ] 名称

# 模式

创建

postgres-> \h create schema
命令：       CREATE SCHEMA
描述：       建立新的架构模式
语法：
CREATE SCHEMA 模式名称 [ AUTHORIZATION role_specification ] [ 模式中对象 [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ 模式中对象 [ ... ] ]
CREATE SCHEMA IF NOT EXISTS 模式名称 [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification

这里role_specification可以是：

    用户名
  | CURRENT_USER
  | SESSION_USER

--创建一个模式，设置所属用户为test
CREATE SCHEMA "test" AUTHORIZATION "test";

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

查看

-- 使用 \dn也可以 只有名称和拥有着两列
postgres-> \dn+
                            架构模式列表
   名称   |  拥有者  |       存取权限       |          描述
----------+----------+----------------------+------------------------
 myschema | postgres |                      |
 public   | postgres | postgres=UC/postgres+| standard public schema
          |          | =UC/postgres         |

1
2
3
4
5
6
7
8

删除

postgres=> \h drop schema
命令：       DROP SCHEMA
描述：       移除一个模式
语法：
DROP SCHEMA [ IF EXISTS ] 名称 [, ...] [ CASCADE | RESTRICT ]

--删除一个模式
DROP SCHEMA IF EXISTS "test_schema";
--如果schema中存在对象,则需要使用cascade选项:
DROP SCHEMA IF EXISTS "test_schema" CASCADE;

1
2
3
4
5
6
7
8
9
10

# 四、权限管理

postgresql 中的权限可以大概分为以下几种

权限	说明
SELECT	该权限用来查询表或是表上的某些列，或是视图，序列。
INSERT	该权限允许对表或是视图进行插入数据操作，也可以使用 COPY FROM 进行数据的插入
UPDATE	该权限允许对表或是或是表上特定的列或是视图进行更新操作
DELETE	该权限允许对表或是视图进行删除数据的操作
TRUNCATE	允许对表进行清空操作
REFERENCES	允许给参照列和被参照列上创建外键约束
TRIGGER	允许在表上创建触发器
CREATE	对于数据库，允许在数据库上创建 Schema；对于 Schema，允许对 Schema 上创建数据库对象；对于表空间，允许把表或是索引指定到对应的表空间上
CONNECT	允许用户连接到指定的数据库上
TEMPORARY 或是 TEMP	允许在指定数据库的时候创建临时表
EXECUTE	允许执行某个函数
USAGE	对于程序语言来说，允许使用指定的程序语言创建函数；对于 Schema 来说，允许查找该 Schema 下的对象；对于序列来说，允许使用 currval 和 nextval 函数；对于外部封装器来说，允许使用外部封装器来创建外部服务器；对于外部服务器来说，允许创建外部表。
ALL PRIVILEGES	表示一次性给予可以授予的权限

# schema 权限管理

如果用户要访问某张表，那么首先需要有访问该表所在 schema 的权限，默认只有 schema 的拥有者可以直接操作该 schema，其他用户需要授权（public 这个 schema 除外）

在 SQL Shell(psql)中使用 \h grant 查看更多命令

--将schema的权限赋予给了指定用户
grant create on schema "test_schema" to "test"

--将schema中usage权限赋予给brent用户
grant usage on schema "test_schema" to "test";

--将schema中all权限赋予给test用户,all表示一次性给予可以授予的所有权限
grant all on schema "test_schema" to "test";

--查看所有的schema
select * from information_schema.schemata;

-- 查看某个用户的所有schema
select * from information_schema.schemata where schema_owner = '用户名';

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 表权限管理

默认的，如果没有特别的授权，普通用户只能访问所有者为自己的表，超级用户可以访问任意表。如果访问非自己的表，那么就需要对表进行授权，当我们以”test“用户想访问 ”other_schema"模式下所属用户为"qiao"的 student 表的时候就会报错。

将表的查询和插入权限赋予用户

-- 赋权之后就可以访问了
grant select,insert on other_schema.student to "test";
-- 收回权限 revoke
revoke select on other_schema.studen from "test";

1
2
3
4
5

# 角色管理

我们除了可以将表的权限赋予给用户,我们还可以将角色赋予给用户,那么用户就会拥有赋予角色的相关权限:

-- 给用户test授予role1这个角色
grant role1 to test;
-- 收回test用户的role1角色
revoke role1 from test;

1
2
3
4

查询表权限角色列表

使用\dp 或者\z 命令

postgres=# \dp person;

详细的权限说明如下:
            r -- SELECT ("读")
            w -- UPDATE ("写")
            a -- INSERT ("追加")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (对于表，对其他对象会变化)
            * -- 用于前述特权的授权选项
        /yyyy -- 授予该特权的角色

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

使用\du 查看用户与角色之间的关系

-- 查看用户用户test的权限与角色
postgres=# \du test

1
2

查看 test 用户具备的具体权限

select * from information_schema.role_table_grants where grantee='test';

参考：原文

###Search_path

PostgreSQL 中有一个环境变量 search_path，它是一个模式列表。search_path 路径中的第一个模式被称为 current schema， 这里推荐模式名与用户名相同。

查询 Search_path 环境变量

postgres=> show search_path;
   search_path
-----------------
 "$user", public

1
2
3
4

从查询结果看，PostgreSQL 会首先在与当前用户名相同的模式中查询，如果相关数据不在此模式中，则会继续其余的模式中查询（如上面的 public）,以此类推。

如果在 search_path 下的模式种都没有匹配到目标对象，则会出错。
如果创建了一个模式 myschema，查询模式下的表时出错了，查询 search_path 后没有看到创建的模式，那么

添加模式至 Search_path 环境变量

SET search_path = "$user", public, myschema;

设置数据库的默认模式（即，该数据库只使用某个模式下的数据）

ALTER database "test" SET search_path TO test;

# 五、表操作

/* 增加让主键自增的权限 */
grant all on sequence tablename_keyname_seq to webuser;

 /* 重命名一个表 */
alter table [表名A] rename to [表名B];

/* 删除一个表 */
drop table [表名];

/* 在已有的表里添加字段 */
alter table [表名] add column [字段名] [类型];

/* 删除表中的字段 */
alter table [表名] drop column [字段名];

/* 重命名一个字段 */
alter table [表名] rename column [字段名A] to [字段名B];

/* 给一个字段设置缺省值 */
alter table [表名] alter column [字段名] set default [新的默认值];

/* 去除缺省值 */
alter table [表名] alter column [字段名] drop default;

/* 插入数据 */
insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);

/* 修改数据 */
update [表名] set [目标字段名]=[目标值] where ...;

/* 删除数据 */
delete from [表名] where ...;

/* 删除表 */
delete from [表名];

/* 查询 */
SELECT * FROM dbname WHERE ...;

/* 创建表 */
create table (
    [字段名1] [类型1] primary key,
    [字段名2] [类型2],
    ......,
    [字段名n] [字段名n] )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

参考：原文

##常用语句

------------------------- 注意：------------------------
-- 1、postgresql中大小写不敏感 重命名时请使用双引号
select 'qiao' as "firstName";
-- 2、没有 dual 下面创建一个dual视图
create or replace view dual as select null;

--------------------------数据库------------------------------
-- 查看当前数据库的版本
select version();
-- 查看所有数据库
select * from pg_database
--查看用户的数据库
select * from pg_database where datname = (select USER);


----------------------------------连接------------------------
--查看最大连接数
show max_connections; -- 可以在postgresql.conf中修改
-- 查看当前连接用户
  SELECT
      A.pid,
      A.usename 用户名,
      A.application_name 连接工具,
      A.client_addr 连接地址
  FROM
      pg_stat_activity A
  WHERE
      NOT pid = pg_backend_pid ()
  AND usesysid IS NOT NULL;

-- 关闭连接
SELECT pg_terminate_backend(pid)

------------------------------用户-------------------------
-- 查看当前链接用户
select user;
--查看所有用户
select * from pg_user;
-- 查看所有角色
select * from pg_roles


-- 根据用户名查看用户的表空间
SELECT A .spcname,B.usename FROM pg_tablespace A LEFT JOIN pg_user B ON A .spcowner = B.usesysid WHERE usename = (SELECT USER) -- 这里填写用户名

-- 查看用户具备的具体权限
select * from information_schema.role_table_grants where grantee='用户名';

-- 查看用户的 表、索引、序列、视图(“关系”)
select * from pg_class where relowner = (select usesysid from pg_user where usename = (SELECT USER))

-- 查看用户的视图
select * from pg_views where viewowner = (select USER);


---------------------------角色---------------------------

select * from pg_roles; 	-- 查看所有角色



------------------------------------表空间-------------------------
-- 查看所有表空间
select * from pg_tablespace ;

-- 查看用户下的表空间
select * from pg_tablespace where spcowner = (select usesysid from pg_user WHERE usename = (select user))
-- 查看表空间下的表
select * from pg_tables where tablespace = '表空间名';


--------------------------------模式------------------------
--------查看所有的schema----------------
select * from information_schema.schemata;
--查看模式(schame)下的表
SELECT * FROM information_schema.tables where table_schema = '模式名'
-- 查看某个用户的所有schema
select * from information_schema.schemata where schema_owner = '用户名';


------------------------------权限------------------------------
-- 查看用户具备的具体权限
select * from information_schema.role_table_grants where grantee='用户名';

--查看usage权限表
select * from information_schema.usage_privileges where grantee='用户名';

--查看存储过程函数相关权限表
select * from information_schema.routine_privileges where grantee='用户名';


----------------查看表结构-------------
SELECT a.attnum,
a.attname AS field,
t.typname AS type,
a.attlen AS length,
a.atttypmod AS lengthvar,
a.attnotnull AS notnull,
b.description AS comment
FROM pg_class c,
pg_attribute a
LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid,
pg_type t
WHERE c.relname = '表名'
and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
ORDER BY a.attnum;
-------------查看模式下的所有函数-------------------------------
SELECT
	routines.ROUTINE_NAME,
	PARAMETERS.data_type,
	PARAMETERS.ordinal_position
FROM
	information_schema.routines
JOIN information_schema.PARAMETERS ON routines.SPECIFIC_NAME = PARAMETERS.SPECIFIC_NAME
WHERE
	routines.specific_schema = '模式名'
ORDER BY
	routines.ROUTINE_NAME,
	PARAMETERS.ordinal_position;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

参考：Postgresql 之系统表概述

# 控制台命令

\h   --帮助命令
\l  -- 列出所有数据库
\c [database_name] --连接其他数据库
\c - username  --切换用户
\c dbname username  --使用username用户链接dbname数据库
\du 	--查看所有用户与角色的关系   角色名称 |  属性 | 成员属于
\dn (dn+) 		--查看数据库的schema
--\d 数据库 		--查看模式中表的名字 （select * from pg_tables where schemaname = 'public'）
\d  [table_name] 	--查看表结构
\dt  --查看当前数据库下的所有表
\dv pg_* --查看以pg_开头的视图
\dp(\z)	--查询表上已经分配的权限列表
\e	--打开文本编辑器
\conninfo	--列出当前数据库和连接的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

扩展控制台命令

\copyright            显示PostgreSQL的使用和发行许可条款
  \g [文件] or;             执行查询 (并把结果写入文件或 |管道)
  \gset [PREFIX]             执行查询并把结果存到psql变量中
  \h [名称]                  SQL命令语法上的说明，用*显示全部命令的语法说明
  \q             退出 psql
  \watch [SEC]          每隔SEC秒执行一次查询

查询缓存区
  \e [FILE] [LINE]                使用外部编辑器编辑查询缓存区(或文件)
  \ef [FUNCNAME [LINE]]           使用外部编辑器编辑函数定义
  \p                            显示查询缓存区的内容
  \r                            重置(清除)查询缓存区
  \s [文件]                                显示历史记录或将历史记录保存在文件中
  \w 文件                                  将查询缓存区的内容写入文件

输入/输出
  \copy ...             执行 SQL COPY，将数据流发送到客户端主机
  \echo [字符串]       将字符串写到标准输出
  \i 文件          从文件中执行命令
  \ir FILE               与 \i类似, 但是相对于当前脚本的位置
  \o [文件]        将全部查询结果写入文件或 |管道
  \qecho [字符串]      将字符串写到查询输出串流(参考 \o)
资讯性
  (选项: S = 显示系统对象, + = 其余的详细信息)
  \d[S+]          列出表,视图和序列
  \d[S+]  名称      描述表，视图，序列，或索引
  \da[S]  [模式]    列出聚合函数
  \db[+]  [模式]     列出表空间
  \dc[S+] [PATTERN]      列表转换
  \dC[+]  [PATTERN]      列出类型强制转换
  \dd[S]  [PATTERN]      显示没有在别处显示的对象描述
  \ddp     [模式]    列出缺省权限
  \dD[S+] [PATTERN]      列出共同值域
  \det[+] [PATTERN]      列出引用表
  \des[+] [模式]    列出外部服务器
  \deu[+] [模式]     列出用户映射
\dew[+] [模式]       列出外部数据封装器
   \df[antw][S+] [模式]    列出[只包括 聚合/常规/触发器/窗口]函数
  \dF[+]  [模式]   列出文本搜索配置
  \dFd[+] [模式]     列出文本搜寻字典
\dFp[+] [模式]     列出文本搜索解析器
  \dFt[+] [模式]   列出文本搜索模版
  \dg[+]  [PATTERN]      列出角色
\di[S+] [模式]  列出索引
  \dl                   列出大对象， 功能与\lo_list相同
  \dL[S+] [PATTERN]      列出所有过程语言
  \dm[S+] [PATTERN]      列出所有物化视图
  \dn[S+] [PATTERN]     列出所有模式
  \do[S]  [模式]   列出运算符
  \dO[S+] [PATTERN]      列出所有校对规则
  \dp     [模式]     列出表，视图和序列的访问权限
  \drds [模式1 [模式2]] 列出每个数据库的角色设置
  \ds[S+] [模式]    列出序列
  \dt[S+] [模式]     列出表
  \dT[S+] [模式]  列出数据类型
  \du[+]  [PATTERN]      列出角色
  \dv[S+] [模式]   列出视图
  \dE[S+] [PATTERN]      列出引用表
  \dx[+]  [PATTERN]      列出扩展
  \dy     [PATTERN]      列出所有事件触发器
  \l[+]   [PATTERN]      列出所有数据库
\sf[+] FUNCNAME        显示函数定义
  \z      [模式]    和\dp的功能相同

格式化
  \a                  在非对齐模式和对齐模式之间切换
  \C [字符串]        设置表的标题，或如果没有的标题就取消
  \f [字符串]         显示或设定非对齐模式查询输出的字段分隔符
  \H                    切换HTML输出模式 (目前是 关闭)
  \pset NAME [VALUE]     设定表输出选项
                         (NAME := {format|border|expanded|fieldsep|fieldsep_zero|footer|null|
                         numericlocale|recordsep|recordsep_zero|tuples_only|title|tableattr|pager})
  \t [开|关]       只显示记录 (目前是 关闭)
  \T [字符串]         设置HTML <表格>标签属性, 或者如果没有的话取消设置
  \x [on|off|auto]       切换扩展输出模式(目前是 关闭)

连接
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "susemanager")
  \encoding [编码名称] 显示或设定客户端编码
  \password [USERNAME]  安全地为用户改变口令
  \conninfo              显示当前连接的相关信息

操作系统
  \cd [目录]     改变目前的工作目录
  \setenv NAME [VALUE]   设置或清空环境变量
\timing [开|关]       切换命令计时开关 (目前是 关闭)
  \! [命令]      在 shell中执行命令或启动一个交互式shell

变量
  \prompt [文本] 名称 提示用户设定内部变量
  \set [名称 [值数]] 设定内部变量，若无参数则列出全部变量
  \unset 名称    清空(删除)内部变量

大对象
  \lo_export LOBOID 文件
  \lo_import 文件 [注释]
  \lo_list
  \lo_unlink LOBOID   大对象运算

# 六、特性

# 对 JSON 的支持

postgresql 支持两种 json 数据类型：json 和 jsonb，而两者唯一的区别在于效率;

json 是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而 jsonb 是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json 类型存储快，使用慢，jsonb 类型存储稍慢，使用较快。

注意：键值对的键必须使用双引号

测试表：

create table api(jdoc jsonb);

insert into api values('{
  "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
  "name": "Angela Barton",
  "is_active": true,
  "company": "Magnafone",
  "address": "178 Howard Place, Gulf, Washington, 702",
  "registered": "2009-11-07T08:53:22 +08:00",
  "latitude": 19.793713,
  "longitude": 86.513373,
  "hobby": {
      "h1": "Singing",
      "h2": "dancing"
  },
  "tags": [
    "enim",
    "aliquip",
    "qui"
   ]
}');

-- 根据key 获取json中的值
select score->'语文' as score from tb_test;

-- #>> 在指定的路径获取JSON对象为 text
-- 数组的获取方法: 获取key为tags，下标为1的值
select jdoc#>>'{tags, 1}' as tags from api;
-- map的获取方法: 获取key为hobby的对象下，key为h2的值
select jdoc#>>'{hobby,h2}' from api;

-- 获取表中jdoc字段去掉hobby属性后的值（并没有物理删除）
select jdoc-'hobby' from api;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

参考文章:postgresql----JSON 类型和函数

# 序列

自增：

postgresql 序列号（SERIAL）类型包括 smrial2(short), smrial4(int), smrial8(long)，但是序列号类型其实不是真正的类型，当声明一个字段为序列号类型时其实是创建了一个序列，INSERT 时如果没有给该字段赋值会默认获取对应序列的下一个值。

创建表的时候可以设置 id 自增，可以这么写: id int4 (int2 表示两个字节， int4 表示四个字节，int8 表达 8 个字节)

使用 SERIAL 时，其实就是使用了 postgresql 内置的序列 tbl_serial_a_seq

序列的类型有

参考: 序列

# 正则表达式查询

使用关键字 "~", 如果匹配规则不需要区分大小写，可以使用组合键 "~*";

1. select * from user where email ~ '^[A-H]' --匹配email地址以A-H开头的记录
2. select * from user where email ~* '^[a-h]'  --匹配email地址以A-H和a-h开头的记录

1
2

# 八、数据库迁移

windows 工具: OracleToPostgres

linu 文章(Ora2Pg): Oracle 迁移至 PostgreSQL 工具之 Ora2Pg

# 九、postGIS

注意：拥有者需要有创建数据库和 schame 的权限

加载空间数据 PostGIS 的安装与初步使用

# 十、pg 简单用法

-- 查看当前登陆用户
select user;
-------------------------------------创建数据-----------------------------------
-- 1、创建用户 并设置登陆密码
CREATE USER "test" PASSWORD '123456';

-- 2、创建角色
CREATE ROLE "test_role" CREATEDB CREATEROLE;

-- 3、给用户设置角色
GRANT "test_role" to "test";

-- 4、创建表空间
CREATE TABLESPACE "test_tbspace" OWNER "test" LOCATION 'e:\tbspaces';

-- 5、创建数据库
CREATE DATABASE testdb OWNER = "test" ENCODING = "UTF-8" TABLESPACE = "test_tbspace";



-- 创建schema  schema一般与用户名一样(在使用表视图等时可以不用写schema，查询也会直接查与用户名相同的schema下的数据)
-- 6、创建得shema想要在指定的数据库中，就需要登陆的时候连接那个数据库
CREATE SCHEMA IF NOT EXISTS "test" AUTHORIZATION "test";

--  查询search_path SET search_path = "$user", public, myschema;

-- 7、创建表  默认在searche_path的第一个shema下(这里一般shema与用户名相同)
CREATE TABLE IF NOT EXISTS "test"."tb_test" (
  "id" serial4, -- 这里会使用默认序列, 当然可以使用自定义的递增序列
  "name" varchar COLLATE "default",
  "age" int4,
  "gender" int4,
  "score" jsonb,
  CONSTRAINT "pk_test.tb_est" PRIMARY KEY ("id")
)
WITH (OIDS=FALSE)
;

-- 设置表的拥有着，默认是当前登陆用户
ALTER TABLE "test"."tb_test" OWNER TO "test";

COMMENT ON TABLE "test"."tb_test" IS '测试表';
COMMENT ON COLUMN "test"."tb_test"."id" IS '用户id';
COMMENT ON COLUMN "test"."tb_test"."name" IS '姓名';
COMMENT ON COLUMN "test"."tb_test"."age" IS '年龄';
COMMENT ON COLUMN "test"."tb_test"."gender" IS '性别';

-- 8、添加数据
INSERT INTO "test"."tb_test"("name", age, gender, score) VALUES('zs', 11, 1, null),('ls', 12, 0, null),('qiao', 25, 1, '{"数学": "100", "语文": "100"}');

------------------------------------删除数据-----------------------------------------

-- 级联删除schema（删除schema下的表，视图，过程，触发器等）,需要有权限
DROP SCHEMA IF EXISTS "test" CASCADE;

-- 删除数据库 需要有权限
DROP DATABASE IF EXISTS "testdb";

-- 删除表空间 表空间没有数据可删
DROP TABLESPACE IF EXISTS "test_tbspace";

-- 删除角色
DROP ROLE IF EXISTS "test_role";

-- 删除用户
DROP USER IF EXISTS "test";

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66