postgresql使用

数据类型、常用系统函数、database、模式、序列、分页、

数据类型

数值类型

关键字 存储空间 描述 范围
smallint 2 字节 小范围整数 -32768 到 +32767
integer 4字节 常用的整数 -2147483648 到 +2147483647
bigint 8 字节 大范围的整数 -9223372036854775808 到 9223372036854775807
decimal 变长 用户声明精度，精确 无限制
numeric 变长 用户声明精度，精确 无限制
real 4字节 变精度，不精确 6 位十进制数字精度
double 8 字节 变精度，不精确 15 位十进制数字精度
serial 4 字节 自增整数 1 到 +2147483647
bigserial 8 字节 大范围的自增整数 1 到 9223372036854775807

字符类型

关键字 描述
varchar 变长，有长度限制
char 定长，不足补空白
text 边长，无长度限制

日期时间类型

关键字 存储空间 描述 最低值 最高值 分辨率
timestamp[无时区] 8字节 包括日期和时间 4713 BC 5874897AD 1毫秒/14位
timestamp[含时区] 8字节 日期和时间，带时区 4713 BC 5874897AD 1毫秒/14位
interval 12字节 时间间隔 -178000000年 178000000年 1毫秒/14位
date 4字节 只用于日期 4713 BC 32767AD 1天
time[无时区] 8字节 只用于一日内时间 00:00:00 24:00:00 1毫秒/14位

数组类型

声明数组字段（三种）：

1
2
3
4
5
CREATE TABLE test (
  test_arr1 integer[],
  test_arr2 integer[4],
  test_arr3 integer ARRAY[4]
);

对数组进行操作：

1
2
3
4
5
6
//查询数组
SELECT test_arr[3] FROM test;
//全部更改
UPDATE test SET test_arr = '{31000,32000,33000,34000}';
//只更改某一项
UPDATE test SET test_arr[4] = 15000；

json类型

json类型分为两种：

  • json 存储格式为文本
  • jsonb 存储格式为二进制

两者的比较：

  • jsonb通常比json占用更多的磁盘空间
  • jsonb比json的写入更耗时间
  • json的操作比jsonb的操作明显更耗时间（在操作一个json类型值时需要每次都去解析）

如何选择：

  • 如果你的应用只用json表示，PostgreSQL只用于保存与获取时，你应该使用json
  • 如果你需要在PostgreSQL中做比较多的json值的操作，或者在一些json字段上使用索引时，你应该使用jsonb

json与jsonb之间的操作符

操作符 右操作数的类型 描述 示例 示例结果
-> int 获取JSON数组元素（索引从0开始） ‘[{“a”:”foo”},{“b”:”bar”},{“c”:”baz”}]’::json->2 {“c”:”baz”}
-> text 通过秘钥获取JSON对象字段 ‘{“a”: {“b”:”foo”}}’::json->’a’ {“b”:”foo”}
->> int 获取JSON数组元素为text ‘[1,2,3]’::json->>2 3
->> text 获取JSON对象字段为text ‘{“a”:1,”b”:2}’::json->>’b’ 2
#> text[] 在指定的路径获取JSON对象 ‘{“a”: {“b”:{“c”: “foo”}}}’::json#>’{a,b}’ {“c”: “foo”}
#>> text[] 在指定的路径获取JSON对象为text ‘{“a”:[1,2,3],”b”:[4,5,6]}’::json#>>’{a,2}’ 3

额外的jsonb操作符

操作符 右操作数的类型 描述 示例
@> jsonb 左侧的JSON值包含右侧的值吗？ ‘{“a”:1, “b”:2}’::jsonb @> ‘{“b”:2}’::jsonb
<@ jsonb 左侧的JSON值包含在右侧的值中吗？ ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb
? text 键/元素字符串包含JSON值吗？ ‘{“a”:1, “b”:2}’::jsonb ? ‘b’
?| text[] 是否存在任一键/元素字符串？ ‘{“a”:1, “b”:2, “c”:3}’::jsonb ?|array[‘b’, ‘c’]
?& text[] 是否所有键/元素字符串都存在？ ‘[“a”, “b”]’::jsonb ?& array[‘a’, ‘b’]

常用系统函数

关键字 描述
abs 取绝对值
cbrt 立方根
replace 文本替换
btrim 从string开头和结尾删除只包含在characters里(缺省是空白)的字符的最长字串
ltrim 从字串string的开头删除只包含characters(缺省是一个空白)的最长的字串。
to_char 转为字符串类型
to_number 转为数值类型

分页

对于postgresql分页使用limit offset方法进行，使用示例如下：

1
SELECT column FROM table LIMIT 2 OFFSET 1

具体语法为：

limit 返回行数 offset 查询起点位置

索引

PostgreSQL提供了多种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

b-tree

CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符” <、<=、=、>=和>”作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE ‘foo%’或col ~ ‘^foo’，索引才会生效，否则将会执行全表扫描，如：col LIKE ‘%bar’。

Hash

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。

这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。

GiST

GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。

GIN

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：<@、@>、=、&&等。

唯一索引

CREATE UNIQUE INDEX name ON table (column [, …]);

目前，只有B-Tree索引可以被声明为唯一索引。如果索引声明为唯一索引，那么就不允许出现多个索引值相同的行。我们认为NULL值相互间不相等。