简介

PostgreSQL提供了非常丰富的数据类型，我们平常使用最多的基本就3类：

1. 数字类型
2. 字符类型
3. 时间类型

这篇文章重点介绍这3中类型，因为对于高并发项目还是推荐：尽量使用简单类型，把运算和逻辑放在应用中，而不是数据库上。

如果有离线数据分析处理、做报表等低并发的业务应用，可以多了解一点PostgreSQL的其他类型，作为知识面的拓展，可以在主导设计表的时候有更丰富和灵活的选择，其他类型的详细信息可以参考官方文档。

数字类型

数字类型概览

类型	字节长度	说明	范围
smallint	2 字节	2字节整数，int2	-32768 到 +32767
integer	4 字节	4字节整数，int,int4	-2147483648 到 +2147483647
bigint	8 字节	8字节大整数，int8	-9223372036854775808 到 +9223372036854775807
numeric	可变长	用户指定的精度，精确	小数点前 131072 位；小数点后 16383 位
decimal	可变长	等价于numeric
real	4 字节	单精度浮点数，float4	6 位十进制数字精度
double precision	8 字节	双精度浮点数，float8	15 位十进制数字精度
smallserial	2 字节	自增的小范围整数	1 到 32767
serial	4 字节	自增整数	1 到 2147483647
bigserial	8 字节	自增的大范围整数	1 到 9223372036854775807

PostgreSQL没有像MySQL的tinynit这样的1字节整型，如果需要就用smaillint吧，不要用bytea，bytea需要额外的空间，并且要转。

numeric decimal money

postgresql的numeric和decimal等价：numeric可以兼容oracle，decimal可以兼容MySQL。

numeric(precision, scale)，numeric(12,4)表示可以存储12位数字，其中小数4位，整数8位(12-4=8)。

存储数据时，整数不能超过8(precision-scale)位，超过8位报错，小数超过4(scale)位会四舍五入。

-- money只保留2位小数
CREATE TABLE price (
    id SERIAL PRIMARY KEY,
    var_val VARCHAR(100),
    price money
);

insert into price(var_val,price) values('123456789.123456789',123456789.123456789);
select * from price;

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    var_val VARCHAR(100),
    numeric_val NUMERIC(12, 4),
    decimal_val decimal(12, 4)
);

-- 整数超过8位出错

-- ERROR:  A field with precision 12, scale 4 must round to an absolute value less than 10^8.numeric field overflow 

-- ERROR:  numeric field overflow
-- SQL state: 22003
-- Detail: A field with precision 12, scale 4 must round to an absolute value less than 10^8.
insert into orders(var_val,numeric_val,decimal_val) values('123456789.123456789',123456789.123456789,123456789.123456789);

小数位的处理方式，采用四舍五入截断：

-- 运行插入，实际存储值12345678.1235
insert into orders(var_val,numeric_val,decimal_val) values('12345678.123456789',12345678.123456789,12345678.123456789);

sequence

聊数据库有一个绕不开的话题，自增主键。

对于PostgreSQL聊自增主键之前，我们得先了解一下sequence。

创建sequence与参数

参数	说明
INCREMENT	步长，每次增加多少
START	初始值
MINVALUE	最小值
MAXVALUE	最大值
CACHE	缓存多少，就是一次生成多个缓存起来，默认值1
NO CYCLE	表示不循环

创建sequence，名字通常是:表名_字段名_seq

CREATE SEQUENCE IF NOT EXISTS public.user_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1
    NO CYCLE;

调用sequence

使用nextval来触发sequence，让它自增。

-- 让xxx_zzz_seq这个sequence自增
select nextval('xxx_zzz_seq');

-- 调用之后，current value自动递增
select nextval('user_id_seq') as r;

表字段关联sequence

alter table table_name alter column id set DEFAULT nextval('xxx_zzz_seq')

可以看到，本质上是表字段设置一个默认值，这个默认值是一个函数nextval，它的参数是sequence的名称。

sequence的其他操作

--删除序列
drop sequence xxx_zzz_seq;

--重置序列
alter sequence xxx_zzz_seq restart with 1;

--修改序列（修改序列的最小值和最大值）
alter sequence sequence_name MINVALUE new_min_value MAXVALUE new_max_value;

查看sequence信息

sequence的信息可以在pg_sequences表查看。

select * from pg_sequences;

自增主键

MySQL习惯了为表添加自增主键，PostgreSQL想要同样的效果可以使用serial。

其实MySQL和PostgreSQL自增主键的实现完全不一样。

PostgreSQL的serial更像一个语法糖，实际上是通过sequence实现。

serial语法糖

serial不是SQL标准的语法，所以PostgreSQL推荐还是使用标准语法来实现：

-- 创建序列
CREATE SEQUENCE IF NOT EXISTS user_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1
    OWNED BY "user".id;
 
-- 创建表格,并为id给定默认值，nextval相当于一个函数
CREATE TABLE user (
    id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('user_id_seq'),
    name VARCHAR(100)
);
 
-- 为序列赋予所有权,这个可以省略，因为创建sequence的时候已经指定了
ALTER SEQUENCE user_id_seq OWNED BY user.id;

注意：sequence只是自增，主键还是要使用PRIMARY KEY关键字。

语法糖不标准，但是简洁啊

手动创建sequence看起来就非常麻烦，所以serial该用还得用，反正最后还是转换了。

不信，可以看：

CREATE TABLE seq_serial (
    id serial NOT NULL PRIMARY KEY,
    name VARCHAR(100),
    age smallint
);

转换之后：

CREATE TABLE IF NOT EXISTS public.seq_serial
(
    id integer NOT NULL DEFAULT nextval('seq_serial_id_seq'::regclass),
    name character varying(100) COLLATE pg_catalog."default",
    age smallint,
    CONSTRAINT seq_serial_pkey PRIMARY KEY (id)
)

serial的问题

serial做自增主键有一个问题，就是如果显式的指定了某个自增值之后，肯定会冲突。

插入2个，没有显式指定值，没有问题：

INSERT INTO seq_serial(name, age) VALUES ('allen', 22);
INSERT INTO seq_serial(name, age) VALUES ('bob', 99);

显式指定id值为3，然后再不指定id就会冲突：

INSERT INTO seq_serial(id,name, age) VALUES (3,'alice', 30);

-- ERROR:  Key (id)=(3) already exists.duplicate key value violates unique constraint "seq_serial_pkey" 
-- ERROR:  duplicate key value violates unique constraint "seq_serial_pkey"
-- SQL state: 23505
-- Detail: Key (id)=(3) already exists.
INSERT INTO seq_serial(name, age) VALUES ('albert', 25);

因为指定id值的时候，关联的sequence并没有更新。

所以使用serial的时候，一定不要在显式的指定自增主键的值了。

和serial有相同问题的还有：GENERATED BY DEFAULT AS IDENTITY，本质也是sequence。

CREATE TABLE user_default (
 id bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
 name varchar(30),
 age smallint
);

转换之后：

CREATE TABLE IF NOT EXISTS public.user_default
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(30) COLLATE pg_catalog."default",
    age smallint,
    CONSTRAINT user_default_pkey PRIMARY KEY (id)
)

这个没有看到nextval，其实还是sequence，只是变成隐式的了：

更优雅的自增主键

我们前面说了serial的问题，有显式指定值，从而主键冲突的情况，如何避免呢？

可以使用：GENERATED ALWAYS AS IDENTITY

CREATE TABLE user_always (
 id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
 name varchar(30),
 age smallint
);

INSERT INTO user_always(name, age) VALUES ('allen', 22);
INSERT INTO user_always(name, age) VALUES ('bob', 99);

不能显式指定值：

-- ERROR:  Column "id" is an identity column defined as GENERATED ALWAYS.cannot insert a non-DEFAULT value into column "id" 

-- ERROR:  cannot insert a non-DEFAULT value into column "id"
-- SQL state: 428C9
-- Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
-- Hint: Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO user_always(id,name, age) VALUES (3,'alice', 30);

我们可以看到如果显式指定值，就会提示我们，不能插入非默认值。

如果一定要显式指定，怎么操作，使用DEFAULT参数自动生成，不能使用自定义值。

INSERT INTO userb(id,name, age) VALUES (DEFAULT,'alice', 30);

无符号整型

postgresql没有无符号类型，可以通过check检查，虽然不能再数据范围上改变，但是，可以约束字段。

CREATE TABLE products (
    id int not null primary key generated always as identity,
    name varchar(50),
    price numeric CHECK (price > 0)
);

-- ERROR:  Failing row contains (1, 羽绒服, -1).new row for relation "products" violates check constraint "products_price_check" 

-- ERROR:  new row for relation "products" violates check constraint "products_price_check"
-- SQL state: 23514
-- Detail: Failing row contains (1, 羽绒服, -1).
insert into products(name,price) values('羽绒服',-1);

字符类型

字符类型基本说明

类型	说明
char(n)	固定长度，最多10485760字节(1GB)，默认长度1
bpchar(n)	等价于char(n)
varchar(n)	可变长度长度，最多10485760字节(1GB)
character(n)	等价于char(n)
character varying(n)	等价于varchar(n)
text	无长度限制

推荐使用char、varchar，这个是SQL的标准类型，看起来也更简洁一些。

char是固定长度，使用空格填充，所以也叫bpchar(blank pad char)

对于一些固定长度的字段非常合适，例如md5、手机号码等。

-- true char比较的时候只会比较值，统计长度时、转换为其他类型时候也会删除填充的空格
select 'aa'::char(20)='aa'::char(10) as r1,
length('aa'::char(10)) as r2,
,length('aa'::char(10)::varchar(3)) as r3;

字符编码与排序

通常我们是不会关系编码和排序相关信息，因为使用默认的设置就可以。

但是，我们最好了解一下，避免碰到问题两眼一抹黑。

在PostgreSQL中有3个概念比较重要：

1. encoding:这个好理解，就是字符编码
2. collate：字符串排序规则
3. ctype：字符分类，定义什么是字符，大小写是否相等

PostgreSQL中可以在创建表的时候和做比较操作的时候指定collate：

CREATE TABLE user_collate (
    a text COLLATE "my_collation",
    b text COLLATE "my_collation"
);
SELECT a < ('what 王德发' COLLATE "my_collation") FROM user;

注意：和MySQL不同，上面collate不是单独collate，而是指collation的name，collation是包含collate和ctype。

可以通过下面语句创建。

CREATE COLLATION collation_name (
    LC_COLLATE = 'zh_CN.UTF-8',
    LC_CTYPE = 'zh_CN.UTF-8'
);

创建完成，可以通过下面的语句查看：

SELECT * from pg_collation;

如果我们没有指定，默认使用的是default，就是创建数据库上指定的值，如：

CREATE DATABASE db_name WITH ENCODING 'UTF8' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;

-- 修改
update pg_database set datcollate='en_US.utf8',datctype='en_US.utf8' where datname='db_name';

CREATE TABLE IF NOT EXISTS basic_file_info
(
    fid bigint NOT NULL,
    report_type numeric(3,0),
    business_type character varying(255) COLLATE pg_catalog."default",
    area character varying(50) COLLATE pg_catalog."default",
    file_name character varying(300) COLLATE pg_catalog."default",
    CONSTRAINT basic_file_info_pkey PRIMARY KEY (fid)
)

可以通过下面语句来查看：

SELECT * from pg_database;

MySQL的collate就包含了排序和是否忽略大小写相关的内容：

CREATE TABLE user (
  id int NOT NULL,
  name varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_general_ci COMMENT='user';

测试不同collation对字段排序的影响：

CREATE COLLATION zhutf8 (
    LC_COLLATE = 'zh_CN.UTF-8',
    LC_CTYPE = 'zh_CN.UTF-8'
);

CREATE COLLATION enuft8 (
    LC_COLLATE = 'en_US.UTF-8',
    LC_CTYPE = 'en_US.UTF-8'
);

CREATE TABLE user_collate (
    id serial not null primary key,
    a text COLLATE "zhutf8",
    b text COLLATE "enuft8"
);

insert into user_collate(a,b) values
('a','a'),
('A','A'),
('b','b'),
('B','B'),
('bca','bca'),
('一二三','一二三'),
('一二三b','一二三b'),
('王德发','王德发');

select * from user_collate order by a;
select * from user_collate order by b;

我们可以通过COLLATION来创建一些特殊的比较规则，如：

1. 让大写字母排前面(通常默认是使用ascii码排序，小写字母会排前面)
2. 忽略大小写
3. 忽略数字和标点符号

-- 忽略口音和大小写
CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
SELECT 'Å' = 'A' COLLATE ignore_accent_case; -- true
SELECT 'z' = 'Z' COLLATE ignore_accent_case; -- true

-- 大写字母排前面
CREATE COLLATION upper_first (provider = icu, locale = 'und-u-kf-upper');
SELECT 'B' < 'b' COLLATE upper_first; -- true

-- 数字当数字比较、忽略标点符号
CREATE COLLATION num_ignore_punct (provider = icu, deterministic = false, locale = 'und-u-ka-shifted-kn');
SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct; -- true
SELECT 'w;x*y-z' = 'wxyz' COLLATE num_ignore_punct; -- true

SELECT 'id-45' < 'id-123' COLLATE num_ignore_punct as r1,
'id-45' < 'id-123' as r2,
'45' < '123' COLLATE num_ignore_punct as r3,
'45' < '123' as r4;

前面几个例子，可以参考官方文档：ICU-CUSTOM-COLLATIONS

关于字符集、比较和本地化的更多内容，也可以参考：

collation

字符集支持

本地化-locale涉及日期、时间、数字、货币等

时间日期时间戳

基本类型说明

PostgreSQL有非常丰富的时间相关类型。

类型	说明
timestamp(n) without time zone	不带时区的时间戳,8字节
timestamp(n) with time zone	带时区的时间戳,8字节
date	日期,4字节
time(n) without time zone	不带时区的时间,8字节
time(n) with time zone	带时区的时间,12字节
interval	时间间隔,16字节

上面除了date的精度是1天，其他类型的最大精度都是微秒(1/1000000秒)

interval

interval支持ISO8601格式，P后面表示日期，T后面表示日期。

interval类型可以使用Java的Duration来存储。

select '2 years 15 months 100 weeks 99 hours 123456789 milliseconds'::interval as r1,
'P1Y2M3DT4H5M6S'::interval as r2;

interval最常用来做计算而不是存储，例如：

select now(),now() - interval '1 day' as subday,
now() + interval '1 week' as addweek,now() - interval '1 month' as submonth,now()+interval '1 year' as addyear;
 
--加1年1月1天1时1分1秒
select now() + interval '1 year 1 month 1 day 1 hour 1 min 1 sec';

关于时间的计算和函数，可以参数：PostgreSQL常用时间函数与时间计算提取示例说明

时区

查看设置时区：

show timezone;

SET TIMEZONE ='Australia/Sydney';

时区转换：

SELECT localtimestamp as lt,current_timestamp as ct,
timezone('Australia/Sydney',current_timestamp) as sydney_time,
timezone('America/New_York',current_timestamp) as newyork_time;

timestamptz

timestamp(n) with time zone可以简写为timestamptz，这个类型可以通过下面的语句查询：

SELECT typname, typlen FROM pg_type WHERE typname like 'timestamp%';

CREATE TABLE ts_tz_n (
    id serial not null primary key,
    ts TIMESTAMPTZ default current_timestamp, 
    ts0 TIMESTAMPTZ(0) default current_timestamp,
    ts1 TIMESTAMPTZ(1) default current_timestamp,
    ts2 TIMESTAMPTZ(2) default current_timestamp,
    ts3 TIMESTAMPTZ(3) default current_timestamp,
    ts4 TIMESTAMPTZ(4) default current_timestamp,
    ts5 TIMESTAMPTZ(5) default current_timestamp,
    ts6 TIMESTAMPTZ(6) default current_timestamp,
    ts7 TIMESTAMPTZ(7) default current_timestamp
);

timestamptz最大精度是6位，到微秒，所以超过6位会提示如下的警告，并自动修正为6位。

WARNING: TIMESTAMP(7) WITH TIME ZONE precision reduced to maximum allowed, 6

如果没有指定精度，那么默认也是6位精度，到微秒。

timestamptz可以使用Java的OffsetDateTime来存储。

timestamp

timestamp和timestamptz的区别在于：timestamp不带时区，可以看做是MySQL的datetime类型。

CREATE TABLE ts_n (
    id serial not null primary key,
    ts TIMESTAMP default current_timestamp, 
    ts0 TIMESTAMP(0) default current_timestamp,
    ts1 TIMESTAMP(1) default current_timestamp,
    ts2 TIMESTAMP(2) default current_timestamp,
    ts3 TIMESTAMP(3) default current_timestamp,
    ts4 TIMESTAMP(4) default current_timestamp,
    ts5 TIMESTAMP(5) default current_timestamp,
    ts6 TIMESTAMP(6) default current_timestamp,
    ts7 TIMESTAMP(7) default current_timestamp
);

timestamp可以使用Java的Timestamp来存储。

timestamp和timestamptz怎么选？

没有国际化的需求无脑选timestamp就对了。

有国际化的也可以用timestamp，把转换操作放在应用层就可以。

时间戳的疑问

有朋友可能会有疑问，我平时看到的时间戳就是1732523307、或者1732523307672这样的一个数字，他为什么会有时区呢？

我们先来看一下时间戳的定义：

时间戳是指格林威治时间1970年01月01日00时00分00秒起至现在的总秒数

所以，我们经常看到的类似于1732523307这样的10位数字，的确是时间戳，这个是unix的时间戳。

实际在数据库和编程语言中，还有一个epoch概念，toEpochSecond就是10位秒时间戳，toEpochMilli就是13位毫秒时间戳。

在PostgreSQL中我们可以通过extract来获取:

-- 1732523307.672621
SELECT extract(epoch from now()) as r1;

我们可以看到PostgreSQL非常严谨，时间戳定义为秒，它的精度是微秒，它就用小数来表示。

根据时间戳的定义，我们知道不同时区同一时间的时间戳相同，我再此刻生成一个时间戳，不管是在北京生成的，还是在纽约生成的，都是相同的数字。

那为什么timestamptz还有时区，这不是很奇怪么？

timestamptz只是表示时间戳，它有自己的结构，转换为epoch还是等于10位的整数时间戳。

带时区，主要是为了转换显示为本地时间，因为我们数据库查看的时候，通常不会查看一个整数，而是本地化之后的时间。

这样如果有国际化的需求，就能一眼看出其中的时间差别。

timestamp与整型时间戳互转

最高精度到微秒：

select extract(epoch from now()) as r,
floor(extract(epoch from now())) as r1,
floor(extract(epoch from now())*1000) as r2,
extract(epoch from now())*1000000 as r3;

to_timestamp接收的参数是10位秒，获取到的是一个带时区的时间戳timestamptz：

select to_timestamp(1732610870057034 / 1000000) as r1,
to_timestamp(1732610870057 / 1000) as r2,
to_timestamp(1732610870) as r3;

有朋友肯定在想，精确到微秒，那用微秒级时间戳来做自增主键，那不得能支持10w并发(秒后6位10万级)。

理论上是这样，但实际上可能有点差距，可以看一下下面的SQL：

do $$
declare
	i int;
begin
	for i in 1..100000000 loop
		if i % 10000000 = 0 then
			raise notice '%',extract(epoch from current_timestamp);
		end if;
	end loop;
end;
$$

执行时间肯定超过微秒，但输出时间并没有变化：

不太熟悉上面存储过程的朋友可以看一下：PostgreSQL存储过程

bit

类型	说明
bit(n)	固定长度,默认长度1
varbit(n)	可变长度位
bit varying(n)	可变长度位,等价于varbit(n)

注意：bit的n是精确值，就是设置n为3，那么必须插入3位值，不能少也不能多，不像char少了会用空格填充。

CREATE TABLE table_bit(col_bit bit(3), col_varbit varbit(5));

-- B表示这是一个二进制串
INSERT INTO table_bit VALUES (B'101', B'00');

-- ERROR:  bit string length 2 does not match type bit(3) 
-- SQL state: 22026
INSERT INTO table_bit VALUES (B'10', B'101');

-- 可以强转为3位，在尾部填充0
INSERT INTO table_bit VALUES (B'10'::bit(3), B'101');

bit是2进制位，一个比较典型的应用场景是开关。

比如，我有一条数据的校验可能有20个校验类型，每个校验类型都有1个开关，怎么设计呢？

当然，可以设置20个字段来分别表示，但是，不够优雅。

我们可以用一个bit(20)的字段来表示，1表示开启，0表示关闭，这样可以通过位运算，可以非常方便知道哪些校验开启了，哪些校验关闭了。

CREATE TABLE check_bit(id serial not null primary key,switch bit(20));

INSERT INTO check_bit(switch) VALUES (B'00000000000000000101');
INSERT INTO check_bit(switch) VALUES (B'00000000000000000001');
INSERT INTO check_bit(switch) VALUES (B'00000000000000000100');

这种设计也可以查询开启指定校验的数据有哪些。

通过位运算的方式：

例如，最后1位表示数据正确性校验，现在想要知道哪些数据开启了数据正确性校验，就可以通过下面的方式查询：

select * from check_bit where (switch & B'00000000000000000001') = B'00000000000000000001';

这种查询方式，会在数据库上做大量的运算，所以，这种查询最好确保有其他的条件能先过滤掉大部分数据。

bytea(二进制数据)

输入输出

一提到二进制数据，很多朋友就想到文件、图片之类的资源。

很多设计也这么做，例如工作流引擎Camunda，就把流程图资源保存到数据库。

通常不建议把文件之类的资源放在数据库中，Camunda这样设计是因为它是框架，为了管理避免数据丢失了。

bytea按字节存储，PostgreSQL接受2中输入方式：

1. hex:16进制
2. escape：转义字符

输出方式也支持上面2种方式，可以配置：

-- 输出为16进制
SET bytea_output = 'hex'；
-- 输出为转义字符
SET bytea_output = 'escape';

escape转义是\八进制(进制对应关系可以参考后面的ASCII表)：

Java对应类型处理

create table bytea_table(
    id serial not null primary key,
    bin_data bytea
);

插入：

@Test
void insert() throws SQLException {
    String url = "jdbc:postgresql://127.0.0.1:5432/postgres";
    try (Connection connection = DriverManager.getConnection(url, "root", "")) {
        assert connection != null;
        String sql = "INSERT INTO bytea_table(bin_data) VALUES (?)";

        PreparedStatement ps = connection.prepareStatement(sql);
        byte[] data = "hello world".getBytes(StandardCharsets.UTF_8);
        ps.setBytes(1, data);
        ps.executeUpdate();
        ps.close();
    }
}

查看：

@Test
void query() throws SQLException {
    String url = "jdbc:postgresql://127.0.0.1:5432/postgres";
    try (Connection connection = DriverManager.getConnection(url, "root", "")) {
        assert connection != null;
        String sql = "select bin_data from bytea_table";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            System.out.println(new String(rs.getBytes(1)));
        }
    }
}

网络地址

PostgreSQL提供了处理ip和mac地址的类型，可以非常方便计算。

可能我们很少用这些ip来参与计算，但是可以作为小工具来用，还是非常方便。

例如，计算子网掩码、网络地址等。

类型	说明
cidr	7字节(ipv4)、19字节(ipv6)
inet	7字节(ipv4)、19字节(ipv6)
macaddr	6字节，MAC地址
macaddr8	8字节，MAC地址

CREATE TABLE ip_table (
    id serial not null PRIMARY KEY,
    ip_net inet,
    ip_cidr cidr
);

INSERT INTO ip_table (ip_net,ip_cidr) VALUES ('192.168.0.1','192.168.0.1');
INSERT INTO ip_table (ip_net,ip_cidr) VALUES ('192.168.0.0/24','192.168.0.0/24');
INSERT INTO ip_table (ip_cidr) VALUES ('128.1.2');
INSERT INTO ip_table (ip_cidr) VALUES ('128.1');
INSERT INTO ip_table (ip_cidr) VALUES ('128');
-- inet类型必须是完整的ip或者网络
-- INSERT INTO ip_table (ip_net) VALUES ('128');

SELECT * FROM ip_table;

select broadcast('192.168.1.5/24') as 广播地址,
host('192.168.1.5/24') as 主机地址,
hostmask('192.168.23.20/30') as 主机掩码,
masklen('192.168.1.5/24') as 掩码长度,
netmask('192.168.1.5/24') as 子网掩码,
network('192.168.1.5/24') as 网络地址,
family(inet '::1') ip4或ip6,
inet_same_family(inet '192.168.1.5/24', inet '::1') as 是否是相同网络族,
inet_merge('192.168.1.5/24', '192.168.2.5/24') as 包含两个给定网络的最小网络;

-- 192.168.1.7这个ip是否属于192.168.1/24这个网络
select inet '192.168.1.7' << inet '192.168.1/24' as r;

-- 192.168.1/24网络是否包含或者等于192.168.1/25网络
select inet '192.168.1/25' <<= inet '192.168.1/24' as r;

select ~ inet '192.168.1.6' as op_异或,
inet '192.168.1.6' & inet '0.0.0.255' as op_与,
inet '192.168.1.6' | inet '0.0.0.255' as op_或;

很多时候我们查看的时候只知道ip和网络掩码，并不知道网络长度，怎么计算网络地址呢？

network函数显然不行了，我们可以自己手动计算(ip & 子网掩码)：

select network('10.185.33.8') as r, 
inet '255.255.252.0' & inet '10.185.33.8' as r2;

更多可以参考官方文档:网络地址与函数

枚举

枚举有用吗？

当然有用，例如一个订单状态，可能好多地方都会使用，我们如果用枚举约束，就比较出现未知类型。

但是，枚举最大的问题在于，它是我们自定义的类型，很多框架是不支持，都需要我们自己单独处理。

所以，需要考虑迁移维护和兼容问题。

基本操作(创建、修改、添加)

-- 创建枚举
CREATE TYPE cup_enum as ENUM ('中杯', '大杯', '特大杯', '超大杯');

-- 重命名枚举
ALTER TYPE cup_enum RENAME TO cup_enum_all;

-- 添加枚举值
ALTER TYPE cup_enum ADD VALUE '超超大杯';

-- 指定添加位置
ALTER TYPE cup_enum ADD VALUE '超超大杯' BEFORE '超大杯';
ALTER TYPE cup_enum ADD VALUE '超超大杯' AFTER '超大杯';

-- 删除枚举
DROP TYPE cup_enum;

查看枚举(第1个、最后1个、枚举范围)

-- 查看第1个最后1个
select enum_first(null::cup_enum),
enum_first('超大杯'::cup_enum),
enum_last(null::cup_enum),
enum_last('大杯'::cup_enum);

-- 查看枚举值
SELECT enum_range(null::cup_enum) as r1,
enum_range(null::cup_enum) as r2,
enum_range('大杯'::cup_enum,'超大杯'::cup_enum) as r3;

SELECT UNNEST(enum_range(null::cup_enum)) as r1;

插入

CREATE TABLE enum_table (
    id serial not null primary key,
    cup cup_enum
);
INSERT INTO enum_table(cup) VALUES ('大杯');
INSERT INTO enum_table(cup) VALUES ('特大杯');
INSERT INTO enum_table(cup) VALUES ('超大杯');

不能插入枚举中不存在的值：

-- ERROR:  invalid input value for enum cup_enum: "无敌大杯"
-- LINE 1: INSERT INTO enum_table(cup) VALUES ('无敌大杯');
-- SQL state: 22P02
-- Character: 37
INSERT INTO enum_table(cup) VALUES ('无敌大杯');

查询

select min(cup),max(cup) from enum_table;

SELECT * FROM enum_table WHERE cup = '特大杯';

查看枚举信息

select * from pg_enum;

-- Psql中可以使用
\dT+ enum_name

枚举Java类型处理

在pgAdmin、psql中我们插入枚举值用字符串没有问题。

但是在jdbc中我们插入枚举值，直接用string有问题。

@Test
void insert() throws SQLException {
    String url = "jdbc:postgresql://127.0.0.1:5432/postgres";
    Connection connection = DriverManager.getConnection(url,"postgres","");
    assert connection != null;
    String sql = "INSERT INTO enum_table(cup) VALUES (?)";

    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setString(1,"特大杯");
    ps.executeUpdate();
    ps.close();
    connection.close();
}

大概会报下面的这个错误：

org.postgresql.util.PSQLException: ERROR: column "cup" is of type cup_enum but expression is of type character varying
  建议：You will need to rewrite or cast the expression.
  位置：37

我们需要使用setObject，类型使用java.sql.Types.OTHER

@Test
void insert() throws SQLException {
    String url = "jdbc:postgresql://127.0.0.1:5432/postgres";
    Connection connection = DriverManager.getConnection(url,"postgres","");
    assert connection != null;
    String sql = "INSERT INTO enum_table(cup) VALUES (?)";

    PreparedStatement ps = connection.prepareStatement(sql);
    ps.setObject(1,"特大杯", Types.OTHER);
    ps.executeUpdate();
    ps.close();
    connection.close();
}

PostgreSQL的一些表

表名	说明
pg_tables	可以查看数据库中的表
pg_type	可以查看PostgreSQL的数据类型
pg_database	可以查看PostgreSQL的所有数据库
pg_namespace	可以查看PostgreSQL的所有命名空间，命名空间是对表、索引函数等的抽象
pg_tablespace	可以查看PostgreSQL的所有表空间，表空间是对磁盘数据的抽象，一个表空间可以有多个磁盘位置，\db
information_schema.tables	类似于MySQL的information_schema.tables
information_schema.columns	类似于MySQL的information_schema.columns
pg_enum	枚举信息表

select * from pg_type;

select * from pg_tables;

表空间再多说2句：

PostgreSQL默认有2个表空间：

1. pg_default：创建数据库默认表空间，对应的目录PGDATA/base
2. pg_global：共享系统表如pg_database、pg_tables等，对应的目录PGDATA/global

SELECT d.oid,d.datname,t.oid,t.spcname from pg_database d join pg_tablespace t on d.dattablespace=t.oid;

通常我们使用默认表空间就可以了，如果有需要可以下吗方式创建

-- 创建表空间
CREATE TABLESPACE appdatas LOCATION 'F:\PostgreSQL17\data\appdatas';
-- 给用户赋权
GRANT CREATE ON TABLESPACE appdatas TO user_name;

附录-ASCII

Bin (二进制)	Oct (八进制)	Dec (十进制)	Hex (十六进制)	缩写/字符	解释
0000 0000	00	0	0x00	NUL(null)	空字符
0000 0001	01	1	0x01	SOH(start of headline)	标题开始
0000 0010	02	2	0x02	STX (start of text)	正文开始
0000 0011	03	3	0x03	ETX (end of text)	正文结束
0000 0100	04	4	0x04	EOT (end of transmission)	传输结束
0000 0101	05	5	0x05	ENQ (enquiry)	请求
0000 0110	06	6	0x06	ACK (acknowledge)	收到通知
0000 0111	07	7	0x07	BEL (bell)	响铃
0000 1000	010	8	0x08	BS (backspace)	退格
0000 1001	011	9	0x09	HT (horizontal tab)	水平制表符
0000 1010	012	10	0x0A	LF (NL line feed, new line)	换行键
0000 1011	013	11	0x0B	VT (vertical tab)	垂直制表符
0000 1100	014	12	0x0C	FF (NP form feed, new page)	换页键
0000 1101	015	13	0x0D	CR (carriage return)	回车键
0000 1110	016	14	0x0E	SO (shift out)	不用切换
0000 1111	017	15	0x0F	SI (shift in)	启用切换
0001 0000	020	16	0x10	DLE (data link escape)	数据链路转义
0001 0001	021	17	0x11	DC1 (device control 1)	设备控制1
0001 0010	022	18	0x12	DC2 (device control 2)	设备控制2
0001 0011	023	19	0x13	DC3 (device control 3)	设备控制3
0001 0100	024	20	0x14	DC4 (device control 4)	设备控制4
0001 0101	025	21	0x15	NAK (negative acknowledge)	拒绝接收
0001 0110	026	22	0x16	SYN (synchronous idle)	同步空闲
0001 0111	027	23	0x17	ETB (end of trans. block)	结束传输块
0001 1000	030	24	0x18	CAN (cancel)	取消
0001 1001	031	25	0x19	EM (end of medium)	媒介结束
0001 1010	032	26	0x1A	SUB (substitute)	代替
0001 1011	033	27	0x1B	ESC (escape)	换码(溢出)
0001 1100	034	28	0x1C	FS (file separator)	文件分隔符
0001 1101	035	29	0x1D	GS (group separator)	分组符
0001 1110	036	30	0x1E	RS (record separator)	记录分隔符
0001 1111	037	31	0x1F	US (unit separator)	单元分隔符
0010 0000	040	32	0x20	(space)	空格
0010 0001	041	33	0x21	!	叹号
0010 0010	042	34	0x22	"	双引号
0010 0011	043	35	0x23	#	井号
0010 0100	044	36	0x24	$	美元符
0010 0101	045	37	0x25	%	百分号
0010 0110	046	38	0x26	&	和号
0010 0111	047	39	0x27	’	单引号
0010 1000	050	40	0x28	(	开括号
0010 1001	051	41	0x29	)	闭括号
0010 1010	052	42	0x2A	*	星号
0010 1011	053	43	0x2B	+	加号
0010 1100	054	44	0x2C	,	逗号
0010 1101	055	45	0x2D	-	减号/破折号
0010 1110	056	46	0x2E	.	句号
0010 1111	057	47	0x2F	/	斜杠
0011 0000	060	48	0x30	0	字符0
0011 0001	061	49	0x31	1	字符1
0011 0010	062	50	0x32	2	字符2
0011 0011	063	51	0x33	3	字符3
0011 0100	064	52	0x34	4	字符4
0011 0101	065	53	0x35	5	字符5
0011 0110	066	54	0x36	6	字符6
0011 0111	067	55	0x37	7	字符7
0011 1000	070	56	0x38	8	字符8
0011 1001	071	57	0x39	9	字符9
0011 1010	072	58	0x3A	:	冒号
0011 1011	073	59	0x3B	;	分号
0011 1100	074	60	0x3C	<	小于
0011 1101	075	61	0x3D	=	等号
0011 1110	076	62	0x3E	>	大于
0011 1111	077	63	0x3F	?	问号
0100 0000	0100	64	0x40	@	电子邮件符号
0100 0001	0101	65	0x41	A	大写字母A
0100 0010	0102	66	0x42	B	大写字母B
0100 0011	0103	67	0x43	C	大写字母C
0100 0100	0104	68	0x44	D	大写字母D
0100 0101	0105	69	0x45	E	大写字母E
0100 0110	0106	70	0x46	F	大写字母F
0100 0111	0107	71	0x47	G	大写字母G
0100 1000	0110	72	0x48	H	大写字母H
0100 1001	0111	73	0x49	I	大写字母I
01001010	0112	74	0x4A	J	大写字母J
0100 1011	0113	75	0x4B	K	大写字母K
0100 1100	0114	76	0x4C	L	大写字母L
0100 1101	0115	77	0x4D	M	大写字母M
0100 1110	0116	78	0x4E	N	大写字母N
0100 1111	0117	79	0x4F	O	大写字母O
0101 0000	0120	80	0x50	P	大写字母P
0101 0001	0121	81	0x51	Q	大写字母Q
0101 0010	0122	82	0x52	R	大写字母R
0101 0011	0123	83	0x53	S	大写字母S
0101 0100	0124	84	0x54	T	大写字母T
0101 0101	0125	85	0x55	U	大写字母U
0101 0110	0126	86	0x56	V	大写字母V
0101 0111	0127	87	0x57	W	大写字母W
0101 1000	0130	88	0x58	X	大写字母X
0101 1001	0131	89	0x59	Y	大写字母Y
0101 1010	0132	90	0x5A	Z	大写字母Z
0101 1011	0133	91	0x5B	[	开方括号
0101 1100	0134	92	0x5C	|反斜杠
0101 1101	0135	93	0x5D	]	闭方括号
0101 1110	0136	94	0x5E	^	脱字符
0101 1111	0137	95	0x5F	_	下划线
0110 0000	0140	96	0x60	`	开单引号
0110 0001	0141	97	0x61	a	小写字母a
0110 0010	0142	98	0x62	b	小写字母b
0110 0011	0143	99	0x63	c	小写字母c
0110 0100	0144	100	0x64	d	小写字母d
0110 0101	0145	101	0x65	e	小写字母e
0110 0110	0146	102	0x66	f	小写字母f
0110 0111	0147	103	0x67	g	小写字母g
0110 1000	0150	104	0x68	h	小写字母h
0110 1001	0151	105	0x69	i	小写字母i
0110 1010	0152	106	0x6A	j	小写字母j
0110 1011	0153	107	0x6B	k	小写字母k
0110 1100	0154	108	0x6C	l	小写字母l
0110 1101	0155	109	0x6D	m	小写字母m
0110 1110	0156	110	0x6E	n	小写字母n
0110 1111	0157	111	0x6F	o	小写字母o
0111 0000	0160	112	0x70	p	小写字母p
0111 0001	0161	113	0x71	q	小写字母q
0111 0010	0162	114	0x72	r	小写字母r
0111 0011	0163	115	0x73	s	小写字母s
0111 0100	0164	116	0x74	t	小写字母t
0111 0101	0165	117	0x75	u	小写字母u
0111 0110	0166	118	0x76	v	小写字母v
0111 0111	0167	119	0x77	w	小写字母w
0111 1000	0170	120	0x78	x	小写字母x
0111 1001	0171	121	0x79	y	小写字母y
0111 1010	0172	122	0x7A	z	小写字母z
0111 1011	0173	123	0x7B	{	开花括号
0111 1100	0174	124	0x7C
0111 1101	0175	125	0x7D	}	闭花括号
0111 1110	0176	126	0x7E	~	波浪号
0111 1111	0177	127	0x7F	DEL (delete)	删除