PostgreSQL初级教程：数据类型与基本转换技巧

发表时间: 2024-02-27 00:04

自从厂子里出来后，数据库的主力就从Oracle变成MySQL了。有一说一哈，贵确实是有贵的道理，不是开源能比的。后面的工作里面基本上就是主MySQL，辅MongoDB、ES等NoSQL。最近想写一点跟技术相关的系列文章过过瘾，先从PG开始更新吧，后面再看写哪个方向的。

随着国内软件本土化的趋势，国产软件现在也慢慢发展起来了，通过查看招聘网站上的要求以及对国产数据库的一些了解，也发现不少产品底层都是基于PG做上去的，PG在流行程度上现在也开始逐渐超过MYSQL，这个产品还是值得研究一下的。

PG的安装和配置（远程连接配置、用户管理等）就不写了。今天主要记录一下PG的数据类型，通过与MySQL对比，先有一个整体的基本印象，接下来简单记录下类型转换的几种用法。

先说一下PG的几个技术文档网站地址：

官网：
https://www.postgresql.org/docs/，最新版本16.2，如果英文还不错，建议通过该文档学习，权威性最高。当然不是说国内做得不好，全看个人喜好

中文网站：
http://postgres.cn/v2/document，这里的版本更新到12

数据类型

先放一张表，稍微翻译一下

数据类型	别名	说明
bigint	int8	signed eight-byte integer 有符号8字节整数
bigserial	serial8	autoincrementing eight-byte integer 自增的8字节整数，这个类似于MySQL的字段设置auto increment
bit [ (n) ]		fixed-length bit string 指定长度的位数组，长度必须为n，不能多也不能少，不写默认长度为1
bit varying [ (n) ]	varbit [ (n) ]	variable-length bit string 可变长度的位数组，最大存储n个位，不写默认不限制
boolean	bool	logical Boolean (true/false) 布尔类型
box		rectangular box on a plane 描述一个长方形，有3种写法： `( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1, y1, x2, y2` 用坐标来描述，分别是左上角和右下角的坐标，通常用于地理位置标记
bytea		binary data (“byte array”) 大容量二进制数据类型，类似于MYSQL的blob
character [ (n) ]	char [ (n) ]	fixed-length character string 固定长度的字符串，长度不够空白填充，填充在后面，比如字段设置为char(5)，输入一个值tt后，存储的值实际为ttxxx，x表示空格
character varying [ (n) ]	varchar [ (n) ]	variable-length character string 变长的字符串，与MYSQL中的varchar(n)类似，最多容纳n个字符，不写与text类型一样效果
cidr		IPv4 or IPv6 network address IPV4或者IPV6地址存储，MYSQL存储还需要开发人员考虑一下数据设计，这里直接提供了数据类型
circle		circle on a plane 与box一样，这是存储一个描述圆的数据
date		calendar date (year, month, day) 日期，只表示年月日
double precision	float8	double precision floating-point number (8 bytes) 双精度浮点数，8个字节大小
inet		IPv4 or IPv6 host address IPV4或者IPV6地址，可以带子网掩码，以IPV4地址示例： 192.168.1.1/24，前面表示IP地址，/后面表示子网掩码，24表示的子网掩码为从高位往低位开头连续为24个1，也就是： 11111111 11111111 11111111 00000000，转成十进制就是255.255.255.0，这个子网掩码熟悉吧，应该好理解
integer	int, int4	signed four-byte integer 4字节有符号整数
interval [ fields ] [ (p) ]		time span 时间差
json		textual JSON data 现在Web API返回值常用的JSON文本格式
jsonb		binary JSON data, decomposed 二进制的JSON串，MongoDB用的这种，性能要比json好
line		infinite line on a plane 表示线条，用法忽略
lseg		line segment on a plane 表示线段，表示方式与线类似，唯一区别是线段有端点，用法忽略
macaddr		MAC (Media Access Control) address MAC地址
macaddr8		MAC (Media Access Control) address (EUI-64 format) EUI-64位格式的MAC地址
money		currency amount 货币数据类型
numeric [ (p, s) ]	decimal [ (p, s) ]	exact numeric of selectable precision 指定整数部分和小数部分的浮点数，可与money做等价转换，不丢精度
path		geometric path on a plane 一系列点的连线
pg_lsn		PostgreSQL Log Sequence Number PG日志位置索引，64位整数，通过该值可去PG的日志文件中查找该字段指定位置的流内容
pg_snapshot		user-level transaction ID snapshot 用户级别的事务ID快照
point		geometric point on a plane 表示点，用法忽略
polygon		closed geometric path on a plane 表示多边形，用法忽略
real	float4	single precision floating-point number (4 bytes) 单精度的浮点数，4字节表示
smallint	int2	signed two-byte integer 有符号的2字节整数
smallserial	serial2	autoincrementing two-byte integer 自增序列，大小为2字节
serial	serial4	autoincrementing four-byte integer 自增序列，大小为4字节
text		variable-length character string 大文本对象
time [ (p) ] [ without time zone ]		time of day (no time zone) 不带时区的日期部分内容
time [ (p) ] with time zone	timetz	time of day, including time zone 带时区的日期部分内容
timestamp [ (p) ] [ without time zone ]		date and time (no time zone) 包含年月日时分秒的格式，不带时区
timestamp [ (p) ] with time zone	timestamptz	date and time, including time zone 包含年月日时分秒的格式，带时区
tsquery		text search query 全文搜索，这个一般用专门的全文搜索数据库，比如ES，这里不再赘述
tsvector		text search document 该字段存储的数据会被进行分词，这个一般用专门的全文搜索数据库，比如ES，这里不再赘述
txid_snapshot		user-level transaction ID snapshot (deprecated; see pg_snapshot) 已过时，由pg_snapshot替代
uuid		universally unique identifier UUID，再熟悉不过啦
xml		XML data XML文本数据

说明：

pg中char、varchar、text3个类型性能没区别
类似box、circle等表示形状的数据类型一般用不到，都直接用类似于ES这种专门的NoSQL来处理，只做了解，不深入研究了
每个数据类型后面要单独进行分析，有的字段还挺复杂，原理也得看一看，方便理解

基本类型转换

单引号与双引号

单引号用于表示字符串，比如插入一个文本test，写值的时候就要写成'test'
双引号里面引用的必须是关键字，避免与PG的关键字冲突，其用法与MYSQL的''类似。举例：某表的字段名叫select，此时查询该字段时必须写成select "select" from XXX。通常要避免出现这种情况，这并不是一个好的工程实践。

类型转换

类型转换有3种写法：

第一种：类型值

select int '123'

第二种：值::类型

select '123'::int

第三种：cast(类型值 as 转换后的类型)

select cast(varchar '123' as int)

前2种写法本质上来说，最终都是调用了第3种方法的实现。

好，今天先写到这，明天继续更新。最近打算做个技术人的人生经历，问下老K看看能不能提供点素材，那么多年的交情，老麻雀应该不会不答应

PostgreSQL初级教程：数据类型与基本转换技巧

数据类型

基本类型转换

热门阅读

推荐阅读