上一篇【第08篇】psql工具完全指南——被严重低估的数据库管理利器下一篇【第10篇】特殊数据类型深度解析——网络、UUID、XML、JSON与数组选对数据类型数据库的设计就成功了一半。本文全面讲解 PostgreSQL 的基础数据类型用对比表格和实战代码帮你掌握每种类型的特点、范围和最佳使用场景。写在前面你建表的时候是不是经常纠结——“这个字段用 int 还是 bigint”“金额用 float 还是 numeric”“存名字用 char 还是 varchar”选错数据类型可能导致的问题存储空间浪费用了 bigint 却只需要 smallint精度丢失金额用了 float性能下降类型不匹配导致隐式转换数据截断varchar 长度不够PG 支持的数据类型是主流数据库中最丰富的。本文先讲基础类型下一篇再讲 JSON、数组、UUID 等高级类型。一、数值类型1.1 整数类型类型别名存储大小范围说明smallintint22字节-32768 到 32767小范围整数integerint, int44字节-2147483648 到 2147483647最常用bigintint88字节-9223372036854775808 到 9223372036854775807大范围整数CREATETABLEnumeric_demo(idserialPRIMARYKEY,tinysmallint,normalinteger,bigbigint);INSERTINTOnumeric_demo(tiny,normal,big)VALUES(32767,2147483647,9223372036854775807);-- 超出范围会报错-- INSERT INTO numeric_demo (tiny) VALUES (32769);-- ERROR: value 32769 is out of range for type smallint选择建议大多数情况用integerint4它在范围、存储空间、性能之间取得了最佳平衡只有磁盘空间紧张时才考虑smallint只有integer范围不够时才用bigint注意bigint 的运算速度比 integer 慢PG vs MySQL 差异PG 没有 MySQL 的tinyint1字节和mediumint3字节也没有unsigned无符号类型。1.2 精确小数类型类型别名存储大小说明numericdecimal可变精确小数最多1000位精度numeric(p,s)decimal(p,s)可变p位总精度s位小数numeric(p)decimal§可变p位总精度小数位为0money—8字节货币类型固定精度CREATETABLEproducts(idserialPRIMARYKEY,namevarchar(100),pricenumeric(10,2),-- 最多10位数字小数点后2位weightnumeric(8,3)-- 最多8位数字小数点后3位);INSERTINTOproducts(name,price,weight)VALUES(笔记本,5999.99,1.250),(签字笔,3.50,0.015);-- numeric 运算是精确的SELECTprice*3FROMproductsWHEREname签字笔;-- 结果10.50精确值为什么金额必须用 numeric 而不是 float-- float 的精度问题SELECT0.1::float0.2::float;-- 结果可能是0.30000000000000004不精确-- numeric 没有这个问题SELECT0.1::numeric0.2::numeric;-- 结果0.3精确1.3 浮点数类型类型存储大小精度范围realfloat46位小数约 ±1E-37 到 ±1E37double precisionfloat815位小数约 ±1E-307 到 ±1E308-- 浮点数适合科学计算不适合金额CREATETABLEscientific_data(idserialPRIMARYKEY,valuedoubleprecision,labeltext);INSERTINTOscientific_data(value,label)VALUES(3.141592653589793,pi),(2.718281828459045,e),(1.414213562373095,sqrt(2));-- 浮点数的特殊值SELECTInfinity::float,-Infinity::float,NaN::float;1.4 序列类型类型实际类型说明serialinteger sequence自增整数1 到 2147483647bigserialbigint sequence自增大整数smallserialsmallint sequence自增小整数-- serial 的本质CREATETABLEusers(idserialPRIMARYKEY,-- 等价于下面的写法namevarchar(50));-- serial 实际等价于-- CREATE SEQUENCE users_id_seq;-- CREATE TABLE users (-- id integer NOT NULL DEFAULT nextval(users_id_seq) PRIMARY KEY,-- name varchar(50)-- );-- 使用INSERTINTOusers(name)VALUES(张三),(李四);SELECT*FROMusers;-- id | name-- ----------- 1 | 张三-- 2 | 李四1.5 货币类型SELECT12.34::money;-- 12.34 取决于 lc_monetary 设置SELECT99.99::money0.01::money;-- 100.00二、字符类型2.1 类型对比类型别名特点长度限制character(n)char(n)定长不足补空格最多 10485760character varying(n)varchar(n)变长按实际长度存储最多 10485760text—无长度限制最多 1GB2.2 实际差异CREATETABLEchar_demo(c1char(10),c2varchar(10),c3text);INSERTINTOchar_demoVALUES(hello,hello,hello);-- char 类型存储的是 hello 后面补5个空格SELECTlength(c1),length(c2),length(c3)FROMchar_demo;-- length | length | length-- ------------------------- 10 | 5 | 5-- 比较时 char 的空格会被忽略SELECTc1helloFROMchar_demo;-- 返回 true2.3 选择建议场景选择指南 存名字、标题等长度可控的字段 → varchar(n) 存文章内容、JSON等不确定长度的内容 → text 需要定长存储如国家代码、状态码 → char(n)PG vs MySQL 差异在 PG 中varchar(n)和text的性能完全一样PG 内部对它们的处理方式相同。唯一的区别是varchar(n)有长度约束。所以 PG 社区很多人直接用text而不用varchar(n)。三、日期时间类型3.1 类型总览类型存储大小精度范围说明date4字节天4713 BC 到 5874897 AD只有日期time8字节微秒00:00:00 到 24:00:00只有时间time with time zone12字节微秒同上带时区的时间timestamp8字节微秒4713 BC 到 294276 AD日期时间timestamptz8字节微秒同上带时区的日期时间interval16字节微秒-178000000 年到 178000000 年时间间隔3.2 实战代码CREATETABLEevents(idserialPRIMARYKEY,namevarchar(100),event_datedate,event_timetime,created_at timestamptzDEFAULTnow(),-- 推荐durationinterval);INSERTINTOevents(name,event_date,event_time,duration)VALUES(会议,2024-09-15,14:30:00,2 hours),(培训,2024-09-16,09:00:00,1 hour 30 minutes),(部署,2024-09-17,22:00:00,3 hours);-- 当前时间和日期SELECTnow();-- 2024-09-15 10:30:00.12345608SELECTcurrent_date;-- 2024-09-15SELECTcurrent_time;-- 10:30:00.12345608SELECTcurrent_timestamp;-- 同 now()-- 日期时间运算SELECTnow()interval1 day;-- 明天SELECTnow()interval1 month;-- 下个月SELECTnow()-interval7 days;-- 7天前SELECTnow()interval3 hours 30 minutes;-- 3小时30分钟后-- 日期提取函数SELECTEXTRACT(YEARFROMnow());-- 年SELECTEXTRACT(MONTHFROMnow());-- 月SELECTEXTRACT(DAYFROMnow());-- 日SELECTEXTRACT(DOWFROMnow());-- 星期几0周日SELECTEXTRACT(HOURFROMnow());-- 小时SELECTdate_part(year,now());-- 同 EXTRACT(YEAR ...)-- 日期格式化SELECTto_char(now(),YYYY-MM-DD HH24:MI:SS);-- 2024-09-15 10:30:00SELECTto_char(now(),YYYY年MM月DD日);-- 2024年09月15日-- 字符串转日期SELECTto_date(2024-09-15,YYYY-MM-DD);SELECTto_timestamp(2024-09-15 14:30:00,YYYY-MM-DD HH24:MI:SS);3.3 timestamptz vs timestamp强烈推荐使用timestamptz带时区-- timestamp不带时区存什么就是什么-- timestamptz带时区存储时会转为 UTC读取时按会话时区显示-- 创建表CREATETABLEtz_demo(tstimestamp,-- 不带时区tstz timestamptz-- 带时区);INSERTINTOtz_demoVALUES(2024-09-15 14:00:00,2024-09-15 14:00:0008);-- 查看存储值SELECTts,tstzFROMtz_demo;-- ts | tstz-- ----------------------------------------------- 2024-09-15 14:00:00 | 2024-09-15 14:00:0008-- 切换时区后SETTIMEZONEUTC;SELECTts,tstzFROMtz_demo;-- ts没变 | tstz按UTC显示-- 2024-09-15 14:00:00 | 2024-09-15 06:00:0000结论如果你的应用可能面向不同时区的用户一定要用timestamptz。四、布尔类型CREATETABLEflags(idserialPRIMARYKEY,namevarchar(50),is_activebooleanDEFAULTtrue,is_deletedbooleanDEFAULTfalse);INSERTINTOflags(name,is_active)VALUES(功能A,true),(功能B,false),(功能C,yes),-- 也可以用 yes/no(功能D,t),-- 也可以用 t/f(功能E,1),-- 也可以用 1/0(功能F,NULL);-- unknown 状态-- 布尔操作SELECT*FROMflagsWHEREis_activetrue;SELECT*FROMflagsWHEREis_active;-- 直接写也行SELECT*FROMflagsWHEREis_activeISTRUE;SELECT*FROMflagsWHEREis_activeISNOTFALSE;-- 排除 false保留 true 和 NULL五、位串类型PG 支持两种位串类型适用于网络协议分析、位标志等场景-- bit(n)定长位串CREATETABLEpermissions(idserialPRIMARYKEY,namevarchar(50),access_bitsbit(8)-- 8位权限标志);INSERTINTOpermissions(name,access_bits)VALUES(读取,B00000001),(写入,B00000010),(执行,B00000100),(全部,B00000111);-- bit varying(n)变长位串CREATETABLEbinary_data(idserialPRIMARYKEY,databitvarying(64));INSERTINTObinary_data(data)VALUES(B1010101);六、类型选择最佳实践6.1 选择决策表场景推荐类型不推荐类型原因主键IDserial/bigserial—自增方便金额numeric(10,2)float/double精度保证百分比numeric(5,2)float精度保证名字varchar(100)char(100)不浪费空间内容/描述textvarchar(10000)PG 中 text 无长度开销日期datetimestamp只需要日期时时间戳timestamptztimestamp时区安全时长intervalinteger秒更语义化布尔标志booleaninteger类型安全IP地址inetvarchar(15)支持网络操作6.2 存储空间对比类型 存储大小 smallint 2 字节 integer 4 字节 bigint 8 字节 numeric(10,2) ~6 字节可变 real 4 字节 double precision 8 字节 boolean 1 字节 date 4 字节 timestamp 8 字节 timestamptz 8 字节 interval 16 字节 varchar(n)/text 1-4 字节 实际数据长度七、常见错误与踩坑7.1 浮点精度-- ❌ 错误用 float 存金额CREATETABLEorders(amountfloat);INSERTINTOordersVALUES(0.10.2);SELECTamountFROMorders;-- 0.30000000000000004-- ✅ 正确用 numeric 存金额CREATETABLEorders(amountnumeric(10,2));INSERTINTOordersVALUES(0.10.2);SELECTamountFROMorders;-- 0.307.2 时区陷阱-- ❌ 错误用 timestamp 存储带时区的数据-- 一旦时区改变数据无法正确显示-- ✅ 正确用 timestamptz-- 数据始终以 UTC 存储按会话时区显示7.3 char 的空格填充-- char(10) 存 abc实际存储 abc 7个空格-- 比较时会忽略尾部空格但在某些场景下可能造成困惑-- 建议除非有定长需求否则用 varchar 或 text7.4 integer 溢出-- PG 的 integer 范围是 -2147483648 到 2147483647-- 如果需要存更大的数用 bigint-- serial 如果插入超过 21 亿条会报溢出错误-- 大表推荐直接使用 bigserial八、总结本文要点PG 的类型系统是主流数据库中最丰富的金额必须用 numeric绝对不能用 float时间戳推荐用 timestamptz避免时区问题varchar(n) 和 text 性能相同PG 中 text 不需要担心长度serial 底层是 sequence大表直接用 bigserial选对类型既省空间又提性能下篇预告第10篇《特殊数据类型深度解析——网络、UUID、XML、JSON与数组》下一篇我们将进入 PG 类型系统的杀手锏领域——JSONB、数组、UUID、网络地址等特色类型。这些是 PG 区别于其他数据库的核心优势也是现代应用开发中最实用的功能。上一篇【第08篇】psql工具完全指南——被严重低估的数据库管理利器下一篇【第10篇】特殊数据类型深度解析——网络、UUID、XML、JSON与数组