数值类型

货币类型

money            8字节              

money 类型存储带有固定小数精度的货币金额，可以与numeric、int、bigint进行转化。不建议使用浮点数处理货币类型，因为可能存在精度问题。

字符类型

char(n) ，character(n)定长，不足补空白

varchar(n) ，character varying(n)变长，有长度限制

text 变长，无长度限制

日期 / 时间

布尔类型

boolean   一字节     true / false / null

枚举类型

创建枚举   

create type 枚举名 as enum('值1', '值2', ..., '值n');

例如：create type mood enum as ('aaa','bbb');

使用枚举    

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

几何类型

网络地址类型

PostgreSQL 提供用于存储 IPv4 、IPv6 、MAC 地址的数据类型。

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

位串类型

位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。 我们有两种 SQL 位类型：bit(n) 和bit varying(n)， 这里的n是一个正整数。

bit 类型的数据必须准确匹配长度 n， 试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型；更长的串会被拒绝。 写一个没有长度的bit 等效于 bit(1)， 没有长度的 bit varying 意思是没有长度限制。

json类型

JSON 代表 JavaScript Object Notation。JSON是开放的标准格式，由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同，JSON是人类可读的文本格式。

从PostgreSQL 9.3开始，json就成了postgres里的一种数据类型，也就是和varchar、int一样，我们表里的一个字段的类型可以为json了。

与此同时，postgres还提供了jsonb格式，jsonb格式是json的二进制形式，二者的区别在于json写入快，读取慢，jsonb写入慢，读取快，但在操作上，二者是没有区别的。

注意：键值对的键必须使用双引号

json、jsonb通用操作符

jsonb额外操作符

创建表

假设我们要存储的json数据是这样的：

{
"id": ID
"name":"名字",
"age":年龄
}

建表语句如下：

create table if not exists user (

info jsonb

)

好了，这样就创建了一张表，里面只有一个 info 字段，下面开始进行CRUD操作。

插入数据

插入数据可以直接以json格式插入：

insert into user values('{"id":1,"name":"小明", "age":18}')

在json里插入新的key值gender(info中的id为1时)，如下：

select info || '{"gender":2}'::jsonb from user where (info->>'id')::int4 = 1;

注：|| 为json连接符

查询数据

Postgres里的查询需要用到查询符。比如说，我们要查询id为1的数据，语句如下：

select info from user where info @> '{"id":1}'::jsonb

用到了 @> 这个查询符，表明info当前这条记录里的顶层json中有没有id为1的key-value对；有的话则满足条件。

再来一个复杂一点的查询的，查询 age>16 的记录，并且只显示 name ，语句如下：

select info->'name' from user where (info->>'age')::int4 > 16

修改数据

下面，将info中id为1的age改为22 ，SQL语句：

select info || '{"age":22}'::jsonb from user where (info->>'id')::int4 = 1;

上述用法仅适用于9.5以上，9.5以下需要整个记录更新，不可以单独修改某个值。

删除数据

删除所以info的age这个key，SQL如下：

select info-'age' from user

直接用操作符 - 即可。

数组类型

PostgreSQL 允许将字段定义成变长的多维数组。

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

声明数组

创建表的时候，我们可以声明数组，方式如下：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。

我们也可以使用 "ARRAY" 关键字，如下所示：

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

插入值

插入值使用花括号 {}，元素在 {} 使用逗号隔开：

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';

数组中检索

要搜索一个数组中的数值，你必须检查该数组的每一个值。

比如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

复合类型

1、创建符合类型

create type 类型名 as (

        属性1    属性1类型

        属性2    属性2类型

        ...

        属性n   属性n类型

);

例如：

create type complex as(

id int,

name text

);

2、创建表使用复合类型

create table user (

        complex_value complex,

        age int

);

3、向表中插入数据 - 

insert into user(complex_value, age) values((1,"张三"), 20);

4、访问复合类型   --    (复合类型名).复合类型属性，加括号防止与 表名.表属性 混合

select (complex).name from user where (complex).id = 1;

若需要添加表名也可这样写：

select (user.complex).name from user where (user.complex).id = 1;

范围类型

范围数据类型代表着某一元素类型在一定范围内的值。

例如，timestamp 范围可能被用于代表一间会议室被预定的时间范围。

PostgreSQL 内置的范围类型有：

• int4range — integer的范围

• int8range —bigint的范围

• numrange —numeric的范围

• tsrange —timestamp without time zone的范围

• tstzrange —timestamp with time zone的范围

• daterange —date的范围

此外，你可以定义你自己的范围类型。

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

范围值的输入必须遵循下面的格式：

(下边界,上边界)
(下边界,上边界]
[下边界,上边界)
[下边界,上边界]
空

圆括号或者方括号显示下边界和上边界是不包含的还是包含的。注意最后的格式是 空，代表着一个空的范围（一个不含有值的范围）。

-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;