简介
PostgreSQL提供了非常丰富的数据类型，我们平常使用最多的基本就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_seqINCREMENT 1START 1MINVALUE 1MAXVALUE 2147483647CACHE 1NO 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_seqINCREMENT 1START 1MINVALUE 1MAXVALUE 2147483647CACHE 1OWNED 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个概念比较重要：
- encoding:这个好理解，就是字符编码
- collate：字符串排序规则
- 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来创建一些特殊的比较规则，如：
- 让大写字母排前面(通常默认是使用ascii码排序，小写字母会排前面)
- 忽略大小写
- 忽略数字和标点符号
-- 忽略口音和大小写
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 $$
declarei int;
beginfor i in 1..100000000 loopif i % 10000000 = 0 thenraise 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中输入方式：
- hex:16进制
- 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个表空间：
- pg_default：创建数据库默认表空间，对应的目录PGDATA/base
- 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) | 删除 |