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

数据类型和运算符

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

数据类型

1. 基本数据类型

   • 整数类型：

     • 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：可变长度字符串，没有长度限制。

2. 日期和时间类型

   • DATE：日期（年、月、日）。
   • TIME：时间（时、分、秒）。
   • TIMESTAMP：日期和时间。
   • INTERVAL：时间间隔。

3. 布尔类型

   • BOOLEAN：真（TRUE）、假（FALSE）或未知（NULL）。

4. 复合类型

   • 可以定义复合类型（类似于结构体），例如：

     CREATE TYPE person AS (name VARCHAR(100),age INT
     );
     

5. 数组类型

   • 支持数组，例如：

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

6. JSON 和 JSONB

   • JSON：存储 JSON 数据。
   • JSONB：二进制格式的 JSON，支持更快的查询。

7. 地理空间数据类型

   • POINT、LINE、POLYGON 等，适用于地理信息系统（GIS）。

https://www.postgresql.org/docs/current/datatype.html

运算符

1. 算术运算符

   • +：加法
   • -：减法
   • *：乘法
   • /：除法
   • %：取模

2. 比较运算符

   • =：等于
   • != 或 <>：不等于
   • >：大于
   • <：小于
   • >=：大于等于
   • <=：小于等于

3. 逻辑运算符

   • AND：与
   • OR：或
   • NOT：非

4. 字符串运算符

   • ||：字符串连接
   • LIKE：模式匹配
   • ILIKE：不区分大小写的模式匹配

5. 数组运算符

   • @>：包含
   • <@：被包含
   • &&：交集

6. 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 的官方文档中找到完整的数组函数列表。

数组类型的特性和限制

特性：

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

限制：

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

JSON 和 JSONB 的区别

1. 存储格式：

   • JSON：以文本形式存储，保留输入的空格、重复键和顺序。每次查询时需要解析，因此性能较低。
   • JSONB：以二进制格式存储，解析后删除不必要的空格和重复键，顺序可能不同。JSONB 的查询性能更好，因为它在存储时进行了优化。

2. 索引支持：

   • JSON：不支持 GIN 或 GiST 索引，查询性能较差。
   • JSONB：支持 GIN 和 GiST 索引，能够显著提高查询性能。

3. 使用场景：

   • JSON：适用于不需要频繁查询和操作的场景。
   • JSONB：适用于需要高性能和频繁查询的场景，特别是处理大量 JSON 数据时。

4. 性能：

   • JSON 在写入时速度较快，但读取速度较慢；JSONB 写入速度稍慢，但读取速度较快。

根据具体需求选择合适的数据类型，可以有效提高数据库的性能和灵活性。