PostgreSQL 07 表约束和时间类型

1月 07 2020

前边知道了怎么创建表, 以及关键的创建自增序列和主键. 现在来看看其他的表操作

此外还有一些常用的数据类型比如DATE等的操作, 由于日期也是常用操作, 一定得掌握.

ALTER TABLE
临时表
PostgreSQL的约束
时间类型 – DATE类型
时间类型 – TIMESTAMP类型
时间类型 – INTERVAL类型
时间类型 – TIME类型

ALTER TABLE

ALTER TABLE命令用来改变关系的内容, 改变关系已经知道就是改变属性, 所以可以增删改列, 设置默认值, 添加约束条件等. 还可以重命名一个关系, 简要命令如下:

ALTER TABLE table_name ADD COLUMN new_column_name TYPE;, 添加新列
ALTER TABLE table_name DROP COLUMN column_name, 删除列
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;, 修改列名
ALTER TABLE table_name ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT];, 添加和删除默认值
ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL| DROP NOT NULL];, 添加约束
ALTER TABLE table_name ADD CHECK expression;, 添加CHECK约束
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;, 添加其他约束
ALTER TABLE table_name RENAME TO new_table_name;, 修改表名

临时表

临时表指的是存在于一次数据库会话中的表, 在会话结束的时候, PgSQL会删除临时表. 在一个会话中创建的临时表, 其他会话无法使用.

创建临时表的指令是:

CREATE TEMPORARY TABLE temp_table(
   ...
);

可以用TEMP来代替TEMPORARY. 临时表的一个特性是可以创建与已经存在的表同名的临时表, 只要临时表还在, 访问同名的表只会访问到临时表. 如果不指定schema, PgSQL会在一个特殊的schema中创建临时表.

操作临时表的各种命令与操作普通表一样.

PostgreSQL的约束

有如下几种约束:

主键, 操作已经基本都知道了.

外键, 操作已经基本都知道了, 唯一注意就是可以指定联合外键:

CREATE TABLE child_table(
  c1 INTEGER PRIMARY KEY,
  c2 INTEGER,
  c3 INTEGER,
  FOREIGN KEY (c2, c3) REFERENCES parent_table (p1, p2)
);

CHECK, CHECK只能进行与字段相关的简单比较, 不能使用复杂的SQL SELECT等操作
UNIQUE, 这个在之前的SQL标准中也看过了
NOT NULL, 这个在之前的SQL标准中也看过了

时间类型 – DATE类型

日期操作是很普遍的操作, 这次就好好看看PgSQL中日期的操作, 这样以后根据日期来进行查询也能彻底掌握了.

DATE类型仅仅保留日期的部分, 也就是年月日, 范围是4713 BC 到 5874897 AD. 对于一般的时间记录足够使用了.

在存储DATE类型的时候, PgSQL允许使用yyyy-mm-dd的字符串形式来写入到DATE类型的列中.

如果需要创建一个默认值是当前日期的列, 采用如下语句:

col_name DATE NOT NULL DEFAULT CURRENT_DATE

CURRENT_DATE 就是代表当前时间的关键字, 与函数now()结果一样;

SELECT now();

不过NOW()返回的其实是一个TIMESTAMP类型. 可以使用TOCHAR函数将其转换成指定格式的字符串:

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

可以直接相减得到Interval类型:

SELECT
    rental_date,
    now() - rental_date as diff
FROM
    rental

order by diff
limit 10

用age()函数加上一个时间, 可以计算这个时间到现在的间隔, 所以叫AGE函数:

SELECT
    rental_date,
    age(rental_date) diff
FROM
    rental

order by diff
limit 10

从日期中获取年, 月, 日, 使用EXTRACT:

SELECT
   employee_id,
   first_name,
   last_name,
   EXTRACT (YEAR FROM birth_date) AS YEAR,
   EXTRACT (MONTH FROM birth_date) AS MONTH,
   EXTRACT (DAY FROM birth_date) AS DAY
FROM
   employees;

最后总结列出一下上边所用的函数和操作:

CURRENT_DATE, 代表当前DATE
NOW(), 函数, 返回当前TIMESTAMP
TOCHAR(), 功能之一是格式化输出时间
AGE(), 计算指定时间到今天的间隔
EXTRACT(YEAR/MONTH/DAY FROM birth_date), 抽取年月日

时间类型 – TIMESTAMP类型

这个时间类型分为两种, 一是TIMESTAMP 二是TIMESTAMPZ. 第一个是不带时区信息, 第二个带时区信息.

所谓不带时区信息的意思是, 如果PgSQL所在的服务器的时区发生了变化, 从TIMESTAMP中取出的结果不会变化. 带时区的意思指的是当插入一个时间的时候, PgSQL会根据当前的时间将其转换成一个UTC时间格式写入数据库.
查询的时候, 会将查到的结果以数据库软件运行所在的时区展示出来. 所以实际上, TIMESTAMPZ不携带任何时区信息, 只是一个UTC值.

TIMESTAMP都是8字节存储的, 查看数据类型可以使用:

SELECT
    typname,
    typlen
FROM
    pg_type
WHERE
        typname ~ '^timestamp';

实际中选用哪一个呢, 看一个例子:

-- 创建一个表带有这两种数据类型
CREATE TABLE timestamp_demo (ts TIMESTAMP, tstz TIMESTAMPTZ);

-- 查看当前时区, 结果发现是UTC
SHOW TIMEZONE

-- 设置时区是上海
SET timezone = 'Asia/Shanghai';

-- 插入当前时间
INSERT INTO timestamp_demo (ts, tstz)
VALUES
(
    now(),
    now()
);
-- 结果是:
             ts                            tstz
2020-01-06 15:20:26.966515  2020-01-06 15:20:26.966515 +08:00

可以发现, ts存的就是那个UTC的值, 取出来的时候也是按照UTC进行解释, 而tstz在取出来的时候, 是按照时区进行解释的.

更改服务器的时区再检索:

SET timezone = 'America/Los_Angeles';
SHOW TIMEZONE;
SELECT * FROM timestamp_demo;

可以发现tstz的结果又变化了, 而ts没有变化:

             ts                            tstz
2020-01-06 15:20:26.966515  2020-01-05 23:20:26.966515 -08:00

所以实践中, 应该首先设置好服务器的时区, 然后选择TIMESTAMPZ来存储数据. 再看看TIMESTAMP相关的函数:

now(), 这个在DATA里也见过, 返回值其实是一个TIMESTAMPZ, 包含DATA和TIME数据和根据当前时区转换.
CURRENT_TIMESTAMP, 这个在DATA里也见过, 返回值其实是一个TIMESTAMPZ, 包含DATA和TIME数据并且根据当前时区转换.(CURRENT_TIME也是带时区的)
timeofday(), 返回字符串格式的当前时间, 也是带时区的
timezone(zone, timestamp), 按照一个时区转换指定的timestamp, 比如 SELECT timezone(‘Asia/Tokyo’, CURRENT_TIMESTAMP), 将当前的东八区转换成东九区时间.

时间类型 – INTERVAL类型

INTERVAL类型比较特殊, 保存的其实是一个时间段, 但本质也是一个时间, 采用16字节保存, 范围是-178,000,000 年到 178,000,000 年.

INTERVAL定义的语句是: interval [ fields ] [ (p) ]

其中(p)表示精度, 可以选0-6, 表示秒数后边的小数长度.

fields表示时间内容, 比较特殊的是可以使用字符串形式的内容,比如:

interval '2 months ago';
interval '3 hours 20 minutes';

比如想知道当前时间3天以前, 就可以写如下语句:

SELECT now() -  INTERVAL '3 days ago'

这个就会用当前时间精确的减去3天然后得到3天前的相同时间的TIMESTAMP.

所以关键就是来看看这个fields怎么写, 有如下规则 quantity unit [quantity unit...] [direction]:

最开头的 quantity 是数量
unit表示时间单位, 可以使用的时间单位有很多, 从 y m d到复数 months, days都支持. 粒度则是从microsecond 一直到millennium都可以.
quantity和unit的组合可以写多个, 比如 1 year 3 months 10 days, 写完以后最后是方向, 有两个选择, 一个是 ago 表示之前, 一个是什么都不写, 表示之后.

fields还可以使用ISO 8601的格式, 这个格式如下: P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

其中的UNIT用 YMWDHMS 分别代表年月周日时分秒, 还可以使用 P [ years-months-days ] [ T hours:minutes:seconds ] 格式, 例子如下:

P6Y5M4DT3H2M1S
P0006-05-04T03:02:01

上边就是INTERVAL的赋值.

而如何输出INTERVAL格式, 也有讲究, 可以设置四种输出方法, 格式各不相同, 例子如下:

SET intervalstyle = 'sql_standard';

SELECT
   INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

SET intervalstyle = 'postgres';

SELECT
   INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

SET intervalstyle = 'postgres_verbose';

SELECT
   INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

SET intervalstyle = 'iso_8601';

SELECT
   INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second';

INTERVAL数值之间可以使用加减来计算得到新的INTERVAL数值, 此外也可以用TOCHAR函数转换成字符串, 用EXTRACT提取具体字段, 用法和普通的时间对象一样.

时间类型 – TIME类型

最后一种时间类型是TIME类型, 这个仅仅表示一天之内的时间, 不包含日期要素. 使用8字节保存, 范围是 00:00:00 到 24:00:00(会被转换成00:00:00, 所以实际上是00:00:00 – 23:59:59, 但是使用24:00:00不会报错)

在填入该类型的数据时, 可以用对应的字符串表示, 如下字符串PgSQL都支持:

HH:MM
HH:MM:SS
HHMMSS

TIME也有另外一种时区相关的类型, 叫做 TIME WITH TIME ZONE.

与TIME相关的函数有:

CURRENT_TIME, 单独使用, 就是带时区的当前时间, 带6位小数精度
CURRENT_TIME(n), 带时区的当前时间, 带n位小数精度
LOCALTIME, 带时区的当前时间, 不带小数
[TIME with time zone] AT TIME ZONE time_zone, 将带时区的时间转换成另外一个时区的时间

时区转换的例子如下:

-- 本地时间转换成东九区时间
SELECT LOCALTIME AT TIME ZONE 'UTC-9';

发表于PostgeSQL 数据库软件开发与应用