Postgersql 从小白到高手 二 ： 数据类型

postgresql 10.0与前面版本语法有异动，本系列以10.0 后为基础，具体sql以16.0为版本。
一：数据类型
1.数字类型

整数类型smallint、integer和bigint ，任意精度 NUMERIC(precision, scale) ，
NUMERIC(长度, 小数位数) 。
浮点类型real和double precision。
序数类型：
smallserial、serial和bigserial类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的AUTO_INCREMENT属性）。
例如： CREATE TABLE tablename (
colname SERIAL
);
2.货币类型
固定两位小数的NUMERIC

3.字符类型

character varying(n)和character(n)， 其中n是一个正整数。两种类型都可以存储最多n个字符长的串。
PostgreSQL提供text类型，它可以存储任何长度的字符串。
character(n) char(n) ,固定长度n。

4.二进制数据类型

二进制串是一个八位位组（或字节）的序列，bytea类型支持两种用于输入和输出的外部格式：PostgreSQL的历史的“转义”格式和“十六进制”格式。在输入时这两种格式总是会被接受。输出格式则取决于配置参数bytea_output，其默认值为十六进制（注意十六进制格式是在PostgreSQL 9.0中被引入的，早期的版本和某些工具无法理解它）。
SQL标准如Oracle 定义了一种不同的二进制串类型， 叫做BLOB或者BINARY LARGE OBJECT。其输入格式和bytea不同，但是提供的函数和操作符大多一样。
十六进制例：SELECT E’\xDEADBEEF’;
转义：

5.日期/时间类型

6.布尔类型

7.枚举类型
枚举（enum）类型是由一个静态、值的有序集合构成的数据类型。
声明变量为枚举：
CREATE TYPE mood AS ENUM (‘sad’, ‘ok’, ‘happy’);
建表:
CREATE TABLE person (
name text,
current_mood mood
);
使用：
INSERT INTO person VALUES (‘Moe’, ‘happy’);
SELECT * FROM person WHERE current_mood = ‘happy’;
name | current_mood
------±-------------
Moe | happy
(1 row)

8.几何类型

9.网络地址类型

10.位串类型
位串就是一串 1 和 0 的串。它们可以用于存储和可视化位掩码。我们有两种类型的 SQL 位类型：bit(n)和bit varying(n)，其中 n是一个正整数。

11.文本搜素类型
PostgreSQL提供两种数据类型，它们被设计用来支持全文搜索，全文搜索是一种在自然语言的文档集合中搜索以定位那些最匹配一个查询的文档的活动。tsvector类型表示一个为文本搜索优化的形式下的文档，tsquery类型表示一个文本查询。
一个tsvector值是一个排序的可区分词位的列表，词位是被正规化合并了同一个词的不同变种的词。
一个tsquery值存储要用于搜索的词位，并且使用布尔操作符&（AND）、|（OR）和!（NOT）来组合它们，还有短语搜索操作符<->（FOLLOWED BY）。也有一种 FOLLOWED BY 操作符的变体，其中N是一个整数常量，它指定要搜索的两个词位之间的距离。<->等效于<1>。
圆括号可以被用来强制对操作符分组。如果没有圆括号，!（NOT）的优先级最高，其次是<->（FOLLOWED BY），然后是&（AND），最后是|（OR）。
Postgresql 同时支持nosql ，这块的文本检索功能可以结合es来的理解。

12. uuid
    一个UUID被写成一个小写十六进制位的序列，该序列被连字符分隔成多个组：首先是一个8位组，接下来是三个4位组，最后是一个12位组。
    例如：a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

13.xml
要从字符数据中生成一个xml类型的值，可以使用函数xmlparse:
例如：
XMLPARSE (DOCUMENT ‘<?xml version="1.0"?>…’)
XMLPARSE (CONTENT ‘abcbarfoo’)
从xml产生一个字符串可以使用函数xmlserialize:
XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

14.json
json数据类型：json jsonb
json类似文本存入取出，格式没有变化，jsonb 是jsonObeject化存入，取出的格式和存入略有不同。
json和jsonb 的合法表达式：
– 简单标量/基本值
– 基本值可以是数字、带引号的字符串、true、false或者null
SELECT ‘5’::json;
– 有零个或者更多元素的数组（元素不需要为同一类型）
SELECT ‘[1, 2, “foo”, null]’::json;
– 包含键值对的对象
– 注意对象键必须总是带引号的字符串
SELECT ‘{“bar”: “baz”, “balance”: 7.77, “active”: false}’::json;
– 数组和对象可以被任意嵌套
SELECT ‘{“foo”: [true, “bar”], “tags”: {“a”: 1, “b”: null}}’::json;

SELECT ‘{“bar”: “baz”, “balance”: 7.77, “active”:false}’::json;
查询结果：

                  json                 

---

{“bar”: “baz”, “balance”: 7.77, “active”:false}
(1 row)

SELECT ‘{“bar”: “baz”, “balance”: 7.77, “active”:false}’::jsonb;

                  jsonb                    

---

{“bar”: “baz”, “active”: false, “balance”: 7.77}
(1 row)

jsonb 有很多简便方法函数：
– 简单的标量/基本值只包含相同的值：
SELECT ‘“foo”’::jsonb @> ‘“foo”’::jsonb;
– 右边的数字被包含在左边的数组中：
SELECT ‘[1, 2, 3]’::jsonb @> ‘[1, 3]’::jsonb;
– 数组元素的顺序没有意义，因此这个例子也返回真：
SELECT ‘[1, 2, 3]’::jsonb @> ‘[3, 1]’::jsonb;
– 重复的数组元素也没有关系：
SELECT ‘[1, 2, 3]’::jsonb @> ‘[1, 2, 2]’::jsonb;
– 右边具有一个单一键值对的对象被包含在左边的对象中：
SELECT ‘{“product”: “PostgreSQL”, “version”: 9.4, “jsonb”: true}’::jsonb @> ‘{“version”: 9.4}’::jsonb;
– 右边的数组不会被认为包含在左边的数组中，
– 即使其中嵌入了一个相似的数组：
SELECT ‘[1, 2, [1, 3]]’::jsonb @> ‘[1, 3]’::jsonb; – 得到假
– 但是如果同样也有嵌套，包含就成立：
SELECT ‘[1, 2, [1, 3]]’::jsonb @> ‘[[1, 3]]’::jsonb;
– 类似的，这个例子也不会被认为是包含：
SELECT ‘{“foo”: {“bar”: “baz”}}’::jsonb @> ‘{“bar”: “baz”}’::jsonb; – 得到假
– 包含一个顶层键和一个空对象：
SELECT ‘{“foo”: {“bar”: “baz”}}’::jsonb @> ‘{“foo”: {}}’::jsonb;
jsonb一般使用gin索引。

15.数组类型
创建：
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
指定长度：
CREATE TABLE tictactoe (
squares integer[3][3]
);

插入数据：
INSERT INTO sal_emp
VALUES (‘Bill’,
‘{10000, 10000, 10000, 10000}’,
‘{{“meeting”, “lunch”}, {“training”, “presentation”}}’);
INSERT INTO sal_emp
VALUES (‘Carol’,
‘{20000, 25000, 25000, 25000}’,
‘{{“breakfast”, “consulting”}, {“meeting”, “lunch”}}’);

查询数据：
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
name
-------
Carol
(1 row)

更新：
UPDATE sal_emp SET pay_by_quarter = ‘{25000,25000,27000,27000}’
WHERE name = ‘Carol’;

16.复合类型
定义复合类型：
CREATE TYPE complex AS (
r double precision,
i double precision
);
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);

17.范围类型
范围类型是表达某种元素类型（称为范围的subtype）的一个值的范围的数据类型。
例如：
PostgreSQL 带有下列内建范围类型：
int4range — integer的范围
int8range — bigint的范围
numrange — numeric的范围
tsrange — 不带时区的 timestamp的范围
tstzrange — 带时区的 timestamp的范围
daterange — date的范围

18.对象标识
对象标识符（OID）被PostgreSQL用来在内部作为多个系统表的主键。OID不会被添加到用户创建的表中，除非在创建表时指定了WITH OIDS或者default_with_oids配置变量被启用。
oid的别名类型：regproc、regprocedure、regoper、regoperator、regclass、regtype、regrole、regnamespace、regconfig和regdictionary。
使用例如：
SELECT * FROM pg_attribute WHERE attrelid = ‘mytable’::regclass;

19.pg ins 、伪类型
pg_lsn数据类型可以被用来存储 LSN（日志序列号）数据，LSN 是一个指向 WAL 中的位置的指针。

20.域类型
域是基于另一种基础类型的用户定义的数据类型。
CREATE DOMAIN posint AS 整数 CHECK (VALUE > 0);
创建表 mytable (id posint);
插入 mytable VALUES(1); ——作品
插入 mytable VALUES(-1); – 失败