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位

数组类型

声明数组字段（三种）：

 CREATE TABLE test (
   test_arr1 integer[],
   test_arr2 integer[4],
   test_arr3 integer ARRAY[4]
 );

对数组进行操作：

//查询数组
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方法进行，使用示例如下：

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值相互间不相等。