PostgreSQL几个常用的数据类型一般表中都会有一个字段作为主键,如果表本身字段不适合作为主键字段,会增加一个专门的

1.数值类型

serial类型

smallserial、serial和bigserial序数类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的AUTO_INCREMENT属性

一般表中都会有一个字段作为主键,如果表本身字段不适合作为主键字段,会增加一个专门的字段并指定为serial类型作为主键。创建表时如果指定了一个字段为serial，那么PostgreSQL会首先创建一个名为table_name_column_name_seq的序列。然后设定该序列为该整形字段的取值来源。

CREATE TABLE serial_table (
    colname SERIAL
);

图1

在PostgreSQL中，序列自身就是一种数据库资产，除了通过创建serial类型数据自动创建序列，也可以通过sql创建一个自定义序列让多个表共用。如果需要生成一个跨多表的唯一主键，那么这种共享序列的用法很方便。

drop table if exists user_1;
drop table if exists user_2;
drop sequence if exists test;
create sequence test start 1;
create table user_1(id bigint default nextval('test') primary key,name jsonb );
create table user_2(id bigint default nextval('test') primary key,name jsonb );

insert into user_1(name)values ('{"name":"张三"}');
insert into user_2(name)values ('{"name":"李四"}');

图2

图3

图4

2.文本类型 text

PostgreSQL有三种最基础的文本数据类型：character(也称为 char)、character varying(也称为varchar)和text。如果使用char类型定义长度，在存储数据时，PostgreSQL会自动在后面用空格填充，知道填充满定义的长度为止。相比varchar或者text，char的右补齐空格机制会导致存储空间的浪费，得到的好处是可以确定字符串是定长。相比charchar和text，char没有性能上的优势，却一定会多占更多的空间。所以，仅当需要存储邮政编码，手机号，身份证号码等定长字符串时，才应该使用char类型；如果要存储边长的字符串应使用varchar类型。 varchar类型的最大长度是可选的，如果不设定的话，varchar和text几乎没有任何差别，但通过特定的驱动连接到PostgreSQL时，二者还是有一些细微的差别：ODBC驱动可以对varchar字段进行排序，却不支持text类型。有时候，为了保持跨平台应用的兼容性，需要使用字符串操作不区分大小写。此时需要对字符串进行操作，相比text，varchar记性运算符操作更容易一些。

3.时间类型

PostgreSQL除了支持常见的日期和时间类型，还支持时区，并能够按照不同的时区对夏令时进行自动转换。除此之外，PostgreSQL还支持特殊的数据类型：interval，该类型可以对日期时间进行数学运算。PostgreSQL还支持区间类型，但是目前我所在的公司还是在使用基本的时间类型。

date

仅存储月、日、年，没有时区、小时、分和秒的信息

time

仅存储小时、分、秒信息，没有时区和日期的信息

timestamp（timestamp without time zone）

该类型存储了日期和时间数据，但是没有时区信息

timestamptz（timestamp with time zone）

该类型存储了日期和时间数据以及时区信息。在系统内部，该类型的字段值是以UTC世界标准时间格式存储的，但是查询显示的时候，会按照服务器的时区设置进行换算后在现实。如果插入不带时区，那么服务器会自动按照数据库的时区补充。

timetz（time with time zone）

与timestametz类型，最燃懈怠了时区，但是没有显示日期的信息

interval

该类型描述一个时间段的长度，单位可以使小时、天、月、分钟或者其他粒度。此类型适用于对日期和时间进行数学运算的场景。

tsrange

该类型包含两个时间戳以及开区间和闭区间限定符，如:'[2020-01-01 00:00 2020-04-28 00:00)'::tsrange定义了时间段。

tstzrange

该类型可用于定义timestamp with timezone的开区间和闭区间。

daterange

该类型可用于定义日期和开区间和闭区间

4.JSON数据类型

PostgreSQL支持JSON数据类型并提供了很多函数。PostgreSQL在9.4中引入了JSONB数据类型，该类型是JSON类型的二进制版本，它与JSON类型最主要的差别是JSONB类型可以支持索引而JSON不可以(只能使用非常受限制的函数索引)。在9.5中为JSONB类型引入了更多的支持函数，包括可用于修改JSONB对象中的子元素函数。在9.6中引入了json_insert函数，可以实现往一个现在的JSONB对象插入数据，即在内部对象中新增元素、键值对。

查找jsonb类型数据中的值

select name->'name' as name from user_1;
--或者 select name::jsonb->'name' as name from user_1;

name
"张三"

select name->>'name' as name from user_1;
--或者 select name::jsonb->>'name' as name from user_1;

name
张三

jsonb类型数据当做条件参数使用

select id from user_1 where name::jsonb->>'name' = '张三';

josnb类型数据更新

jsonb_set(target         jsonb,  // 需要修改的数据
          path           text[], // 数据路径
          new_value      jsonb,  // 新数据
          create_missing boolean default true)

部分属性的添加和替换

update user_1 set name = jsonb_set(name, '{sex}', '"男"', true) where id = 1;
select name from user_1 where id =1;

name
{"age": 18, "sex": "男", "name": "张三"}

update user_1 set name = jsonb_set(name, '{age}', '"20"', true) where id = 1;
select name from user_1 where id =1;

name
{"age": 20, "sex": "男", "name": "张三"}