PostgreSQL：数据类型与运算符
🧑 博主简介：CSDN博客专家，历代文学网（PC端可以访问：https://literature.sinhy.com/#/?__c=1000，移动端可微信小程序搜索“历代文学”）总架构师，
15年工作经验，精通Java编程，高并发设计，Springboot和微服务，熟悉Linux，ESXI虚拟化以及云原生Docker和K8s，热衷于探索科技的边界，并将理论知识转化为实际应用。保持对新技术的好奇心，乐于分享所学，希望通过我的实践经历和见解，启发他人的创新思维。在这里，我希望能与志同道合的朋友交流探讨，共同进步，一起在技术的世界里不断学习成长。
技术合作请加本人wx（注明来自csdn）：foreast_sea
PostgreSQL：数据类型与运算符
引言
在数据库的世界中，数据类型是构建一切的基础。它们不仅决定了数据如何存储，还直接影响了数据的检索效率、计算精度以及系统的整体性能。PostgreSQL作为最先进的开源关系型数据库之一，提供了丰富的数据类型和强大的运算符支持，使其能够应对各种复杂的应用场景。
想象一下，你正在设计一个电商平台，需要存储商品的价格、用户的评论、订单的时间戳以及产品的规格信息。如何选择合适的数据类型来存储这些信息？使用NUMERIC还是REAL来存储价格？用TEXT还是VARCHAR来存储评论？这些问题看似简单，但每一个选择都可能对系统的性能和稳定性产生深远的影响。
PostgreSQL的数据类型系统不仅包括传统的数值、字符串和日期类型，还支持数组、JSON、范围类型等高级数据结构。这些类型与丰富的运算符结合，使得PostgreSQL能够处理从简单的数值计算到复杂的JSON查询等各种任务。
本文将深入探讨PostgreSQL中的各种数据类型及其运算符，从基础的数值类型到高级的JSON类型，从简单的比较运算符到复杂的模式匹配。
1. 数值类型：精确与效率的平衡
1.1 整数类型：INTEGER、SMALLINT、BIGINT
PostgreSQL提供了三种整数类型：SMALLINT（2字节）、INTEGER（4字节）和BIGINT（8字节）。选择哪种类型取决于你需要存储的数值范围。
CREATE TABLE employees (id SERIAL PRIMARY KEY,age SMALLINT CHECK (age BETWEEN 0 AND 120),salary INTEGER CHECK (salary >= 0),company_id BIGINT
);
注意：SERIAL是一种特殊的整数类型，它自动递增并用作主键。实际上，SERIAL是INTEGER的别名，而BIGSERIAL是BIGINT的别名。
1.2 精确数值类型：NUMERIC
NUMERIC类型用于存储任意精度的数字。它非常适合需要高精度计算的场景，如金融应用。
CREATE TABLE financial_transactions (id SERIAL PRIMARY KEY,amount NUMERIC(15, 2) CHECK (amount >= 0)
);
在这个例子中，NUMERIC(15, 2)表示最多15位数字，其中2位是小数部分。
1.3 浮点数类型：REAL和DOUBLE PRECISION
REAL和DOUBLE PRECISION是浮点数类型，分别占用4字节和8字节。它们适合存储不需要精确计算的数值，如科学计算数据。
CREATE TABLE scientific_data (id SERIAL PRIMARY KEY,measurement REAL,precision_value DOUBLE PRECISION
);
注意：浮点数类型可能会引入舍入误差，因此在需要精确计算的场景中应使用NUMERIC。
2. 字符串与二进制类型：文本与字节的存储
2.1 文本类型：TEXT和VARCHAR
TEXT和VARCHAR是PostgreSQL中用于存储文本数据的两种主要类型。TEXT是可变长度的字符串类型，而VARCHAR可以指定最大长度。
CREATE TABLE user_profiles (id SERIAL PRIMARY KEY,username VARCHAR(50) UNIQUE,bio TEXT
);
注意：在PostgreSQL中，TEXT和VARCHAR在性能上没有显著差异，因此通常推荐使用TEXT，除非有明确的长度限制需求。
2.2 二进制类型：BYTEA
BYTEA类型用于存储二进制数据，如图片、音频或加密数据。
CREATE TABLE user_avatars (user_id INTEGER PRIMARY KEY REFERENCES users(id),avatar BYTEA
);
注意：BYTEA类型的数据在存储时会进行转义，因此在处理大量二进制数据时，可能需要考虑使用大对象（Large Object）功能。
3. 日期时间类型：时间的艺术
3.1 日期类型：DATE
DATE类型用于存储日期，不包含时间信息。
CREATE TABLE events (id SERIAL PRIMARY KEY,event_name TEXT,event_date DATE
);
3.2 时间类型：TIME
TIME类型用于存储时间，不包含日期信息。
CREATE TABLE schedules (id SERIAL PRIMARY KEY,event_time TIME
);
3.3 时间戳类型：TIMESTAMP
TIMESTAMP类型用于存储日期和时间。它可以带有时区信息（TIMESTAMP WITH TIME ZONE）或不带时区信息（TIMESTAMP WITHOUT TIME ZONE）。
CREATE TABLE log_entries (id SERIAL PRIMARY KEY,entry_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
注意：在处理跨时区的应用时，推荐使用TIMESTAMP WITH TIME ZONE，以避免时区转换带来的问题。
4. 数组与JSON类型：复杂数据的存储与查询
4.1 数组类型
PostgreSQL支持多维数组，数组的元素可以是任何数据类型。
CREATE TABLE product_tags (product_id INTEGER PRIMARY KEY,tags TEXT[]
);
你可以使用数组运算符来查询数组数据：
SELECT * FROM product_tags WHERE 'sale' = ANY(tags);
4.2 JSON类型
PostgreSQL提供了JSON和JSONB两种JSON数据类型。JSONB是二进制格式的JSON，支持索引和更高效的查询。
CREATE TABLE product_details (product_id INTEGER PRIMARY KEY,details JSONB
);
你可以使用JSON运算符来查询JSON数据：
SELECT * FROM product_details WHERE details @> '{"color": "red"}';
5. 运算符与表达式：数据操作的利器
5.1 比较运算符
PostgreSQL支持标准的比较运算符，如=、<>、>、<、>=、<=。
SELECT * FROM employees WHERE salary > 50000;
5.2 逻辑运算符
逻辑运算符包括AND、OR、NOT。
SELECT * FROM employees WHERE salary > 50000 AND age < 30;
5.3 模式匹配运算符
PostgreSQL提供了强大的模式匹配运算符，如LIKE、ILIKE、SIMILAR TO和正则表达式匹配。
SELECT * FROM employees WHERE username LIKE 'john%';
5.4 数学运算符
PostgreSQL支持各种数学运算符，如+、-、*、/、%。
SELECT salary * 1.1 AS new_salary FROM employees;
5.5 JSON运算符
对于JSONB类型，PostgreSQL提供了专门的运算符，如@>、<@、?、?|、?&。
SELECT * FROM product_details WHERE details ? 'color';
总结
PostgreSQL的数据类型和运算符系统是其强大功能的核心。通过合理选择数据类型和使用适当的运算符，你可以构建高效、可靠的数据库应用。无论是简单的数值计算，还是复杂的JSON查询，PostgreSQL都能提供强大的支持。
参考文献
- PostgreSQL官方文档：https://www.postgresql.org/docs/current/
- “PostgreSQL: Up and Running” by Regina O. Obe and Leo S. Hsu
- “SQL in a Nutshell” by Kevin Kline, Daniel Kline, and Brand Hunt
- “PostgreSQL 9 High Availability” by Shaun M. Thomas
- “Mastering PostgreSQL in Application Development” by Dimitri Fontaine