PostgreSQL - 快速指南

---

PostgreSQL - 概述

PostgreSQL 是一个功能强大的开源对象关系数据库系统。它经过超过 15 年的积极开发阶段和经过验证的架构，为其在可靠性、数据完整性和正确性方面赢得了良好的声誉。

本教程将帮助您快速入门 PostgreSQL，并让您熟悉 PostgreSQL 编程。

什么是 PostgreSQL？

PostgreSQL（发音为post-gress-QL）是一个开源关系数据库管理系统 (DBMS)，由全球志愿者团队开发。PostgreSQL 不受任何公司或其他私人实体控制，源代码免费提供。

PostgreSQL 简史

PostgreSQL 最初称为 Postgres，是由 UCB 计算机科学教授 Michael Stonebraker 创建的。Stonebraker 于 1986 年创建了 Postgres，作为其前身 Ingres（现归 Computer Associates 所有）的后续项目。

• 1977-1985 – 开发了一个名为 INGRES 的项目。

  • 关系数据库的概念验证

  • 1980年成立安格尔公司

  • 1994 年被 Computer Associates 收购

• 1986-1994 – 研究生

  • INGRES 中概念的发展，重点是面向对象和查询语言 - Quel

  • INGRES 的代码库未用作 POSTGRES 的基础

  • 商业化为 Illustra（被 Informix 收购，被 IBM 收购）

• 1994-1995 - Postgres95

  • 1994 年添加了对 SQL 的支持

  • 1995 年作为 Postgres95 发布

  • 1996 年重新发布为 PostgreSQL 6.0

  • PostgreSQL全球开发团队成立

PostgreSQL 的主要特性

PostgreSQL 可在所有主要操作系统上运行，包括 Linux、UNIX（AIX、BSD、HP-UX、SGI IRIX、Mac OS X、Solaris、Tru64）和 Windows。它支持文本、图像、声音和视频，并包括 C/C++、Java、Perl、Python、Ruby、Tcl 和开放数据库连接 (ODBC) 的编程接口。

PostgreSQL 支持大部分 SQL 标准并提供许多现代功能，包括以下功能 -

• 复杂的 SQL 查询
• SQL 子选择
• 外键
• 扳机
• 意见
• 交易
• 多版本并发控制 (MVCC)
• 流式复制（从 9.0 开始）
• 热备（9.0 起）

您可以查看PostgreSQL的官方文档来了解上述功能。用户可以通过多种方式扩展 PostgreSQL。例如通过添加新的 -

• 数据类型
• 功能
• 运营商
• 聚合函数
• 索引方法

过程语言支持

PostgreSQL支持四种标准的过程语言，允许用户用任何一种语言编写自己的代码，并且可以由PostgreSQL数据库服务器执行。这些过程语言是 - PL/pgSQL、PL/Tcl、PL/Perl 和 PL/Python。此外，还支持其他非标准过程语言，如 PL/PHP、PL/V8、PL/Ruby、PL/Java 等。

PostgreSQL - 环境设置

要开始了解 PostgreSQL 基础知识，首先让我们安装 PostgreSQL。本章介绍如何在 Linux、Windows 和 Mac OS 平台上安装 PostgreSQL。

在 Linux/Unix 上安装 PostgreSQL

按照给定的步骤在 Linux 计算机上安装 PostgreSQL。在继续安装之前，请确保您以root身份登录。

• 从EnterpriseDB中选择您想要的 PostgreSQL 版本号以及尽可能准确的平台

• 我为我的 64 位 CentOS-6 机器下载了postgresql-9.2.4-1-linux-x64.run 。现在，让我们执行如下 -

[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run
[root@host]# ./postgresql-9.2.4-1-linux-x64.run
------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

• 启动安装程序后，它会询问您一些基本问题，例如安装位置、将使用数据库的用户的密码、端口号等。因此，除了密码之外，请将所有这些都保留为默认值，您可以提供密码根据您的选择。它将在您的 Linux 计算机上安装 PostgreSQL 并显示以下消息 -

Please wait while Setup installs PostgreSQL on your computer.

 Installing
 0% ______________ 50% ______________ 100%
 #########################################

-----------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

• 请按照以下安装后步骤创建数据库 -

[root@host]# su - postgres
Password:
bash-4.1$ createdb testdb
bash-4.1$ psql testdb
psql (8.4.13, server 9.2.4)

test=#

• 如果 postgres 服务器没有运行，您可以使用以下命令启动/重新启动它 -

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

• 如果您的安装正确，您将看到 PotsgreSQL 提示符test=#，如上所示。

在 Windows 上安装 PostgreSQL

按照给定的步骤在 Windows 计算机上安装 PostgreSQL。确保您在安装时已关闭第三方防病毒软件。

• 从EnterpriseDB中选择您想要的 PostgreSQL 版本号以及尽可能准确的平台

• 我为以 32 位模式运行的 Windows PC 下载了 postgresql-9.2.4-1-windows.exe，因此让我们以管理员身份运行postgresql-9.2.4-1-windows.exe来安装 PostgreSQL。选择您想要安装它的位置。默认情况下，它安装在 Program Files 文件夹中。

• 安装过程的下一步是选择存储数据的目录。默认情况下，它存储在“data”目录下。

• 接下来，安装程序要求输入密码，因此您可以使用您喜欢的密码。

• 下一步; 将端口保留为默认值。

• 在下一步中，当询问“区域设置”时，我选择了“英语，美国”。

• 在系统上安装 PostgreSQL 需要一段时间。安装过程完成后，您将看到以下屏幕。取消选中该复选框并单击“完成”按钮。

安装过程完成后，您可以从 PostgreSQL 9.2 下的程序菜单访问 pgAdmin III、StackBuilder 和 PostgreSQL shell。

在 Mac 上安装 PostgreSQL

按照给定的步骤在 Mac 计算机上安装 PostgreSQL。在继续安装之前，请确保您以管理员身份登录。

• 选择EnterpriseDB上提供的适用于 Mac OS 的 PostgreSQL 的最新版本号

• 我为运行 OS X 版本 10.8.3 的 Mac 操作系统下载了postgresql-9.2.4-1-osx.dmg 。现在，让我们在 finder 中打开 dmg 映像，然后双击它，这将在以下窗口中为您提供 PostgreSQL 安装程序 -

• 接下来，单击postgres-9.2.4-1-osx图标，这将给出警告消息。接受警告并继续进行进一步安装。它将要求输入管理员密码，如以下窗口所示 -

输入密码，继续安装，完成此步骤后，重新启动 Mac 计算机。如果您没有看到以下窗口，请重新开始安装。

• 启动安装程序后，它会询问您一些基本问题，例如安装位置、将使用数据库的用户的密码、端口号等。因此，除了您可以提供的密码外，请将所有这些都保留为默认值根据您的选择。它将在您的 Mac 计算机上的应用程序文件夹中安装 PostgreSQL，您可以检查该文件夹 -

• 现在，您可以启动任何程序。让我们从 SQL Shell 开始。当您启动 SQL Shell 时，只需使用它显示的所有默认值，但输入您在安装时选择的密码除外。如果一切顺利，那么您将进入 postgres 数据库，并且将显示postgress#提示符，如下所示 -

恭喜！！！现在您的环境已准备好，可以开始 PostgreSQL 数据库编程了。

PostgreSQL - 语法

本章提供了 PostgreSQL SQL 命令的列表，后面是每个命令的精确语法规则。这组命令取自 psql 命令行工具。现在您已经安装了 Postgres，打开 psql：

程序文件 → PostgreSQL 9.2 → SQL Shell(psql)。

使用 psql，您可以使用 \help 命令生成完整的命令列表。对于特定命令的语法，请使用以下命令 -

 postgres-# \help <command_name>

SQL语句

SQL 语句由标记组成，其中每个标记可以表示关键字、标识符、带引号的标识符、常量或特殊字符符号。下表使用简单的 SELECT 语句来说明基本但完整的 SQL 语句及其组件。

PostgreSQL SQL 命令

中止

中止当前事务。

ABORT [ WORK | TRANSACTION ]

改变聚合

更改聚合函数的定义。

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner

改变转换

更改转换的定义。

ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner

修改数据库

更改数据库特定参数。

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner

更改域

更改域特定参数的定义。

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name { SET | DROP } NOT NULL
ALTER DOMAIN name ADD domain_constraint
ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name OWNER TO new_owner

改变功能

更改函数的定义。

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

阿尔特集团

更改用户组。

ALTER GROUP groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_name

改变索引

更改索引的定义。

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

改变语言

更改过程语言的定义。

ALTER LANGUAGE name RENAME TO new_name

改变运算符

更改运算符的定义。

ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner

改变操作符类别

更改运算符类的定义。

ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner

改变架构

更改架构的定义。

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner

改变序列

更改序列生成器的定义。

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

修改表

更改表的定义。

ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name

其中操作是以下行之一 -

ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name

修改表空间

更改表空间的定义。

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner

改变触发器

更改触发器的定义。

ALTER TRIGGER name ON table RENAME TO new_name

改变类型

更改类型的定义。

ALTER TYPE name OWNER TO new_owner

更改用户

更改数据库用户帐户。

ALTER USER name [ [ WITH ] option [ ... ] ]
ALTER USER name RENAME TO new_name
ALTER USER name SET parameter { TO | = } { value | DEFAULT }
ALTER USER name RESET parameter

其中选项可以是 -

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'

分析

收集有关数据库的统计信息。

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

开始

启动一个交易块。

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

其中transaction_mode是以下之一 -

ISOLATION LEVEL { 
   SERIALIZABLE | REPEATABLE READ | READ COMMITTED
   | READ UNCOMMITTED
}
READ WRITE | READ ONLY

检查站

强制事务日志检查点。

CHECKPOINT

关闭

关闭光标。

CLOSE name

簇

根据索引对表进行聚类。

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

评论

定义或更改对象的注释。

COMMENT ON {
   TABLE object_name |
   COLUMN table_name.column_name |
   AGGREGATE agg_name (agg_type) |
   CAST (source_type AS target_type) |
   CONSTRAINT constraint_name ON table_name |
   CONVERSION object_name |
   DATABASE object_name |
   DOMAIN object_name |
   FUNCTION func_name (arg1_type, arg2_type, ...) |
   INDEX object_name |
   LARGE OBJECT large_object_oid |
   OPERATOR op (left_operand_type, right_operand_type) |
   OPERATOR CLASS object_name USING index_method |
   [ PROCEDURAL ] LANGUAGE object_name |
   RULE rule_name ON table_name |
   SCHEMA object_name |
   SEQUENCE object_name |
   TRIGGER trigger_name ON table_name |
   TYPE object_name |
   VIEW object_name
} 
IS 'text'

犯罪

提交当前事务。

COMMIT [ WORK | TRANSACTION ]

复制

在文件和表之间复制数据。

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

创建聚合

定义一个新的聚合函数。

CREATE AGGREGATE name (
   BASETYPE = input_data_type,
   SFUNC = sfunc,
   STYPE = state_data_type
   [, FINALFUNC = ffunc ]
   [, INITCOND = initial_condition ]
)

创建演员阵容

定义一个新的演员阵容。

CREATE CAST (source_type AS target_type)
WITH FUNCTION func_name (arg_types)
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]

创建约束触发器

定义一个新的约束触发器。

CREATE CONSTRAINT TRIGGER name
AFTER events ON
table_name constraint attributes
FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

创建转换

定义一个新的转换。

CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name

创建数据库

创建一个新数据库。

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ TABLESPACE [=] tablespace ] 
]

创建域

定义一个新域。

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

其中约束是 -

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

创建函数

定义一个新函数。

CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | AS 'definition'
   | AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

创建组

定义一个新的用户组。

CREATE GROUP name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]

创建索引

定义一个新索引。

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

创建语言

定义一种新的过程语言。

CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ]

创建运算符

定义一个新的运算符。

CREATE OPERATOR name (
   PROCEDURE = func_name
   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
   [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
   [, RESTRICT = res_proc ] [, JOIN = join_proc ]
   [, HASHES ] [, MERGES ]
   [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
   [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
)

创建操作员类

定义一个新的运算符类。

CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method AS
{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
   | FUNCTION support_number func_name ( argument_type [, ...] )
   | STORAGE storage_type
} [, ... ]

创建规则

定义新的重写规则。

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

创建模式

定义一个新模式。

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]

创建序列

定义一个新的序列生成器。

CREATE [ TEMPORARY | TEMP ] SEQUENCE name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

创建表

定义一个新表。

CREATE [ [ GLOBAL | LOCAL ] { 
   TEMPORARY | TEMP } ] TABLE table_name ( { 
      column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
      | table_constraint
      | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] 
   } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

其中column_constraint是 -

[ CONSTRAINT constraint_name ] { 
   NOT NULL |
   NULL |
   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
   CHECK (expression) |
   REFERENCES ref_table [ ( ref_column ) ]
   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
   [ ON DELETE action ] [ ON UPDATE action ] 
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

table_constraint是-

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] )
REFERENCES ref_table [ ( ref_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

创建表为

根据查询结果定义一个新表。

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query

创建表空间

定义一个新的表空间。

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

创建触发器

定义一个新的触发器。

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )

创建类型

定义一个新的数据类型。

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

创建用户

定义一个新的数据库用户帐户。

CREATE USER name [ [ WITH ] option [ ... ] ]

其中选项可以是 -

SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP group_name [, ...]
| VALID UNTIL 'abs_time'

创建视图

定义一个新视图。

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

解除分配

取消分配准备好的语句。

DEALLOCATE [ PREPARE ] plan_name

宣布

定义一个光标。

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

删除

删除表的行。

DELETE FROM [ ONLY ] table [ WHERE condition ]

掉落骨料

删除聚合函数。

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

投掷

取出石膏。

DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

下降转换

删除一个转换。

DROP CONVERSION name [ CASCADE | RESTRICT ]

删除数据库

删除数据库。

DROP DATABASE name

删除域名

删除域。

DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

跌落功能

删除一个函数。

DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

下降组

删除用户组。

DROP GROUP name

掉落指数

删除索引。

DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

删除语言

删除过程语言。

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

删除操作员

删除一个操作员。

DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
[ CASCADE | RESTRICT ]

删除操作符类

删除一个运算符类。

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

丢弃规则

删除重写规则。

DROP RULE name ON relation [ CASCADE | RESTRICT ]

删除模式

删除架构。

DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]

下降顺序

删除一个序列。

DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]

掉落表

删除一个表。

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

删除表空间

删除表空间。

DROP TABLESPACE tablespace_name

跌落触发器

移除触发器。

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

跌落类型

删除数据类型。

DROP TYPE name [, ...] [ CASCADE | RESTRICT ]

删除用户

删除数据库用户帐户。

DROP USER name

下拉视图

删除视图。

DROP VIEW name [, ...] [ CASCADE | RESTRICT ]

结尾

提交当前事务。

END [ WORK | TRANSACTION ]

执行

执行准备好的语句。

EXECUTE plan_name [ (parameter [, ...] ) ]

解释

显示语句的执行计划。

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

拿来

使用游标从查询中检索行。

FETCH [ direction { FROM | IN } ] cursor_name

其中方向可以为空或以下之一 -

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

授予

定义访问权限。

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

插入

在表中创建新行。

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

听

收听通知。

LISTEN name

加载

加载或重新加载共享库文件。

LOAD 'filename'

锁

锁定一张桌子。

LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

其中lock_mode是以下之一 -

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

移动

定位光标。

MOVE [ direction { FROM | IN } ] cursor_name

通知

生成通知。

NOTIFY name

准备

准备一个要执行的语句。

PREPARE plan_name [ (data_type [, ...] ) ] AS statement

重新索引

重建索引。

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

释放保存点

销毁先前定义的保存点。

RELEASE [ SAVEPOINT ] savepoint_name

重置

将运行时参数的值恢复为默认值。

RESET name
RESET ALL

撤销

删除访问权限。

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

回滚

中止当前事务。

ROLLBACK [ WORK | TRANSACTION ]

回滚到保存点

回滚到保存点。

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

保存点

在当前事务中定义一个新的保存点。

SAVEPOINT savepoint_name

选择

从表或视图中检索行。

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

其中from_item可以是以下之一：

[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] )
[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column [, ...] ) ]

选择进入

根据查询结果定义一个新表。

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

放

更改运行时参数。

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

设定限制

设置当前事务的约束检查模式。

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

设置会话授权

设置当前会话的会话用户标识符和当前用户标识符。

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION

设置交易

设置当前事务的特征。

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

其中transaction_mode是以下之一 -

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

展示

显示运行时参数的值。

SHOW name
SHOW ALL

开始交易

启动一个交易块。

START TRANSACTION [ transaction_mode [, ...] ]

其中transaction_mode是以下之一 -

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

截短

清空一张桌子。

TRUNCATE [ TABLE ] name

不听

停止监听通知。

UNLISTEN { name | * }

更新

更新表的行。

UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]

真空

垃圾收集并可选择分析数据库。

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

PostgreSQL - 数据类型

在本章中，我们将讨论 PostgreSQL 中使用的数据类型。创建表时，为每一列指定数据类型，即要在表字段中存储什么类型的数据。

这带来了几个好处 -

• 一致性- 对相同数据类型的列的操作给出一致的结果，并且通常是最快的。

• 验证- 数据类型的正确使用意味着数据的格式验证和拒绝数据类型范围之外的数据。

• 紧凑性- 由于列可以存储单一类型的值，因此它以紧凑的方式存储。

• 性能- 正确使用数据类型可以实现最有效的数据存储。可以快速处理存储的值，从而提高性能。

PostgreSQL 支持多种数据类型。此外，用户可以使用CREATE TYPE SQL 命令创建自己的自定义数据类型。PostgreSQL 中有不同类别的数据类型。下面讨论它们。

数字类型

数字类型由两字节、四字节和八字节整数、四字节和八字节浮点数以及可选择精度的小数组成。下表列出了可用的类型。

货币类型

货币类型存储具有固定小数精度的货币金额。numeric、int 和 bigint数据类型的值可以转换为Money。由于可能出现舍入错误，因此不建议使用浮点数来处理资金。

字符类型

下表列出了 PostgreSQL 中可用的通用字符类型。

二进制数据类型

bytea数据类型允许存储二进制字符串，如下表所示。

日期/时间类型

PostgreSQL 支持一整套 SQL 日期和时间类型，如下表所示。日期是根据公历计算的。这里，除了日期类型，其分辨率为day之外，所有类型的分辨率均为1 微秒/14 位。

布尔类型

PostgreSQL 提供标准 SQL 类型 Boolean。布尔数据类型可以具有状态true、false和第三种状态known，该状态由SQL null 值表示。

枚举类型

枚举 (enum) 类型是包含静态、有序值集的数据类型。它们相当于许多编程语言中支持的枚举类型。

与其他类型不同，枚举类型需要使用 CREATE TYPE 命令创建。该类型用于存储静态、有序的值集。例如罗盘方向，即北、南、东、西或一周中的几天，如下所示 -

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

枚举一旦创建，就可以像任何其他类型一样使用。

几何型

几何数据类型表示二维空间对象。最基本的类型，即点，构成了所有其他类型的基础。

网络地址类型

PostgreSQL 提供了存储 IPv4、IPv6 和 MAC 地址的数据类型。最好使用这些类型而不是纯文本类型来存储网络地址，因为这些类型提供输入错误检查以及专门的运算符和函数。

位串类型

位字符串类型用于存储位掩码。它们是 0 或 1。有两种 SQL 位类型：bit(n)和bit Varying(n)，其中 n 是正整数。

文本搜索类型

此类型支持全文搜索，这是搜索自然语言文档集合以查找与查询最匹配的文档的活动。有两种数据类型 -

UUID类型

UUID（通用唯一标识符）被写为一系列小写十六进制数字，由连字符分隔成几组，具体来说是一组八位数字，后跟三组四位数字，最后是一组 12 位数，例如总共32位数字代表128位。

UUID 的示例为 - 550e8400-e29b-41d4-a716-446655440000

XML类型

XML数据类型可用于存储XML数据。为了存储 XML 数据，首先必须使用 xmlparse 函数创建 XML 值，如下所示 -

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

JSON 类型

json数据类型可用于存储 JSON（JavaScript 对象表示法）数据。此类数据也可以存储为text，但json数据类型的优点是检查每个存储的值是否是有效的 JSON 值。还有相关的支持函数可用，可以直接使用它们来处理 JSON 数据类型，如下所示。

数组类型

PostgreSQL 提供了将表的列定义为可变长度多维数组的机会。可以创建任何内置或用户定义的基类型、枚举类型或复合类型的数组。

数组声明

数组类型可以声明为

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

或者使用关键字“ARRAY”作为

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

插入值

数组值可以作为文字常量插入，将元素值括在花括号内并用逗号分隔。下面显示了一个示例 -

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’); 

访问数组

下面显示了访问数组的示例。下面给出的命令将选择第二季度储蓄比第四季度储蓄更多的人。

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

修改数组

修改数组的示例如下所示。

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

或使用 ARRAY 表达式语法 -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

搜索数组

搜索数组的示例如下所示。

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

如果已知数组的大小，则可以使用上面给出的搜索方法。另外，下面的示例显示了在大小未知时如何进行搜索。

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

复合类型

该类型表示字段名称及其数据类型的列表，即表的行或记录的结构。

复合类型的声明

以下示例展示了如何声明复合类型

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

此数据类型可用于创建表，如下所示 -

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

复合值输入

复合值可以作为文字常量插入，将字段值括在括号内并用逗号分隔。下面显示了一个示例 -

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

这对于上面定义的inventory_item有效。只要表达式中有多个字段，ROW 关键字实际上是可选的。

访问复合类型

要访问复合列的字段，请使用点后跟字段名称，就像从表名称中选择字段一样。例如，要从 on_hand 示例表中选择一些子字段，查询将如下所示 -

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

您甚至还可以使用表名称（例如在多表查询中），如下所示 -

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

范围类型

范围类型表示使用一定范围数据的数据类型。范围类型可以是离散范围（例如，所有整数值 1 到 10）或连续范围（例如，上午 10:00 到 11:00 之间的任何时间点）。

可用的内置范围类型包括以下范围 -

• int4range - 整数范围

• int8range - bigint 的范围

• numrange - 数字范围

• tsrange - 没有时区的时间戳范围

• tstzrange - 带时区的时间戳范围

• daterange - 日期范围

可以创建自定义范围类型以使新类型的范围可用，例如使用 inet 类型作为基础的 IP 地址范围，或使用 float 数据类型作为基础的浮点范围。

范围类型分别使用 [ ] 和 ( ) 字符支持包含和排除范围边界。例如，“[4,9)”表示从 4 开始（含 4）到 9（不包括 9）的所有整数。

对象标识符类型

对象标识符 (OID) 在 PostgreSQL 内部用作各种系统表的主键。如果指定了WITH OIDS或启用了default_with_oids配置变量，则只有在这种情况下，OID 才会添加到用户创建的表中。下表列出了几种别名类型。除了专门的输入和输出例程外，OID 别名类型没有自己的操作。

伪类型

PostgreSQL 类型系统包含许多特殊用途的条目，统称为伪类型。伪类型不能用作列数据类型，但可用于声明函数的参数或结果类型。

下表列出了现有的伪类型。