PostgreSQL（一）数据类型

特征

• 函数：通过函数，可以在数据库服务器端执行指令程序。

• 索引：用户可以自定义索引方法，或使用内置的 B 树，哈希表与 GiST 索引。

• 触发器：触发器是由SQL语句查询所触发的事件。如：一个INSERT语句可能触发一个检查数据完整性的触发器。触发器通常由INSERT或UPDATE语句触发。 多版本并发控制：PostgreSQL使用多版本并发控制（MVCC，Multiversion concurrency control）系统进行并发控制，该系统向每个用户提供了一个数据库的"快照"，用户在事务内所作的每个修改，对于其他的用户都不可见，直到该事务成功提交。

• 规则：规则（RULE）允许一个查询能被重写，通常用来实现对视图（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、删除（DELETE）。

• 数据类型：包括文本、任意精度的数值数组、JSON 数据、枚举类型、XML 数据

  等。

• 全文检索：通过 Tsearch2 或 OpenFTS，8.3版本中内嵌 Tsearch2。

• NoSQL：JSON，JSONB，XML，HStore 原生支持，至 NoSQL 数据库的外部数据包装器。

• 数据仓库：能平滑迁移至同属 PostgreSQL 生态的 GreenPlum，DeepGreen，HAWK 等，使用 FDW 进行 ETL。

命令

• BEGIN 开始一个事务，COMMIT / END 提交当前事务。

• ROLLBACK 回滚事务

• ABORT 丢弃当前事务 ABORT [ WORK | TRANSACTION ]

• ALTER 修改

  • ALTER COLLATION 修改一个排序规则定义 。

  • ALTER CONVERSION 修改一个编码转换的定义。

  • ALTER DATABASE 修改一个数据库。

  • ALTER AGGREGATE 修改一个聚集函数的定义 。

  • ALTER DEFAULT PRIVILEGES 定义默认的访问权限。

  • ALTER FUNCTION 修改一个函数的定义。

  • ALTER INDEX 修改一个索引的定义。

  • ALTER SCHEMA 修改一个模式的定义。

  • ALTER TABLE 修改表的定义。

  • ALTER TYPE 修改类型的定义

  • ALTER SEQUENCE 修改一个序列发生器

• CREATE 创建

  • CREATE AGGREGATE 创建一个新的聚集函数。

  • CREATE DATABASE 创建新数据库。

  • CREATE FUNCTION 创建一个新函数。

  • CREATE INDEX 创建一个新索引。

  • CREATE SCHEMA 创建一个新模式。

  • CREATE SEQUENCE 创建一个新序列发生器。

  • CREATE TABLE 创建一个新表。

  • CREATE TABLE AS 从一条查询的结果中创建一个新表。

  • CREATE TABLESPACE 创建一个新的表空间。

  • CREATE TYPE 创建一个类型

  • CREATE VIEW 创建一个视图。

• DELETE 删除表中的行数据

• TRUNCATE 整表删除，保留表结构

• DROP 删除

  • DROP AGGREGATE 删除一个用户定义的聚集函数。

  • DROP DATABASE 删除一个数据库。

  • DROP FUNCTION 删除一个函数。

  • DROP INDEX 删除一个索引。

  • DROP SCHEMA 删除一个模式。

  • DROP SEQUENCE 删除一个序列。

  • DROP TABLE 删除一个表数据和表结构。

  • DROP TYPE 删除一个类型

• EXPLAIN 显示一个语句的执行规划。

• INSERT INTO 向表中插入数据

• SELECT 查询表数据

• UPDATE 更新表数据

数据类型

数值

• 整数：smallint 2字节, integer 4字节, bigint 8字节

• 浮点数

  • 精确：decimal ，numeric

  • 不精确：real 4字节, double precision 8字节

• 自增整数：smallserial 2字节, serial 4字节, bigserial 8字节（等于MySQL的AUTO_INCREMENT）

货币类型

money：8字节，存储带有固定小数精度的货币金额，numeric、int 和 bigint 类型的值可以转换为 money

字符

• char

• varchar

• text，无长度限制

时间日期

• timestamp

• date

• time

• interval ，时间间隔

布尔类型

boolean，有三个值：true , false , NULL（未知）

枚举

枚举类型是一个包含静态和值的有序集合的数据类型，枚举类型需要使用 CREATE TYPE 命令创建

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

几何类型

几何数据类型表示二维的平面物体，最基本的类型：点。它是其它类型的基础。

• point，点 16字节，(x,y)

• line，直线 32字节，((x1,y1),(x2,y2))

• lseg，线段 32字节， ((x1,y1),(x2,y2))

• box，矩形 32字节， ((x1,y1),(x2,y2))

• path，闭合路径（与多边形类似） 16+16n 字节，((x1,y1),...)

• path，开放路径 16+16n 字节， [(x1,y1),...]

• polygon，多边形（与闭合路径类似） 40+16n 字节， ((x1,y1),...)

• circle，圆 24字节，<(x,y),r> (圆心和半径)

网络地址

存储 IPv4 、IPv6 、MAC 地址的数据类型。

用这些数据类型存储网络地址比用纯文本类型好， 因为这些类型提供输入错误检查和特殊的操作和功能

• cidr，IPv4 或 IPv6网络

• inet，IPv4 或 IPv6主机和网络

• macaddr，MAC地址

XML类型

xml 数据类型可以用于存储XML数据。 将 XML 数据存到 text 类型中的优势在于它能够为结构良好性来检查输入值， 并且还支持函数对其进行类型安全性检查，使用这个数据类型，编译时必须使用 configure --with-libxml

创建XML值

使用函数 xmlparse: 来从字符数据产生 xml 类型的值：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

JSON类型

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值

数组类型

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型

创建或修改表时，integer[]，text[][] ，一维数组和二维数组，也可以使用 ARRAY 关键字：

integer ARRAY[4]

复合类型

复合类型表示一行或者一条记录的结构； 它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL 允许像简单数据类型那样使用复合类型。