基础数据类型

本章我们讨论 PostgreSQL 的基本数据类型：布尔型、字符型、数值型和日期时间型。这些类型与其它数据库有着很多不同，让我们一起深入细节进行了解。

布尔类型

和某些数据库使用 TINYINT 模拟布尔类型不同，PostgreSQL 原生支持布尔类型：BOOLEAN。它可以有三个值：TRUE、FALSE 和 NULL。它使用一个字节存储布尔值，同时 BOOLEAN 可以简写为 BOOL。

在标准 SQL中，布尔值可以是 TRUE、FALSE 和 NULL。然而，PostgreSQL 在处理 FALSE 值时非常灵活。下表显示了 PostgreSQL 中 TRUE 和 FALSE 的有效常量值。

• 除了 TRUE 和 FALSE ，其它常量值都要用单引号包裹
• 这些常量值并不区分大小写

• 除了 TRUE 和 FALSE ，其它常量值都要用单引号包裹
• 这些常量值并不区分大小写

示例

示例数据：

CREATE TABLE stock_availability (
   product_id INT PRIMARY KEY,
   available BOOLEAN NOT NULL
);

INSERT INTO stock_availability (product_id, available)
VALUES
	(100, TRUE),
	(200, FALSE),
	(300, 't'),
	(400, '1'),
	(500, 'y'),
	(600, 'yes'),
	(700, 'no'),
	(800, '0');

检查产品的可用性：

SELECT *
FROM stock_availability
WHERE available = 'yes';

/*结果
 product_id | available
------------+-----------
        100 | t
        300 | t
        400 | t
        500 | t
        600 | t
(5 rows)
*/

可以直接使用布尔类型的字段，而不用任何运算符来暗示真实值：

SELECT *
FROM stock_availability
WHERE available;

如果要查找 FALSE 值，则可以将布尔类型的字段与任何有效的布尔常量值进行比较。

以下查询返回不可用的产品：

或者使用 NOT 运算符来检查布尔类型字段的值是否为 FALSE：

SELECT *
FROM stock_availability
WHERE NOT available;

字符类型

PostgreSQL 主要有三种字符类型：CHARACTER(n)、CHARACTER VARYING(n) 和 TEXT，n 是正整数。

其中，CHARACTER(n) 通常简写为 CHAR(n)，CHARACTER VARYING(n)通常简写为 VARCHAR(n)。

• CHAR(n)和VARCHAR(n)：最多存储 n 个字段，如果尝试存储超过 n 个的字符，PostgreSQL 将报错。唯一例外是，如果超出的字符是空格，PostgreSQL 会将空格截断到最大长度(n)并进行存储。
• 如果字符串显式转换为 CHAR(n)或VARCHAR(n)，PostgreSQL 会将字符串截断为 n 个字段，然后再将其插入表中。
• TEXT 数据类型，理论上可以存储无限长度的字符，实际取决于电脑的存储大小。
• 特别注意（尤其是MySQL用户）：VARCHAR（即没有指定长度的 VARCHAR），其行为和 TEXT 一样，在PostgreSQL中，VARCHAR 和 TEXT 的性能是相同的。
• 特别注意：CHAR（即没有指定长度的CHAR），其行为和 CHAR(1) 一样。
• 与其它数据库不同的是，在PostgreSQL 中，三种字符类型之间没有性能差异。

示例

示例数据：

CREATE TABLE character_tests (
	id serial PRIMARY KEY,
	x CHAR (1),
	y VARCHAR (10),
	z TEXT
);

使用下面的 SQL 尝试插入数据时，将报错：

INSERT INTO character_tests (x, y, z)
VALUES('Yes','This is a test for varchar','This is a very long text for the PostgreSQL text column');

/*报错
ERROR:  value too long for type character(1)
*/

整数类型

常用的整数类型有 SMALLINT、INTEGER 和 BIGINT：

• 如果尝试存储超出允许范围的值，PostgreSQL 将报错
• 与 MySQL 不同，PostgreSQL 不提供无符号整数类型。——这是最让人无法理解的事情。

序列

序列是一种特殊的数据库对象，它生成整数序列，通常用于自动编号的主键。它类似于其它数据库中的 AUTO_INCREMENT。

创建新表时，可以通过 SERIAL 来创建序列：

CREATE TABLE table_name(
    id SERIAL
);

将 id 字段的数据类型设置为 SERIAL，PostgreSQL 将执行以下操作：

• 首先，创建一个序列对象，并将序列生成的下一个值设置为该字段的默认值
• 其次，向id 字段添加 NOT NULL 约束，因为序列始终生成一个非空整数
• 第三，将序列的所有者分配给 id 字段，当删除 id 字段或者表时，序列对象也会被 删除。

PostgreSQL 提供了3种序列类型：

示例

示例数据：

CREATE TABLE fruits(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

虽然 SERIAL 会隐式给字段加上 NOT NULL 约束，但它并不会隐式加上主键约束。所以你需要手动通过 PRIMARY KEY 给 id 字段加上主键约束。

在插入记录时，请忽略加了序列的字段：

INSERT INTO fruits(name) 
VALUES('Orange');

或者，使用 DEFAULT 关键字：

INSERT INTO fruits(id,name) 
VALUES(DEFAULT,'Apple');

可以使用 pg_get_serial_sequence()函数，获取序列的名称：

pg_get_serial_sequence('表名','字段名');

-- 比如本例
pg_get_serial_sequence('fruits','id');

将序列名传递给 currval() 函数，可以获取序列生成的最新值：

currval(序列名);

-- 比如本例
SELECT currval(pg_get_serial_sequence('fruits', 'id'));

正如在讨论 INSERT 语句时所说，你可以通过 RETURNING 子句来返回 id 字段的值：

INSERT INTO fruits(name) 
VALUES('Banana')
RETURNING id;

注意，序列生成器操作不是事务安全的。这意味着如果多个并发的数据库连接尝试从序列中获取下一个值，则每个客户端将获得不同的值。如果一个客户端回滚事务，则该客户端的序列号将不会被使用，从而在序列中产生间隙。

浮点类型

浮点类型主要有以下三种：

• float(n)：精度至少为 n，最大为8字节
• real或float8：4字节浮点数
• numeric(p,s)：p位实数，小数点后有 s 位。它是精确的数字。

float/real存储的是近似值，而numeric 可以存储精确性的数字，例如金额。其语法是：

NUMERIC(precision, scale)

• precision：总位数（精度）；scale：小数部分的位数
• 例如：1234.567，它的总位数（精度，precision）是 7，小数位数（scale）为 3
• NUMERIC 类型最多可保存小数点前131072位，小数点后 16383 位。
• NUMERIC的小数位数可以为零或正数：NUMERIC(precision) 表示的就是小数位数为零
• 如果同时省略 precision 和 scale，则可以存储任何精度的值，最高可达上述精度和小数位数的限制。
• 在 PostgreSQL 中， NUMERIC 和 DECIMAL 是同义词
• 如果存储的值能接受近似值，使用 float/real 将比 numeric更节省资源

示例

示例数据 ：

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(5,2)
);

INSERT INTO products (name, price)
VALUES ('Phone',500.215), 
       ('Tablet',500.214);

由于 price 字段的小数位数是2，PostgreSQL 将 500.215 向上舍入为 500.22，并将 500.214 向下舍入为 500.21。

除了保存数字值之外，NUMERIC 还可以保存名为 NaN的特殊值，它表示非数字。

-- 将ID为1的价格修改为 NaN
UPDATE products
SET price = 'NaN'
WHERE id = 1;

• NaN 不等于任何值，包括它本身。NaN = NaN 返回 false
• 在排序时(ORDER BY)，NaN 大于其它具体的数字

日期类型

要存储日期值，可以使用 DATE 类型。它占用4个字节，取值范围在公元前 4713 年 ~ 公元 5874897 年。它的格式是 yyyy-mm-dd，比如：2023-07-31 表示 2023年7月31日。

可以使用 CURRENT_DATE 作为 DATE 字段的默认值。

DROP TABLE IF EXISTS documents;

CREATE TABLE documents (
	document_id serial PRIMARY KEY,
	header_text VARCHAR (255) NOT NULL,
	posting_date DATE NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO documents (header_text)
VALUES('Billing to customer XYZ');

SELECT * FROM documents;

常用日期函数

PostgreSQL 提供了许多日期函数，我们来看一些常用的函数：

获取当前日期

NOW() 函数返回当前的日期和时间，如果仅要获取日期部分，可以使用 :: 将其转换为 DATE 类型：

SELECT NOW()::date;

另一种方法是使用 CURRENT_DATE：

SELECT CURRENT_DATE;

格式化输出日期值

日期值的默认格式是 yyyy-mm-dd ，它很符合国人的习惯。但当你的产品面向老外或者在某些特定场景下，可能还是需要对其进行格式化输出。PostgreSQL 提供了 TO_CHAR() 函数，下例为将日期格式化为 dd/mm/yyyy 格式：

SELECT TO_CHAR(NOW()::DATE, 'dd/mm/yyyy');

下例将日期格式化为 Jun 22, 2016格式：

SELECT TO_CHAR(NOW()::DATE, 'Mon dd, yyyy');

计算两个日期之间的间隔

直接使用减号 - 即可。

 SELECT NOW() - '2023-07-01'::DATE AS diff;
/*结果
          diff
-------------------------
 29 days 14:00:06.100446
*/

计算年龄（年、月、日）

要计算当前日期的年龄（以年、月、日为单位），可以使用 AGE() 函数。

SELECT AGE('2000-12-31'::DATE);
/* 结果
           age
-------------------------
 22 years 6 mons 30 days
 */

如果传递两个参数，将是第一个日期减去第二个日期：

 SELECT AGE('2010-01-01'::DATE, '2000-12-31'::DATE);
 /*结果
      age
---------------
 9 years 1 day
 */

从日期值中提取年、月、日

EXTRACT() 可以从日期中提取年、月、日等部分：

SELECT EXTRACT(YEAR FROM NOW()) AS 年, EXTRACT(MONTH FROM NOW()) AS 月, EXTRACT(DAY FROM NOW()) AS 日;

/*结果
  年  | 月 | 日
------+----+----
 2023 |  7 | 31
 */

时间类型

TIME 类型用于存储时间：

TIME(精度)

精度 是指小数位数，最多可达6位。TIME需要8个字节进行存储，允许的范围是 00:00:00 ~ 24:00:00。

常用格式如下：

示例：

CREATE TABLE shifts (
    id serial PRIMARY KEY,
    shift_name VARCHAR NOT NULL,
    start_at TIME NOT NULL,
    end_at TIME NOT NULL
);  

INSERT INTO shifts(shift_name, start_at, end_at)
VALUES('上午', '08:00:00', '12:00:00'),
      ('下午', '13:00:00', '17:00:00'),
      ('晚上', '18:00:00', '22:00:00');

时区

时间还可以带上时区，数据类型为 TIME WITH TIME ZONE：

字段名 TIME WITH TIME ZONE

常用时间函数

获取当前时间

CURRENT_TIME 的结果中带有时区信息，LOCALTIME 也是获取当前时间，但它获取的是本地时间，所以隐藏了时区信息。

LOCALTIME 也有时区信息，只是在结果中隐藏了

LOCALTIME 也有时区信息，只是在结果中隐藏了

SELECT LOCALTIME;

-- 特定精度
SELECT LOCALTIME(5);

时区转换

语法：

[TIME with time zone] AT TIME ZONE time_zone

举例：

-- 将当前时间转换为中国标准时间（东8区）
 SELECT CURRENT_TIME AT TIME ZONE 'PRC';
 -- 或者
 SELECT CURRENT_TIME AT TIME ZONE 'UTC-8';
 -- 或者
 SELECT CURRENT_TIME AT TIME ZONE 'Asia/Shanghai';
 -- LOCALTIME 同样适用
 SELECT LOCALTIME AT TIME ZONE 'Asia/Shanghai';

从时间值中提取小时、分钟、秒

使用 EXTRACT() 函数，可以从时间值中提取小时、分钟、秒

时间的算术运算

-- 计算间隔
 SELECT CURRENT_TIME - '10:00'::TIME;
 
-- 当前时间添加2小时
SELECT CURRENT_TIME + INTERVAL '2 hours' AS result;

PostgreSQL 使用 TIMESTAMP 来存储日期时间。它提供了两种数据类型：

• TIMESTAMP：没有时区的时间戳。当更改数据库服务器的时区时，存储在数据库中的值不会自动更改。
• TIMESTAMPTZ：带有时区的时间戳。它会感知服务器的时区，当时区发生改变时，存储在数据库中的值会自动更改。
  • PostgreSQL 以 UTC 值存储 TIMESTAMPTZ
  • 当向 TIMESTAMPTZ 字段插入值时，PostgreSQL 会将其转换为 UTC 值，然后进行存储
  • 当从 TIMESTAMPTZ 字段查询值时，PostgreSQL 会将值转换为服务器、用户或当前连接设置的时区的时间值
  • TIMESTAMPTZ 不存储时区数据，永远只存储 UTC 值。它只是会根据时区，将值进行转换。
• TIMESTAMP 和 TIMESTAMPTZ 都是使用8个字节进行存储。

• PostgreSQL 以 UTC 值存储 TIMESTAMPTZ
• 当向 TIMESTAMPTZ 字段插入值时，PostgreSQL 会将其转换为 UTC 值，然后进行存储
• 当从 TIMESTAMPTZ 字段查询值时，PostgreSQL 会将值转换为服务器、用户或当前连接设置的时区的时间值
• TIMESTAMPTZ 不存储时区数据，永远只存储 UTC 值。它只是会根据时区，将值进行转换。

这里的”时间戳“更像其它数据库中的DATETIME，而不是编程语言中的相同名词。

这里的”时间戳“更像其它数据库中的DATETIME，而不是编程语言中的相同名词。

更改、显示当前时区

使用 SET TIMEZONE='时区'可以更改数据库服务器的时区；使用 SHOW TIMEZONE 可以显示服务器当前的时区：

-- 更改时区为中国标准时间
SET TIMEZONE='Asia/Shanghai';

-- 显示当前时区
SHOW TIMEZONE;
/*结果 
   TimeZone
---------------
 Asia/Shanghai
 */

示例

示例数据：

CREATE TABLE timestamp_demo (
    ts TIMESTAMP, 
    tstz TIMESTAMPTZ
);

插入数据：

INSERT INTO timestamp_demo (ts, tstz)
VALUES('2016-06-22 19:10:25-07','2016-06-22 19:10:25-07');

查询：

SELECT * FROM timestamp_demo;
/* 结果
         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-23 10:10:25+08
*/

将时区设置为纽约：

SET timezone = 'America/New_York';

再次查询：

 SELECT * FROM timestamp_demo;
 /*结果
         ts          |          tstz
---------------------+------------------------
 2016-06-22 19:10:25 | 2016-06-22 22:10:25-04
 */

你会看到：

• 不带时区的ts 字段，无论时区如何改变，它的值都是固定不变的
• 带时区的tstz 字段，它的值随着时区的改变而自动发生改变

强烈建议使用TIMESTAMPTZ来存储日期时间

强烈建议使用TIMESTAMPTZ来存储日期时间

常用函数

获取当前日期时间

SELECT NOW();

或者

SELECT CURRENT_TIMESTAMP;

时区转换

将时间戳的值转换为另一个时区：

SELECT TIMEZONE('时区名', 时间戳);

比如：

-- 虽然将一个字符串传递给该函数，PostgreSQL会进行隐式转换
SELECT timezone('Asia/Shanghai','2016-06-01 00:00');

-- 但更建议的是手动进行显式转换
SELECT timezone('Asia/Shanghai','2016-06-01 00:00'::timestamptz);