PostgreSQL学习笔记三：数据类型和运算符

数据类型和运算符

在这里插入图片描述

PostgreSQL 支持多种数据类型和运算符，以下是一些常见的数据类型和运算符的概述：

数据类型

基本数据类型
- 整数类型：
  - SMALLINT：2 字节，范围 -32,768 到 32,767。
  - INTEGER：4 字节，范围 -2,147,483,648 到 2,147,483,647。
  - BIGINT：8 字节，范围 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
- 浮点数类型：
  - REAL：4 字节，单精度浮点数。
  - DOUBLE PRECISION：8 字节，双精度浮点数。
  - NUMERIC：可变精度，适合存储精确的小数。
- 字符类型：
  - CHAR(n)：固定长度字符串。
  - VARCHAR(n)：可变长度字符串，最大长度为 n。
  - TEXT：可变长度字符串，没有长度限制。
日期和时间类型
- DATE：日期（年、月、日）。
- TIME：时间（时、分、秒）。
- TIMESTAMP：日期和时间。
- INTERVAL：时间间隔。
布尔类型
- BOOLEAN：真（TRUE）、假（FALSE）或未知（NULL）。
复合类型
- 可以定义复合类型（类似于结构体），例如：
```
CREATE TYPE person AS (name VARCHAR(100),age INT
);
```

数组类型

支持数组，例如：

CREATE TABLE example (id SERIAL PRIMARY KEY,numbers INT[]
);

JSON 和 JSONB
- JSON：存储 JSON 数据。
- JSONB：二进制格式的 JSON，支持更快的查询。
地理空间数据类型
- POINT、LINE、POLYGON 等，适用于地理信息系统（GIS）。

在这里插入图片描述
https://www.postgresql.org/docs/current/datatype.html

运算符

算术运算符
- +：加法
- -：减法
- *：乘法
- /：除法
- %：取模
比较运算符
- =：等于
- != 或 <>：不等于
- >：大于
- <：小于
- >=：大于等于
- <=：小于等于
逻辑运算符
- AND：与
- OR：或
- NOT：非
字符串运算符
- ||：字符串连接
- LIKE：模式匹配
- ILIKE：不区分大小写的模式匹配
数组运算符
- @>：包含
- <@：被包含
- &&：交集
JSON 运算符
- ->：获取 JSON 对象的字段
- ->>：获取 JSON 对象字段的文本值
- #>：获取 JSON 对象的嵌套字段

示例

-- 创建表
CREATE TABLE users (id SERIAL PRIMARY KEY,name VARCHAR(100),age INT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);-- 插入数据
INSERT INTO users (name, age) VALUES ('Alice', 30);-- 查询数据
SELECT * FROM users WHERE age > 25;-- 使用运算符
SELECT name || ' is ' || age || ' years old' AS description FROM users;

如何使用数组类型进行数据操作

在 PostgreSQL 中，数组类型是一种非常强大的数据类型，它允许你将多个值存储在一个数组数据结构中。你可以使用数组类型来存储一维或多维数组，并且可以使用一系列内置函数和运算符来操作这些数组。

以下是一些基本的数组操作示例：

创建数组类型的列

CREATE TABLE products (id SERIAL PRIMARY KEY,names TEXT[],prices NUMERIC[]
);

插入数组数据

INSERT INTO products (names, prices) VALUES
('{Alice,Bob,Charlie}', '{100,150,200}');

查询数组数据

SELECT * FROM products;

更新数组数据

UPDATE products SET prices = prices[1:2] || ARRAY[250] || prices[3:3] WHERE id = 1;

这将更新 prices 数组，将第三个元素更改为 250。

数组内置函数和运算符

数组赋值：

SELECT prices[1] FROM products; -- 获取数组的第一个元素

数组连接：

SELECT prices || ARRAY[300] FROM products; -- 将新元素添加到数组末尾

数组切片：

SELECT prices[1:2] FROM products; -- 获取数组的第1到第2个元素（不包括索引2）

数组长度：

SELECT array_length(prices, 1) FROM products; -- 获取数组的长度

数组重叠：

SELECT prices && ARRAY[100, 200, 300] FROM products; -- 检查数组是否有重叠的元素

数组元素的存在性：

SELECT prices @> ARRAY[100] FROM products; -- 检查数组中是否存在特定的元素

数组位置：

SELECT prices <@ ARRAY[100, 150, 200, 250] FROM products; -- 检查数组是否是另一个数组的子集

数组交集和差集：

SELECT (prices * ARRAY[100, 150, 200]) AS intersection, -- 交集(prices - ARRAY[100, 150, 200]) AS difference -- 差集
FROM products;

多维数组：

CREATE TABLE matrices (id SERIAL PRIMARY KEY,matrix NUMERIC[][]
);
INSERT INTO matrices (matrix) VALUES
(ARRAY[[1,2],[3,4]]);

多维数组操作

对于多维数组，你可以使用更复杂的切片语法来获取元素。

-- 获取二维数组的第一行
SELECT matrix[1:2] FROM matrices;-- 获取二维数组的第一列
SELECT matrix[1:2:1] FROM matrices;

注意事项

数组中的所有元素必须是相同的数据类型。
数组可以使用下标访问，但是下标是从 1 开始的，而不是从 0 开始。
PostgreSQL 中的数组是一维数组的变体，对于多维数组，实际上是存储一系列数组。

这些是 PostgreSQL 中数组类型的基本操作。PostgreSQL 提供了大量的数组处理函数，可以进行更复杂的操作，如数组排序、反转、转换等。你可以在 PostgreSQL 的官方文档中找到完整的数组函数列表。

数组类型的特性和限制

特性：

多维数组：PostgreSQL 允许定义可变长度的多维数组，可以是任何内置或用户定义的基本类型、枚举类型、复合类型等。
灵活性：数组的维度和大小可以在创建表时指定，但 PostgreSQL 不强制执行这些限制，实际行为与未指定长度的数组相同。
数组操作：支持多种数组操作符和函数，如连接、包含、重叠等。

限制：

不强制维度和大小：虽然可以在定义中指定数组的维度和大小，但 PostgreSQL 不会强制这些限制，数组的实际维度和大小取决于插入的数据。
性能考虑：在某些情况下，使用数组可能会导致性能问题，尤其是在需要频繁搜索数组元素时，建议考虑将数组元素拆分到独立的表中。
不支持域类型：目前 PostgreSQL 不支持域类型的数组。

JSON 和 JSONB 的区别

存储格式：
- JSON：以文本形式存储，保留输入的空格、重复键和顺序。每次查询时需要解析，因此性能较低。
- JSONB：以二进制格式存储，解析后删除不必要的空格和重复键，顺序可能不同。JSONB 的查询性能更好，因为它在存储时进行了优化。
索引支持：
- JSON：不支持 GIN 或 GiST 索引，查询性能较差。
- JSONB：支持 GIN 和 GiST 索引，能够显著提高查询性能。
使用场景：
- JSON：适用于不需要频繁查询和操作的场景。
- JSONB：适用于需要高性能和频繁查询的场景，特别是处理大量 JSON 数据时。
性能：
- JSON 在写入时速度较快，但读取速度较慢；JSONB 写入速度稍慢，但读取速度较快。