首页 > 数据库 >PostgreSQL的数据类型

PostgreSQL的数据类型

时间：2024-08-11 15:17:33浏览次数：6  
标签：PostgreSQL postgres timestamp 数据类型 2020 类型 select row

PostgreSQL的数据类型

1. 布尔类型

1.1 布尔值对应表

在Postgresql数据库中Boolean的值除了“true”（真）、“false”（假），还有一个“unknown”（未知）状态。
如果是unknown时用NULL表示。布尔类型在Postgresql中可以用不带引号的TRUE或FALSE表示，也可以用更多表示真和假的带引号的字符表示：

真 假 备注
‘TRUE’ FALSE
tRue fAlse
tRue fAlse 不分大小写
t f 单一字符表示
yes no 英文的是和否表示
y n yes和no的简写表示
1 0 1表示true，0表示false

以上表格中值加引号都可以表示真和假，但是在Postgresql中Boolean类型字段里存储的值还是 t （true）和 f（false）。

1.2 布尔类型的操作符

操作符分为逻辑操作符和比较操作符
1.逻辑操作符有：AND、OR、NOT。
SQL标准使用三种值的布尔逻辑：TRUE、FALSE和NULL，NULL代表unknown。

布尔的and、or逻辑运算：

a b a AND b a OR b
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL NULL NULL NULL

布尔的not运算：

a NOT a
TRUE FALSE
FALSE TRUE
NULL NULL

逻辑操作符的两边值可以互换，结果是相同的。布尔类型使用 IS 作为比较运算符，结果很直观，不多做介绍了。

2. 数值类型（整数、小数、浮点数、序列、货币）

2.1 Postgresql数据库的所有数值类型及其解释：

类型名称 所占空间 描述 取值范围
smallint 2字节 小范围整数，Oracle中使用number代替 -215 ~ 215-1
int或integer 4字节 常用整数，Oracle中也有integer，等效于number(38)，与此不同 -231 ~ 231-1
bigint 8字节 大范围整数，Oracle中没有此类型，也是只有number代替 -263 ~ 263-1
numeric或decimal 变长 用户来声明精度 无限制
real 4字节 变精度，不精确 6位十进制数字精度
double precision 8字节 变精度，不精确 15位十进制数字精度
serial 4字节 自增整数 1 ~ 2的31次方 -1
bigserial 8字节 大范围自增整数 1 ~ 2的63次方-1

2.2 数值类型的详细解释

2.2.1 整数类型

整数类型分三种：smallint、int、bigint。
Postgresql中最常用的是int类型，它提供了在范围，存储空间、性能之间的平衡。一般磁盘紧张会使用smallint，在int范围不足时用bigint类型。
SQL语句中 int、integer和int4是等效的，smallint和int2是等效的，bigint和int8是等效的，也就是说在创建表时，指定字段的类型可以用简写表示，便于编写SQL语句。

2.2.2 精确的小数类型

精确的小数类型可以用numeric、numeric(m)、numeric(m,n)表示。
numeric和decimal是等效的。可以存储1000位精度的数字，它的语法是：
numeric(precision， scale)
—— precision必须位正数，规定了小数点前有几位。
—— scale可以是零或者正数，规定了小数点后有几位。
numeric(m)表示了scale是0，与numeric(m,0)的含义相同。
而numeric表示precision和scale没有指定，可以存储任意精度和标度的数值。

postgres=# create table tb_int(id1 numeric(3),id2 numeric(3,0),id3 numeric(3,2),id4 numeric);
CREATE TABLE
postgres=# \d tb_int 
                 Table "public.tb_int"
 Column |     Type     | Collation | Nullable | Default 
--------+--------------+-----------+----------+---------
 id1    | numeric(3,0) |           |          | 
 id2    | numeric(3,0) |           |          | 
 id3    | numeric(3,2) |           |          | 
 id4    | numeric      |           |          | 

postgres=# insert into tb_int values(3.126,3.126,3.126,3.126);
INSERT 0 1
postgres=# select * from tb_int ;
 id1 | id2 | id3  |  id4  
-----+-----+------+-------
   3 |   3 | 3.13 | 3.126
(1 row)
postgres=# insert into tb_int values(3.126,1311.126,3.126,3.126);
2020-02-16 20:33:05.601 CST [27416] ERROR:  numeric field overflow
2020-02-16 20:33:05.601 CST [27416] DETAIL:  A field with precision 3, scale 0 must round to an absolute value less than 10^3.
2020-02-16 20:33:05.601 CST [27416] STATEMENT:  insert into tb_int values(3.126,1311.126,3.126,3.126);
ERROR:  numeric field overflow
DETAIL:  A field with precision 3, scale 0 must round to an absolute value less than 10^3.

PS：声明了精度（precision）没有声明标度（scale），超出精度的会按照四舍五入取值。
同样，声明了标度时，超出标度的会按照四舍五入取值。

2.2.3 浮点数类型

数据类型real和double precision 是不精确的、变精度的数字类型。
对于浮点数，需要注意：
（1）如果需要精确计算，应用numeric类型。
（2）两个浮点数类型的值做比较时，结果可能不是所想象的那样运行。
（3）infinity、-infinity、NaN分别表示正无穷大、负无穷大、非数字。

2.2.4 序列类型

序列类型中，serial和bigserial与MySQL中的自增字段是同样性质，在Postgresql中是通过序列（sequence）来实现的。
比如：

postgres=# create table tb_serial(id serial);
CREATE TABLE
postgres=# \d+ tb_serial
                                                Table "public.tb_serial"
 Column |  Type   | Collation | Nullable |                Default                | Storage | Stats target | Description 
--------+---------+-----------+----------+---------------------------------------+---------+--------------+-------------
 id     | integer |           | not null | nextval('tb_serial_id_seq'::regclass) | plain   |              | 
Access method: heap

例子中创建了一个表 tb_serial ，它的id列位serial类型，创建后查询表属性，显示id默认的是通过序列赋值，并且可以看到除了表之外还同时创建了一个名字位tb_serial_id_seq的序列。
所以create语句就相当于一下几个语句同时执行：

create sequence tb_serial_id_seq;
create table tb_serial(
	id integer NOT NULL DEFAULT nextval('tb_serial_id_seq')
	);
alter sequence tb_serial_id_seq OWNED BY tb_serial.id;

2.2.5 货币类型

货币类型（money type）可以存储固定小数的货币数目，精度准确。查询时输出的格式跟数据库中的参数：lc_monetary的设置有关，不同国家的货币输出的格式时不一样的。例如：

postgres=# select '12.14'::money;
  money  
---------
 ￥12.14
(1 row)

postgres=# select '1214.13'::money;
   money    
------------
 ￥1,214.13
(1 row)

postgres=# show lc_monetary;
 lc_monetary 
-------------
 zh_CN.UTF-8
(1 row)

postgres=# set lc_monetary='en_US.UTF-8';
SET
postgres=# select '12.14'::money;
 money  
--------
 $12.14
(1 row)

postgres=# select '1214.13'::money;
   money   
-----------
 $1,214.13
(1 row)

postgres=# show lc_monetary;
 lc_monetary 
-------------
 en_US.UTF-8
(1 row)

2.3 数学函数和操作符

数学操作符：

操作符 描述 例子 结果
+ 加 4+7 11
- 减 4-7 -3
* 乘 4*7 28
/ 除（只保留整数部分，不会四舍五入） 3/2 1
% 模（求余数） 6%4 2
^ 次幂（指数运算） 3^3 27
l/ 平方根 l/4.0 2
ll/ 立方根 ll/8.0 2
！ 阶乘 5！ 120
!! 前缀方式的阶乘 !!5 120
@ 绝对值 @-5.0 5
& 二进制AND 31&15 15
l 二进制OR 31 l 15 31
# 二进制XOR 31 # 15 16
~ 二进制NOT ~1 -2
<< 二进制左移 1 << 8 256
>> 二进制右移 16 >> 3 2

数学函数：

函数 描述 例子 结果
abs(x) 绝对值 abs(-23.7) 23.7
cbrt(dp) 立方根 cbrt(8.0) 2
ceil(dp或numeric) 别名：ceiling 不小于参数的最小整数 ceil(-38.8),ceil(38.1),ceiling(38.1) -38,39,39
degrees(dp) 把弧度转化为角度 degrees(1.0) 57.295779513
exp(dp或numeric) 自然指数 exp(1.0) 2.71828182845905
floor(dp或numeric) 不大于参数的最大整数 floor(-38.8)，floor(38.8) -38，38
ln(dp或numeric) 自然对数 ln(2.71828) 0.999999
log(dp或numeric) 以10为底的对数 log(1000.0) 3
log(b numeric,x numeric) 以b为底的对数 log(2.0,32.0) 5.00000
mod(y,x) y/x的余数（模） mod(7,3) 1
pi() Π的常量 pi() 3.14159265358979
power(a dp,b dp ) a的b次幂 power(2.0,3.0) 8
power(a numeric,b numeric) a的b次幂 power(2.0,3.0) 8
radians(dp) 把角度转换为弧度 radians(45.0) 0.785398163397
random() 0.0-1.0之间的随机数 random() 随机返回一个小数
round(dp或numeric) 圆整为最接近的整数（四舍五入） round(36.5) 37
round(v numeric, s int) 圆整为s位小数（四舍五入） round(36.5252, 2) 36.53
setseed(dp) 为随后的random()调用设置种子（0到1.0之间） setseed(0.2)； random() 先设置setseed后，后续的random取值是可以被预见的，后三次取值将会是固定值
sign(dp 或 numeric) 返回参数的符号：-1代表负数；0代表0；1代表正数 sign(-1.5) -1
sqrt(dp 或 numeric) 平方根 sqrt(9.0) 3
trunc(dp 或 numeric) 截断小数,不四舍五入 trunc(2.3) 2
trunc(v numeric, s int) 截断 s 位小数 trunc(12.356, 3) 12.35

数学函数之三角函数：

函数 描述 例子 结果
acos(x) 反余弦 acos(1) , acos(-1) 0 , 3.14159265358979
asin(x) 反正弦 asin(0) , asin(1)*2 0, 3.14159265358979
atan(x) 反正切 atan(1)
atan2(x,y) x/y的反正切
cos(x) 余弦
cot(x) 余切
sin(x) 正弦
tan(x) 正切

3. 字符串类型

3.1 Postgresql数据库字符串类型

类型 描述
character varying(n) 或 varchar(n) 变长字符串类型，最大空间1GB，存储空间4+实际的字符串长度。与MySQL中的varchar(n)或text(n)，以及Oracle中的varchar2(n)类似，但是在MySQL中varchar最多只有64KB，Oracle中的varchar2最多只有4000字节。
character(n)或char(n) 定长字符串类型，不足空白补充。最大1GB，存储空间4+n
text 变长字符串类型。无长度限制，与MySQL中的LONGTEXT类似

varchar(n) 在没有指定 n 的值时，表示可以接受1GB内的任何长度的字符串，建议加上 n，
char(n) 在没有指定 n 值时，表示 char(1) ,
不管varchar还是char类型最小存储空间时4字节。而在Postgresql数据库中varchar和char指定长度过长时，实际在字段中存储的可能只是一个指针，具体内容会存储在toast表中，指针便于对字段值的快速访问。
在一些其他种类的数据库中定长的char有一定的优势。但是在postgresql中没有什么差别。大多数还是会用varchar或text。

2.2 字符串函数和操作符

支持标准的SQL字符串的函数和操作符：

函数 返回类型 描述 例子 结果
string ll string text 字符串链接 selec ‘abc’ ll ‘EFG’； ‘abcEFG’
length(string) int string 中字符的个数，主要区分的是中文和英文不同 select length(‘abc’) 3
bit_length(string) int 字符串二进制位长度（个数） select bit_length(‘jack’); 32
char_length(string)或character_length(string) int 字符串中字符的个数 select char_length(‘jack’); 4
octet_length(string) int 字符串中的字节数 select octet_length(‘jack’); 4
convert(string using conversion_name) test 使用指定的转换名字改变编码。转换可以通过CREATE CONVERSION定义，系统中也有一些预定义的转换名字 select convert(‘Postgresql’ using iso_8859_1_to_utf8); UTF8编码的Postgresql
convert( string text, [ src_encoding name, ], dest_encoding name ) text 把字符串原来编码位 src_encoding 转成 dest_encoding 编码。如果没有指定src_encoding，则src_encoding为数据库编码 select convert( ‘abc’, ‘UTF8’, ‘GBK’ ) 以GBK编码的abc
lower(string) text 把所有的字符串转成小写 select lower(‘TOM’); tom
upper(string) text 把所有字符串转成大写 select upper(‘tom’); TOM
initcap(string) text 将字符串中每个单词的第一个字符转为大写，其他变为小写（字符串中区分单词：非字符数字分割） initcap(‘hi jack’) Hi Jack
overlay(string placing string from int[for int]) text 指定子字符串替换的范围，例子里时用 hom 字符串替换 Txxxxas 中从第2个字符开始的4个字符，如果没有指定 for int ，int 是子字符串的长度 select overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4); Thomas
position( substring in string ) int 查找substring 在 string 中的起始位置 select position( ‘om’ in ‘Thomas’); 3
strpos(string , substring) int 查找指定的子字符串substring在string中的起始位置 select strpos(‘helloworld’ ,‘llo’); 3
substring(string, from [, count] ) text 截取string中指定位置from的指定长度count的子字符串 select substring ( ‘jack’ , 2, 2) ac
substring(string from int for int ) text 抽取指定位置（from int ）开始指定个数（for int）的字符串，如果没有指定 for int 则从指定位置一直到结束全部截取 select substring( ’ Thomas’ from 2 for 3 ); hom
substring(string from pattern) text 截取匹配posix正则表达式的字符串 select substring(‘hello world’ from ‘…$’); rld
substring(string from pattern for escape) text 截取匹配posix正则表达式的字符串,for为转移字符 select substring(‘Thomas’ from ‘%#“o_a#”_’ for ‘#’); oma
replace(string text, from text, to text ) text 把字符串string中所有出现的from 都替换成 to select replace(‘abcddggdd’,‘d’,‘5’) abc55gg55
ascii(string) int 字符串中第一个字符的ASCII码 select ascii(‘abc’) 97
chr(int) text 给出ASCII码所对应的字符 select chr(97) a
decode(string text, type text ) bytea 把早先用encode编码的string里面的二进制数据解码，支持类型：base64、hex、escape
encode(data bytea, type text) text 把二进制数据编码位只包含ASCII形式的数据，支持类型与decode类似
lpad(string text, length int [, fill text] ) text 通过在指定的string左边填充字符 fill （不指定默认空白），把string填充为指定的length长度，如果string长度比指定的length长，则将其尾部截断,例子里是将字符串 OK 左边使用 1 填充直到长度为5 select lpad( ‘OK’, ‘5’, ‘1’) 111OK
rpad(string text, length int [, fill text] ) text 通过在指定的string右边填充字符 fill （不指定默认空白），把string填充为指定的length长度，如果string长度比指定的length长，则将其尾部截断,例子里是将字符串 OK 右边使用 1 填充直到长度为5 select rpad( ‘OK’, ‘5’, ‘1’) OK111
btrim( string text [ , character text ] ) text 从string的开头和结尾删除含有 character 中的字符，不指定character默认空格 select btrim( ‘aajackaaaa’, ‘aa’ ) jack
ltrim( string text [ , character text ] ) text 从string的开头（左边）删除含有 character 中的字符，不指定character默认空格 select btrim( ‘aajackaaaa’, ‘aa’ ) jackaaaa
rtrim( string text [ , character text ] ) text 从string的结尾（右边）删除含有 character 中的字符，不指定character默认空格 select btrim( ‘aajackaaaa’, ‘aa’ ) aajack
quote_ident(string) text 返回适用于SQL语句的标识符形式（使用适当的引号进行界定），只有在必要的时候才会添加引号（字符串包含非标识符字符或者会转换大小写的字符），嵌入的引号会被恰当的写双份
quote_literal(string) text 返回适用于在SQL语句里当作文本的形式，嵌入的引号和反斜杠被恰当的写双份
pg_client_encoding() name 当前客户端编码名称 select pg_client_encoding(); UTF8
repeat(string text, number int ) text 将string重复number次 select repeat( ‘abc’, 2); abcabc
split_part(string text, delimiter text, field int) text 根据delimiter分隔string，返回生成第 field 个字符串 split_part( ‘123#456#789’, ‘#’, 2 ) 456
regexp_replace(string text, pattern text, replacement text [ , flages text ]) text 替换匹配POSIX正则表达式的子字符串

3.3 特别注释

字符串length类似函数用法：

postgres=# select length('abcd');
 length 
--------
      4
(1 row)

postgres=# select octet_length('abcd');
 octet_length 
--------------
            4
(1 row)

postgres=# select bit_length('abcd');
 bit_length 
------------
         32
(1 row)

postgres=# select length('测试');
 length 
--------
      2
(1 row)

postgres=# select octet_length('测试');
 octet_length 
--------------
            6
(1 row)

postgres=# select bit_length('测试');
 bit_length 
------------
         48
(1 row)

4. 日期类型

4.1 日期和时间的类型如下

名称 存储空间 描述 例子
timestamp [ p ] [ without time zone ] 8字节 显示日期加时间 2020-02-23 12:00:00
timestamp [ p ] with time zone 8字节 显示日期、时间加时区
interval [ § ] 12字节 时间间隔
date 4字节 只用于日期
time [ p ] [ without time zone ] 8字节 只用于1日内时间，不带时区
time [ p ] with time zone 8字节 只用于1日内时间，带有时区

4.2 日期输入

在SQL中，任何日期或时间的文本输入需要有 ’ 日期/时间 ‘ 类型加单引号包围的字符串组成，语法：
type [ ( p ) ] ’ value ’
日期和时间的输入几乎可以是任何合理的形式，在Postgresql中有系统参数 datestyle 决定是什么格式或形式，如下：

postgres=# show datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

其中 “ MDY ” 表示 月-日-年
在数据库中参数可以设置成 YMD 的形式，但在使用语句插入时，用了date转换格式输入，参数设置那种格式没有影响，都可以进行插入成功。

postgres=# show datestyle;
 DateStyle 
-----------
 ISO, MDY
(1 row)

postgres=# insert into tb_date values(date '02-23-2020');
INSERT 0 1
postgres=# insert into tb_date values(date '2020-02-23');
INSERT 0 1
postgres=# select * from tb_date ;
    col1    
------------
 2020-02-23
 2020-02-23
(2 rows)

但是有时候日期输入习惯使用诸如此类 ’ 1/8/2020 ‘ 的格式，类似这种各位月或日没有0做前缀时要注意参数 datestyle 的值，防止日期混乱或者插入错误。
日期输入示例：

例子 描述
date ‘April 26,2020’ 在任何datestyle参数值下都没有输入问题
date ‘2019-01-08’ ISO 8601格式，任何方式输入没有问题
date ‘1/8/2019’ 在datestyle为 MDY 时，表示2019年1月8日，在datestyle为 DMY 时，表示2019年8月1日，类似输入日期文本时没有在一位数月份或日前加前缀0的输入方式一定要注意 datestyle 参数的格式，凡是日期混乱
date ‘1/18/2020’ 在datestyle参数值为 MDY 时，表示2020年1月18日，其他格式会出现输入错误
date ‘03/04/11’ 在 MDY 格式下表示2011年3月4日，在DMY 格式下表示2011年4月3日，在YMD格式下表示2003年4月11日，所以建议在输入日期文本时要输入完整的日期文本，尽量不要用简写导致日期混乱。
date ‘2019-Apr-08’ 在任何格式下都是2019年4月8日
date ‘Apr-08-2019’ 在任何格式下都是2019年4月8日
date ‘08-Apr-2019’ 在任何格式下都是2019年4月8日
date ‘20190405’ ISO 8601格式，任何格式下都是2019年4月5日
date ‘110405’ ISO 8601 格式，在任何格式下都是2011年4月5日
date ‘2019.098’ 2019年的第98天，即2019年4月8日
date ‘J2455886’ 儒略日，即从公元前4713年1月1日期到今天经过的天数，多为天文学家使用，2455886天，就是2011年11月20日
date ‘April 26,202 BC’ 公元前202年4月26日

PS: 对于国内的程序员来说，避免使用 ’/’ 进行日期的输入，，最好使用 ‘-’ 来进行日期文本分割，然后使用 YMD 也就是 年-月-日
的格式输入日期。

3.4 时间输入

时间输入时，要注意时区的输入，time 被认为时 time without time zone 的类型，所以即使输入的字符串中有时区，也会被忽略，例如：

postgres=# select time '12:59:59';
   time   
----------
 12:59:59
(1 row)

postgres=# select time '12:59:59 PST';
   time   
----------
 12:59:59
(1 row)

postgres=# select time with time zone  '12:59:59 PST';
   timetz    
-------------
 12:59:59-08
(1 row)
1234567891011121314151617

时间输入时字符串之间使用冒号作为分隔符，输入格式为： ‘ hh:mm:ss ’，如：‘ 12:59:59 ’，也可以不使用分隔符，如：‘125959’ 表示 12时59分59秒。
更多的时间输入示例如下：

例子 描述
time ‘12:59:59.789’ ISO 8601
time ‘12:59:59’ ISO 8601
time ‘12:59’ ISO 8601
time ‘125959’ ISO 8601
time ‘10:59 AM’ 与 10：59 一样，AM不影响数值
time ‘10:59 PM’ 与 22：59一样，使用PM时输入的小时的值小于等于12
time with time zone ‘12:59:59.789+8’ 带有时区
time with time zone ‘12:59:59+08:00’ ISO 8601
time with time zone ‘12:59+08:00’ ISO 8601
time with time zone ‘125959+08:00’ ISO 8601
time with time zone ‘12:59:59 CCT’ 带有缩写的时区，北京时间12:59:59
select time with time zone ‘2020-02-24 12:59:59 Asia/Beijing’ 用具体名字声明时区

PS: 建议在输入时区时不适用缩写表示，相同的缩写代表的可能时不同的时区。

4.4 特殊的时间字符串

在Postgresql中有一些特殊的字符串输入表示了特别的意义，如下：

字符串 使用类型 描述
epoch date,timestamp 1970-01-01 00:00:00+00（Unix 系统的零时）
infinity timestamp 时间戳的最大值，比任何其他时间戳都晚
-infinity timestamp 时间戳的最小值，比任何其他时间戳都早
now date,time,timestamp 当前事务的开始时间（当前时间）
today date,timestamp 今日午夜
tomorrow date,timstamp 明日午夜
yesterday date,timestamp 昨日午夜
allballs time 00:00:00.00 UTC

4.5 时间函数和操作符

日期、时间和interval类型之间可以进行加减乘除运算，具体如下：

操作符：

操作符 例子 结果
+ date ‘2020-02-01’ + integer ‘7’ date ‘2020-02-08’
+ date ‘2020-02-01’ + interval ‘1 hour’ timestamp ‘2020-0201 01:00:00’
+ date ‘2020-02-01’ + time ‘03:00’ timestamp ‘2020-02-01 03:00:00’
+ interval ‘1 day’ + interval ‘1 hour’ interval ‘1 day 01:00:00’
+ timestamp ‘2020-02-01 01:00’ + interval ‘23 hours’ timestamp ‘2020-02-02 00:00:00’
+ time ‘01:00’ + interval ‘2 hours’ time ‘03:00:00’
- date ‘2020-02-23’ - date ‘2020-02-20’ 3
- date ‘2020-02-23’ - integer ‘3’ date ‘2020-02-20’
- date ‘2020-02-23’ - interval ‘1 hour’ timestamp ‘2020-02-22 23:00:00’
- time ‘07:00’ - time ‘03:00’ interval ‘04:00:00’
- time ‘07:00’ - interval ‘4 hours’ time ‘03:00:00’
- timestamp ‘2020-02-23 22:00’ - interval ‘22 hour’ timestamp ‘2020-02-23 00:00:00’
- interval ‘a day’ - interval ‘1 hour’ interval ‘1 day -01:00:00’
- timestamp ‘2020-02-23 03:00:00’ - timestamp ‘2020-02-20 12:00’ interval ‘2 days 15:00:00’
* 600*interval ‘1 second’ interval ‘00:01:00’
* 15*interval ‘1 day’ interval ‘15 days’
* 2.5*interval ‘1 hour’ interval ‘02:30:00’
/ interval ‘2 hour’ / 1.5 interval ‘01:22:00’

函数如下：

函数 返回类型 描述 例子 结果
age(timestamp,timestamp) interval 两个时间段之间的时间间隔，前者时间一般为较新时间，结果为正数的时间间隔，否则为负数的时间间隔 age(timestamp ‘2020-02-01’,timestamp ‘2019-01-01’); age(timestamp ‘2019-02-01’,timestamp ‘2020-01-01’); 1 year 1 mon ；-11 mons
age(timestamp) interval 从当前时间减去参数时间的结果（时间间隔） select age(timestamp ‘2000-01-01’) 20 years 1 mon 23 days
clock_timestamp() timestamp with time zone 实时时钟的当前时间戳，带有时区 select clock_timestamp();
current_date date 当前日期，注意使用时不用加括号 select current_date
current_time time with time zone 当前时间，带有时区 select current_time;
current_timestamp timestamp with time zone 当前事务开始时的时间戳，带有时区 select current_timestamp
date_part(text, timestamp) double precision 获取时间timestamp中指定年月日时分秒等，text的值包括了year（简写 y ），month（m），day（d），hour（h），minute（m），second（s） select date_part(‘day’,timestamp ‘2020-02-23 12:59:30’); 23
date_part(text, interval) double precision 获取interval时间类型中的指定的年月日时分秒等，text的值包括了year（简写 y ），month（m），day（d），hour（h），minute（m），second（s） select date_part(‘hour’,interval ‘2 years 3 months 12 days 10 hours 30 minutes 10 seconds’); 10
date_trunc(text, timestamp) timestamp 将时间timestamp截断成text指定的精度，text的值包括了year（简写 y ），month（m），day（d），hour（h），minute（m），second（s） select date_trunc(‘hour’,timestamp’2020-02-23 12:59:30’); 2020-02-23 12:00:00
extract(field from timstamp) double precision 获取timestamp中指定的text，类似date_part select extract(‘hour’ from timestamp ‘2020-02-23 12:59:30’)； 12
extract(field from interval) double pracision 获取interval中指定的text，类似date_part select extract(‘hour’ from interval ‘2 days 12 hours’) 12
isfinite(timestamp) boolean 测试是否为有穷时间戳 isfinite(timestamp ‘2020-02-25 20:12:30’) true
isfinite(interval) boolean 测试是否为有穷时间隔 isfinite(interval ‘5 hours’) true
justify_days(interval) interval 按照每天30天调整时间间隔，每个30天会变为一个月，不够显示真是天数，超出显示减去30天后的天数 select justify_days(interval ‘35 days’); 1 mon 5 days
justify_hours(interval) interval 按照每24小时调整为1天，满24小时会以day显示，如果输入的是day，只会显示day select justify_hours(interval ‘35 hours’); 1 day 11:00:00
justify_interval(interval) interval 同时具备justify_days与justify_hours的功能，而interval参数可以进行加减计算 select justify_interval(interval ‘1 mon - 2 days’); 28 days
localtime time 当日时间 select localtime; 20:39:09.384134
localtimestamp timestamp 当前事务开始时的时间戳 select localtimestamp; 2020-02-25 20:40:17.168613
now() timestamp with time zone 当前事务开始时的时间戳并带有时区 select now(); 2020-02-25 20:41:28.969894+08
statement_timestamp() timestamp with time zone 与now()类似，实时时钟的当前时间戳 select statement_timestamp(); 2020-02-25 20:42:24.416925+08
timeofday() text 与statment_timestamp类似，但是返回的类型是text字符串，而非时间类型，显示的格式也不一样 select timeofday(); Tue Feb 25 20:44:17.330426 2020 CST
transaction_timestamp() timestamp with time zone 当前事务开始时的时间戳 select transaction_timestamp() 2020-02-25 20:46:02.107255+08

除了以上的函数外，Postgresql还支持SQL的OVERLAPS操作符，如：
(start1，end1)OVERLAPS(start2,end2)
(start1,length1)OVERLAPS(start2,length2)
以上两个表达式表示在左边的时间段与右边的时间段是否有重叠，如果重叠返回true，如果没有重叠返回false。终点参数（end1、end2）可以是日期，时间，时间戳。或者后面（length1、length2）是一个时间间隔的日期、时间、时间戳。如：

postgres=# select (date '2019-01-01',date '2019-09-25')overlaps(date '2019-09-01',date '2020-02-25');
 overlaps 
----------
 t
(1 row)

postgres=# select (date '2019-01-01',date '2019-08-31')overlaps(date '2019-09-01',interval '25 days');
 overlaps 
----------
 f
(1 row)

4.6 时间函数

Postgresql数据库中内置了很多返回当前日期和时间的函数，一下函数是按照当前事务的开始时间返回结果：

current_date
current_time
current_timestamp
current_time(precision)
current_timestamp(precision’)
localtime
localstamp
localtime(precision)
localtimestamp(precision)
now()
transacion_timestamp()

其中precision代表的是精度，该精度会导致结果的秒数域会四舍五入到指定的小数位，如果没有精度参数，将给予全部得到的精度。

PS: 特别介绍一下，当前时间获取的函数分为事务开始时的时间戳和实时时钟（当前时间）的时间两类，分别如下：
1.当前事务开始时的时间戳。开始事务时也就是begin；执行时表示开始了一个事务，而当前事务开始时的当前时间，表示执行begin时的时间，在end结束事务前，获取的当前时间时不变的，都是相同一个时间。主要函数包括：current_date、current_time、current_timestamp、localtime、localstamp、transaction_timestamp().。Postgresql这么做的目的是为了允许一个事务在当前时间上有连贯的概念，同一个事务里的多个修改可以保持同样的时间戳。
2.实时时钟时间表示即便执行begin开始了事务，获取的当前时间仍是随着时间而改变的，类似直接获取的是系统时间，函数有：clock_timestamp()、statement_timestamp()、stimeofday()。

所有的日期或时间还接受特殊的文本值：now。用于声明当前的时间和日期（注意：是当前事务开始的开始时间），所以，一下三个语句返回结果是相同的：

postgres=# begin;
BEGIN
postgres=# select current_timestamp;
       current_timestamp       
-------------------------------
 2020-02-25 21:24:32.964255+08
(1 row)

postgres=# select now();
              now              
-------------------------------
 2020-02-25 21:24:32.964255+08
(1 row)

postgres=# select timestamp with time zone 'now';
          timestamptz          
-------------------------------
 2020-02-25 21:24:32.964255+08
(1 row)

postgres=# end;
COMMIT

4.7 extract函数详解

extract函数格式：

extract(field from source)

extract函数从日期或时间数值中抽取子域，比如年分、小时等，返回类型为 double precision 的数值，source参数必须是一个timestamp、time、interval类型的值的表达式，此外，类型是date的表达式自动转换为timestamp，所以source也可用date类型，field参数是一个标识符或者字符串，指定了从源数据中抽取的域，下表列举了field可以取得值：

field值 说明 例子 结果
century 世纪 select extract(century from timestamp ‘2020-02-25 12:59:59’); 21
year 年份 select extract(year from timestamp ‘2020-02-25 12:59:59’); 2020
decade 得到年份除以10后的值 select extract(decade from timestamp ‘2020-02-25 12:59:59’); 202
millennium 得到当前是第几个千年（0-1000是第一个，1001-2000是第二个，2001-3000是第三个） select extract(millennium from timestamp ‘2020-02-25 12:59:59’); 3
quarter 是第几季度 select extract(quarter from timestamp ‘2020-02-25 12:59:59’); 1
month 当source是timestamp得到的是月份，当source是interval得到是月的数目 select extract(month from timestamp ‘2020-02-25 12:59:59’); l select extract(month from interval ‘2 years 8 months’); 2 l 8
week 得到所给的日期是这一年的第几个星期 select extract(week from timestamp ‘2020-02-25 12:59:59’); 9
dow 得到所给的日期是星期几，0是星期天，1是星期一 。。。 select extract(dow from timestamp ‘2020-02-25 12:59:59’); 2
day 本月的第几天 select extract(day from timestamp ‘2020-02-25 12:59:59’); 25
doy 本年的第几天 select extract(doy from timestamp ‘2020-02-25 12:59:59’); 56
hour 得到时间中的小时（0-23） select extract(hour from timestamp ‘2020-02-25 12:59:59’); 12
minute 得到时间中的分钟 select extract(minute from timestamp ‘2020-02-25 12:59:59’); 59
second 得到时间中的秒，包括小数部分 select extract(second from timestamp ‘2020-02-25 12:59:59.7521’); 59.7521
epoch 对于date和timestamp值来说，得到的是自1970-01-01 00：00：00以来的秒数；对于interval值来说，得到时间间隔的总秒数 select extract(epoch from timestamp ‘2020-02-25 12:59:59.7521’); l select extract(epoch from interval ‘2 days 12 hours’); 1582635599.7521 l 216000
milliseconds 秒域（包括小数）乘以 1000，即秒域的毫秒级 select extract(milliseconds from timestamp ‘2020-02-25 12:59:59.7521’); 59752.1
microseconds 秒域（包括小数部分）乘以 1000000，即秒域的微秒级 select extract(microseconds from timestamp ‘2020-02-25 12:59:59.7521’); 59752100
timezone 与UTC的时区偏移量，以秒记录。例如中国是 +8区，返回的是3600x8=28800 select extract(timezone from timestamp with time zone ‘2020-02-25 12:59:59.7521’); 28800
timezone_hour 时区偏移量的小时部分 select extract(timezone_hour from timestamp with time zone ‘2020-02-25 12:59:59.7521’); 8
timezone_minute 时区偏移量分钟部分，整数时区返回0 select extract(timezone_minute from timestamp with time zone ‘2020-02-25 12:59:59.7521’); 0

5. 枚举类型

5.1 枚举类型介绍

Postgresql数据库中的枚举类型不能直接使用，在使用前要先创建枚举类型，create type 命令创建枚举类型。
例如：

postgres=# create type week as enum('Sun','Mon','Tues','Wed','Thur','Fri','Sat');
CREATE TYPE
postgres=# create table tb_duty(person text, weekday week);
CREATE TABLE
postgres=# insert into tb_duty values('Zhang','Sun');
INSERT 0 1
postgres=# insert into tb_duty values('Li','Mon');
INSERT 0 1
postgres=# select * from tb_duty ;
 person | weekday 
--------+---------
 Zhang  | Sun
 Li     | Mon
(2 rows)

如果插入tb_duty表的weekday列中的值没有在枚举类型week中则会报错：

postgres=# insert into tb_duty values('Li','MON');
2020-02-26 09:25:32.727 CST [49927] ERROR:  invalid input value for enum week: "MON" at character 33
2020-02-26 09:25:32.727 CST [49927] STATEMENT:  insert into tb_duty values('Li','MON');
ERROR:  invalid input value for enum week: "MON"
LINE 1: insert into tb_duty values('Li','MON');

注意：枚举类型中指定的值大小写敏感！！！匹配时值必须完全一致！！

如果想查询tb_duty表中枚举类型的定义，如下：

postgres=# \dT+ week
                                      List of data types
 Schema | Name | Internal name | Size | Elements |  Owner   | Access privileges | Description 
--------+------+---------------+------+----------+----------+-------------------+-------------
 public | week | week          | 4    | Sun     +| postgres |                   | 
        |      |               |      | Mon     +|          |                   | 
        |      |               |      | Tues    +|          |                   | 
        |      |               |      | Wed     +|          |                   | 
        |      |               |      | Thur    +|          |                   | 
        |      |               |      | Fri     +|          |                   | 
        |      |               |      | Sat      |          |                   | 
(1 row)

直接查询系统表pg_enum也可以查看枚举类型的定义：

postgres=# select * from pg_enum;
  oid  | enumtypid | enumsortorder | enumlabel 
-------+-----------+---------------+-----------
 16416 |     16414 |             1 | Sun
 16418 |     16414 |             2 | Mon
 16420 |     16414 |             3 | Tues
 16422 |     16414 |             4 | Wed
 16424 |     16414 |             5 | Thur
 16426 |     16414 |             6 | Fri
 16428 |     16414 |             7 | Sat

5.2 枚举类型说明

在枚举类型中，定义的值的顺序是在创建此枚举类型week时已经定义好的顺序，而标准的运算符或者相关的聚集函数都可以对枚举类型就行操作。例如：

postgres=# select * from tb_duty ;
 person | weekday 
--------+---------
 Zhang  | Sun
 Li     | Mon
 Feng   | Tues
 Qian   | Wed
 Sun    | Thur
 Wu     | Fri
 Wang   | Sat
(7 rows)

postgres=# select min(weekday),max(weekday) from tb_duty ;
 min | max 
-----+-----
 Sun | Sat
(1 row)

每个枚举类型都是独立存储的，一个枚举类型占4个字节。

5.3 枚举类型的函数

函数 描述 例子 结果
enum_first(anyenum) anyenum代表一个创建的枚举类型，函数返回此枚举类型的第一个值 select enum_first(‘Mon’::week); Sun
enum_last(anyenul) 返回枚举类型的最后一个值 select enum_last(‘Mon’::week); Sat
enum_range(anyenum) 以一个有序的数组形式返回输入枚举类型的所有值 select enum_range(‘Mon’::week);
enum_range(anyenum,anyenum) 以一个有序数组返回在给定的两个枚举类型之间的范围，两个参数值必须时相同的枚举类型，如果第一个参数为null，其结果时从枚举类型的第一个值开始到给定的结束值，如果第二个参数为null，其结果将以枚举类型的给定的值开始到枚举类型的最后一个值结束（注意：给定的值也会包含在结果中 ） 例1：select enum_range(‘Mon’::week , ‘Fri’::week); 例2：select enum_range(null , ‘Fri’::week); 例3：select enum_range(‘Mon’::week , null); 例1：{Mon,Tues,Wed,Thur,Fri}；例2：{Sun,Mon,Tues,Wed,Thur,Fri}；例3：

表格中的函数enum_first、enum_last中，函数会忽略传入的参数值 Mon ，它只对声明的数据类型返回值，即使使用null也会得到相同的结果，例如：

 postgres=# select enum_first(null::week),enum_first('Mon'::week),enum_last(null::week),enum_last('Mon'::week);
 enum_first | enum_first | enum_last | enum_last 
------------+------------+-----------+-----------
 Sun        | Sun        | Sat       | Sat
(1 row)

6. 几何类型

6.1 几何类型概况

Postgresql主要支持一些二维的几何数据类型，最基本的时 “point” ，它是其他类型的基础。

几何类型如下：

类型名称 存储空间 描述 表现形式
point 16字节 平面中的点 (x,y)
line 32字节 直线 ((x1,y1),(x2,y2))
lseg 32字节 线段（有限长度） [(x1,y1),(x2,y2)]
box 32字节 矩形 ((x1,y1),(x2,y2))
path 16+16*n 闭合路径（与多边形类似） ((x1,y1),(x2,y2),…)
path 16+16*n 开放路径 [(x1,y1),(x2,y2),…]
polygon 40+16n字节 多边形（与闭合路径相似） ((x1,y1),(x2,y2),…)
circle 24字节 园 <(x,y),r>

6.2 几何类型的输入

6.2.1 输入格式：

类型名称 ‘值’ 或者 ‘值’::类型名称

示例：

点：

postgres=# select point '1,2';
 point 
-------
 (1,2)
(1 row)

postgres=# select '(1,2)'::point;
 point 
-------
 (1,2)
(1 row)

线段 ：

postgres=# select lseg '1,2,3,2';
     lseg      
---------------
 [(1,2),(3,2)]
(1 row)

postgres=# select lseg '(1,2),(3,2)';
  ?column?   
-------------
 [(1,2),(3,2)]
(1 row)

postgres=# select lseg '[(1,2),(3,2)]';
   ?column?    
---------------
 [(1,2),(3,2)]
(1 row)

postgres=# select '1,2,3,2'::lseg;
     lseg      
---------------
 [(1,2),(3,2)]
(1 row)

postgres=# select '(1,2),(3,2)'::lseg;
     lseg      
---------------
 [(1,2),(3,2)]
(1 row)

postgres=# select '[(1,2),(3,2)]'::lseg;
     lseg      
---------------
 [(1,2),(3,2)]
(1 row)

矩形：

postgres=# select box '1,1,2,2';
     box     
-------------
 (2,2),(1,1)
(1 row)

postgres=# select box '(1,1),(2,2)';
     box     
-------------
 (2,2),(1,1)
(1 row)

postgres=# select box '((1,1),(2,2))';
     box     
-------------
 (2,2),(1,1)
(1 row)

postgres=# select '((1,1),(2,2))'::box;
     box     
-------------
 (2,2),(1,1)
(1 row)

postgres=# select '1,1,2,2'::box;
     box     
-------------
 (2,2),(1,1)
(1 row)

注意：矩形不可以使用类似线段那种中括号输入方式，如：

postgres=# select box '[(1,1),(2,2)]';
2020-02-26 15:17:08.646 CST [49927] ERROR:  invalid input syntax for type box: "[(1,1),(2,2)]" at character 12
2020-02-26 15:17:08.646 CST [49927] STATEMENT:  select box '[(1,1),(2,2)]';
ERROR:  invalid input syntax for type box: "[(1,1),(2,2)]"
LINE 1: select box '[(1,1),(2,2)]';

路径：

postgres=# select path '1,1,2,2,3,3,4,4';
           path            
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select '1,1,2,2,3,3,4,4'::path;
           path            
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select path '(1,1),(2,2),(3,3),(4,4)';
           path            
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select '(1,1),(2,2),(3,3),(4,4)'::path;
           path            
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select path '((1,1),(2,2),(3,3),(4,4))';
           path            
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select '((1,1),(2,2),(3,3),(4,4))'::path;
           path            
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select path '[(1,1),(2,2),(3,3),(4,4)]';
           path            
---------------------------
 [(1,1),(2,2),(3,3),(4,4)]
(1 row)

postgres=# select '[(1,1),(2,2),(3,3),(4,4)]'::path;
           path            
---------------------------
 [(1,1),(2,2),(3,3),(4,4)]
(1 row)

注意：路径中使用方括号 “[ ]” 表示开放路径，使用圆括号 “( )" 表示闭合路径，闭合路径指最后一个点与第一个点时连接在一起的。

多边形：

postgres=# select polygon '1,1,2,2,3,3,4,4';
          polygon          
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select '1,1,2,2,3,3,4,4'::polygon;
          polygon          
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select polygon '(1,1),(2,2),(3,3),(4,4)';
          polygon          
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select '(1,1),(2,2),(3,3),(4,4)'::polygon;
          polygon          
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select polygon '((1,1),(2,2),(3,3),(4,4))';
          polygon          
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

postgres=# select '((1,1),(2,2),(3,3),(4,4))'::polygon;
          polygon          
---------------------------
 ((1,1),(2,2),(3,3),(4,4))
(1 row)

注意：多边形类型输入中不能使用中括号，例如：

postgres=# select '[(1,1),(2,2),(3,3),(4,4)]'::polygon;
2020-02-26 15:31:51.449 CST [49927] ERROR:  invalid input syntax for type polygon: "[(1,1),(2,2),(3,3),(4,4)]" at character 8
2020-02-26 15:31:51.449 CST [49927] STATEMENT:  select '[(1,1),(2,2),(3,3),(4,4)]'::polygon;
ERROR:  invalid input syntax for type polygon: "[(1,1),(2,2),(3,3),(4,4)]"
LINE 1: select '[(1,1),(2,2),(3,3),(4,4)]'::polygon;

圆形：

postgres=# select circle '1,1,5';
  circle   
-----------
 <(1,1),5>
(1 row)

postgres=# select circle '((1,1)5)';
  circle   
-----------
 <(1,1),5>
(1 row)

postgres=# select circle '<(1,1)5>';
  circle   
-----------
 <(1,1),5>
(1 row)

postgres=# select '1,1,5'::circle;
  circle   
-----------
 <(1,1),5>
(1 row)

postgres=# select '((1,1)5)'::circle;
  circle   
-----------
 <(1,1),5>
(1 row)

postgres=# select '<(1,1)5>'::circle;
  circle   
-----------
 <(1,1),5>
(1 row)

注意：圆形不能使用一下输入方式：

postgres=# select '(1,1),5'::circle;
2020-02-26 15:35:05.073 CST [49927] ERROR:  invalid input syntax for type circle: "(1,1),5" at character 8
2020-02-26 15:35:05.073 CST [49927] STATEMENT:  select '(1,1),5'::circle;
ERROR:  invalid input syntax for type circle: "(1,1),5"
LINE 1: select '(1,1),5'::circle;
               ^
postgres=# select circle '(1,1),5';
2020-02-26 15:35:16.025 CST [49927] ERROR:  invalid input syntax for type circle: "(1,1),5" at character 15
2020-02-26 15:35:16.025 CST [49927] STATEMENT:  select circle '(1,1),5';
ERROR:  invalid input syntax for type circle: "(1,1),5"
LINE 1: select circle '(1,1),5';

6.3 几何类型的操作符

Postgresql提供了丰富的几何类型的操作符，如下：

6.3.1 平移运算符：”+“、”-“

平移运算符：”+“、”-“ ， 缩放 / 旋转运算符：” * “、” / “

这四个运算符都是二次元运算符，运算符左侧值的类型可以时point、box、path、circle。运算符右侧值的类型只能时 point ，例如：

点和点之间的加减乘除相当于两个复数之间的加减乘除：

postgres=# select point '(1,2)' + point '(10,20)';
 ?column? 
----------
 (11,22)
(1 row)

postgres=# select point '(1,2)' - point '(10,20)';
 ?column? 
----------
 (-9,-18)
(1 row)

postgres=# select point '(1,2)' * point '(10,20)';
 ?column? 
----------
 (-30,40)
(1 row)

postgres=# select point '(1,2)' / point '(10,20)';
 ?column? 
----------
 (0.1,0)
(1 row)

矩形与点之间的运算：

postgres=# select box '((0,0),(1,1))' + point '(2,2)';
  ?column?   
-------------
 (3,3),(2,2)
(1 row)

postgres=# select box '((0,0),(1,1))' - point '(2,2)';
    ?column?     
-----------------
 (-1,-1),(-2,-2)
(1 row)

路径与点之间的运算：

postgres=# select path '(0,0),(1,1),(2,2)' + point '(2,2)';
      ?column?       
---------------------
 ((2,2),(3,3),(4,4))
(1 row)

postgres=# select path '(0,0),(1,1),(2,2)' - point '(2,2)';
        ?column?         
-------------------------
 ((-2,-2),(-1,-1),(0,0))
(1 row)

圆与点的运算：

postgres=# select circle '((0,0),1)' + point '(10,20)';
  ?column?   
-------------
 <(10,20),1>
(1 row)

postgres=# select circle '((0,0),1)' - point '(10,20)';
   ?column?    
---------------
 <(-10,-20),1>
(1 row)

对于乘法，如果乘数的y值为0，比如 point(x,0)，则相当于几何对象缩放了x倍，具体如下：

postgres=# select circle '((0,0),1)' - point '(10,20)';
   ?column?    
---------------
 <(-10,-20),1>
(1 row)

postgres=# select point '(1,2)' * point '(2,0)';
 ?column? 
----------
 (2,4)
(1 row)

postgres=# select point '(1,2)' * point '(3,0)';
 ?column? 
----------
 (3,6)
(1 row)

postgres=# select circle '((1,2),1)' * point '(3,0)';
 ?column?  
-----------
 <(3,6),3>
(1 row)

postgres=# select circle '((0,0),1)' * point '(3,0)';
 ?column?  
-----------
 <(0,0),3>
(1 row)

如果乘法为point(0,1)，则相当于几何对象逆时针旋转90度，如果乘数为point(0,-1)，则表示顺时针旋转90度，例如：

postgres=# select point '(1,2)' * point '(0,1)';
 ?column? 
----------
 (-2,1)
(1 row)

postgres=# select point '(1,2)' * point '(0,-1)';
 ?column? 
----------
 (2,-1)
(1 row)

postgres=# select circle '((0,0),1)' * point '(0,1)';
 ?column?  
-----------
 <(0,0),1>
(1 row)

postgres=# select circle '((1,1),1)' * point '(0,-1)';
  ?column?  
------------
 <(1,-1),1>
(1 row)

6.3.2 运算符 ”#“

用法：
（1）对于两个线段，计算出线段交点。
（2）对于两个矩形，计算出相交的矩形。
（3）对于路径或多边形，则计算出顶点数。

两个线段：

postgres=# select lseg '(0,0),(2,2)' # lseg '(0,2),(2,0)';
 ?column? 
----------
 (1,1)
(1 row)

如果两个线段不相交，返回空：

postgres=# select lseg '(0,0),(2,2)' # lseg '(3,2),(4,5)';
 ?column? 
----------
 
(1 row)

两个矩形：

postgres=# select box '(0,0),(2,2)' # box '(1,0),(3,1)';
  ?column?   
-------------
 (2,1),(1,0)
(1 row)

6.3.3 运算符 ”@-@“

一元运算符，参数类型只能是lseg（线段）、path（路径）。一般用于计算几何对象的长度，例如：

postgres=# select @-@ lseg '(1,0),(2,3)';
      ?column?      
--------------------
 3.1622776601683795
(1 row)

postgres=# select @-@ path '(1,0),(2,3),(5,6)';
      ?column?      
--------------------
 14.616020898215645
(1 row)

注意：开放式路径和闭合式路径的长度时不一样的，例如：

postgres=# select @-@ path '((1,0),(2,3),(5,6))';
      ?column?      
--------------------
 14.616020898215645
(1 row)

postgres=# select @-@ path '[(1,0),(2,3),(5,6)]';
     ?column?      
-------------------
 7.404918347287666
(1 row)

6.3.4 运算符 ”@@“

运算符 ”@@“ 为一元运算符，用于计算中心点，例如：

# 圆
postgres=# select @@ circle '((1,1),4)';
 ?column? 
----------
 (1,1)
(1 row)

# 矩形
postgres=# select @@ box '(0,0),(1,1)';
 ?column?  
-----------
 (0.5,0.5)
(1 row)

# 线段
postgres=# select @@ lseg '(0,0),(2,2)';
 ?column? 
----------
 (1,1)
(1 row)

6.3.5 运算符 ”##“

运算符 ”##“为二元运算符，用于计算两个几何对象上离的最近的点，例如：

postgres=# select point '(0,0)' ## lseg '(2,0),(0,2)';
 ?column? 
----------
 (1,1)
(1 row)

postgres=# select lseg '(0,0),(2,4)' ## lseg '(1,3),(4,5)';
  ?column?  
------------
 (1.75,3.5)
(1 row)

6.3.6 运算符 ”<->“

运算符 ”<->“为二元运算符，用于计算两个几何对象之间的间距，例如：

postgres=# select lseg '(0,0),(2,2)' <-> lseg '(2,3),(4,5)';
 ?column? 
----------
        1
(1 row)

postgres=# select circle '<(0,0),4>' <-> circle '<(6,7),2>';
     ?column?      
-------------------
 3.219544457292887
(1 row)

6.3.7 运算符 ”&&“

运算符 ”&&“ 为二元运算符，用于计算两个几何对象之间是否重叠，只要有一个共同点，则为真，否则返回false。例如：

postgres=# select box '(0,0),(2,2)' && box '(1,0),(4,3)';
 ?column? 
----------
 t
(1 row)

postgres=# select box '(0,0),(2,2)' && box '(3,4),(4,3)';
 ?column? 
----------
 f
(1 row)

6.3.8 判断两个对象相对位置的运算符

判断左右位置的4个运算符：
1.<< ： 是否严格在左
\2. >> ：是否严格在右
\3. &< ：没有延展到左边
\4. &> ：没有延展到右边

判断上下位置的6个运算符：
1.<<| ：严格在下
2.|>> ：严格在上
3.&<| ：没有延展到上面
4.|&> ：没有延展到下面
5.<^ ：在下面（允许接触）
6.>^ ：在上面（允许接触）

额外一些运算符：
1.?# ：是否相交
2.?- ：是否水平或水平对齐
3.?| ：是否竖直或竖直对齐
4.?-| ：两个对象是否垂直
5.?|| ：两个对象是否平行
6.@> ：是否包含
7.<@ ：包含或在其上

6.3.9 判断两个几何对象是否相同的运算符 ”~=“

对于多边形，如果表示的七点不一样，但是实现它们的是相同的两个多边形，只是位置不同而已，那么对应的判断方式如下：

postgres=# select polygon '((1,1),(0,0))' ~= polygon '((2,2),(3,3))';
 ?column? 
----------
 f
(1 row)

postgres=# select polygon '((1,1),(0,0))' ~= polygon '((0,0),(1,1))';
 ?column? 
----------
 t
(1 row)

postgres=# select box '(0,0),(1,1)' ~= box '(1,1),(0,0)'
postgres-# ;
 ?column? 
----------
 t
(1 row)

6.4 几何类型的函数

函数如下：

函数 返回类型 描述 例子 结果
area(object) double precision 计算几何对象面积 select area(box ‘(2,2),(4,4)’); 4
center(object) point 中心 select center(box ‘(2,2),(4,4)’); 3
diameter(circle) double precision 计算圆的直径 select diameter(circle ‘<(1,1),2>’) 4
height(box) double precision 矩形的高度 select height(box ‘(2,4),(5,9)’); 5
witdth(box) double precision 矩形的宽度 select width(box ‘(2,4),(5,9)’); 3
isclosed(path) blooean 是否是闭合路径 select isclosed(path ‘((1,1),(2,2),(3,3))’); t
isopen(path) blooean 是否是开发路径 select isclosed(path ‘((1,1),(2,2),(3,3))’); f
length(object) double precision 长度 select length(path ‘((1,1),(2,2),(3,3))’); 5.656854
npoints(path) int 计算路径上的点 select npoints(path ‘((1,1),(2,2),(3,3))’); 3
npoints(polygon) int 计算多边形上的点 select npoints(polygon ‘((1,1),(2,2),(3,3),(4,4))’); 4
pclose(path) path 把路径转换为闭合路径（不管参数路径是否是闭合） select pclose(path ‘[(1,1),(2,2),(3,3)]’); ((1,1),(2,2),(3,3))
popen(path) path 把路径转换为开放路径（不管参数路径是否是开放） select popen(path ‘((1,1),(2,2),(3,3))’); [(1,1),(2,2),(3,3)]
radius(circle) double precision 圆的半径 select radius(circle ‘<(1,1),3>’); 3

不同的几何类型还可以相互转换，转换函数如下：

函数 返回类型 描述
box(circle) box 将圆形转换为矩形
box(point,point) box 将两个点转换成矩形
box(polygon) box 将多边形转换成矩形
circle(box) circle 将矩形转换成圆
circle(point,double precision) circle 将圆心和半径转换为圆
circle(polygon) circle 将多边形转换成圆
lseg(box) lseg 将矩形转换为对角线线段
lseg(point,point) lseg 将两个点转化成线段
path(polygon) path 将多边形转换成路径
point(double precision,double precision) point 构建一个点
point(box) point 矩形的中心点
point(circle) point 圆的圆心
point(lseg) point 线段的中心点
point(polygon) point 多边形的中心点
polygon(box) polygon 将矩形转换成4个点的多边形
ploygon(circle) polygon 将圆形转换成12个点的多边形
polygon(npts,circle) polygon 将圆形转换成 npts 个点的多边形
polygon(path) polygon 将路径转换成多边形

7. 复合类型

在Postgresql中可以像C语言中的结构体一样定义一个复合型

7.1 复合类型的定义

如定义一个名为 “person” 的复合类型：

postgres=# create type person as (name text,age integer,sex boolean);
CREATE TYPE

通过以上语句可以看出复合类型的语法类似于create table，但是只能声明字段和类型，不能声明约束（如NOT NULL）。
其中关键词 AS 是关键词，没有 AS ，会出现语法错误。
复合类型创建完后就可以用此类型来创建表的列，例如：

postgres=# create table tb_complex(id int, people person);
CREATE TABLE

也可以在创建的函数中使用创建的复合类型作为参数。

7.2 复合类型的输入

例如：

postgres=# create type person as (name text,age integer,sex boolean);
CREATE TYPE

postgres=# create table tb_complex(id int, people person);
CREATE TABLE

postgres=# create table tb_complex(id int, people person);
CREATE TABLE
postgres=# insert into tb_complex values(1,'("张三",30,true)');
INSERT 0 1
postgres=# insert into tb_complex values(1,'("Alia",27,false)');
INSERT 0 1
postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (Alia,27,f)
(2 rows)

可见复合类型的值格式类似： ‘(val1,val2,…)’
使用的是单引号加圆括号的方式，在此格式括号里，可以在复合类型定义的字段值上放双引号进行却别（如字符类型），尤其是值本身包含逗号或者圆括号，则必须加双引号。
如果在复合类型的字段里放一个空值（NULL），当空值的是字符串类型的，要写一对双引号，如果是其他类型（如int或Boolean），就在列表里不写任何字符，例如：

postgres=# insert into tb_complex values(1,'("无名",,true)');
INSERT 0 1
postgres=# insert into tb_complex values(1,'("",20,false)');
INSERT 0 1

当然，也可以用关键词ROW表达式语法来构造复合类型的值，大多数场合，这种方法比直接使用字符串单引号的方法更为简单，不用操心多重引号需要转义导致的各种问题，例如：

postgres=# insert into tb_complex values(1,row('无',40,true));
INSERT 0 1

注意：使用ROW表达式来构造复合类型的值时，括号内的字符串类型就不需要双引号表示，不然插入会报语法错误。

7.3 复合类型的访问

访问复合类型，使用点和域从复合类型中选出一个字段显示，但是在字段前应该加上括号将复合类型名称括起来，避免SQL解析器混淆报错，例如：

postgres=# insert into tb_complex values(1,row('无',40,true));
INSERT 0 1
postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (Alia,27,f)
  3 | (无名,,t)
  4 | ("",20,f)
  5 | (无,40,t)
(5 rows)
               ^
postgres=# select (people).name from tb_complex ;
 name 
------
 张三
 Alia
 无名
 
 无
(5 rows)

或者加上表明，如下：

postgres=# select (tb_complex.people).name from tb_complex ;
 name 
------
 张三
 Alia
 无名
 
 无
(5 rows)

类似的查询方式适用任何需要从一个复合类型值中查询一个域，另外，要从一个返回复合类型值的函数中选取一个字段展示，则需要如下方式：

select (my_func(...)).field from table_name

注意：函数外的括号一定要加，不然会产生语法错误！！

7.4 复合类型的修改

1.按照一般表数据的更新方式：

postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (Alia,27,f)
  3 | (无名,,t)
  4 | ("",20,f)
  5 | (无,40,t)
(5 rows)

postgres=# update tb_complex set people = ROW('Lina',22,false) where (people).name='Alia';
UPDATE 1
postgres=# select * from tb_complex ;
 id |   people    
----+-------------
  1 | (张三,30,t)
  2 | (无名,,t)
  3 | ("",20,f)
  4 | (无,40,t)
  5 | (Lina,22,f)
(5 rows)

2.单独更新复合类型中的某一个字段：

postgres=# update tb_complex set people.age = 35 where (people).name='张三';
UPDATE 1

注意：在更新语句中指定更新复合类型列中单个字段，复合类型列名不需要加括号。但是在where条件中使用复合类型中单个列查询时必须加括号，不然会报语法错误。

3.既然单个列可以更新，那么复合类型中单个列也可以进行单独插入，如果没指定的列则为空值，例如：

postgres=# insert into tb_complex(id,people.name,people.sex) values (6,'scott',true);
INSERT 0 1

建议在进行复合类型值插入时使用ROW表达式，避免需要输入类似 双引号（“）或者反斜杠时要使用多层转义的麻烦，避免出入出错。

8. 特殊类型（UUID）

8.1 UUID类型

UUID（Universally Unique Identifiers）用于存储一个UUID，UUID定义是一个128bit的数字。
Postgresql提供了UUID类型的比较运算符，例如：

postgres=# select uuid '1b34eaba-0d59-11e4-bf51-dc85de4d74f3' < uuid '1e0e95b0-0d59-11e4-bf51-dc85de4d74f3';
 ?column? 
----------
 t
(1 row)

虽然在Postgresql中没有提供直接的UUID生成函数，但是在contrib下的uuid-ossp模块提供了产生UUID函数。可以通过一下方式来生成UUID：
1.先安装系统os依赖包uuid、uuid-devel：

yum -y install uuid uuid-devel

安装完成可能 uuid-devel包没有，我使用Centos 6.5配置本地yum源（没有互联网）在使用yum安装时没有uuid-devel依赖包，因此我从网上下载了一个rpm包（参考网址：https://centos.pkgs.org/6/centos-x86_64/uuid-devel-1.6.1-10.el6.x86_64.rpm.html），然后使用rpm进行安装：

rpm -ivh uuid-devel-1.6.1-10.el6.x86_64.rpm

2.依赖包安装完成后，进行预编译（postgresql的源码包我还留着，进到源码包后再次进行编译）：

./configure --prefix=/data/pg12.1 --with-uuid=ossp

注意：我PG安装目录是/data/pg12.1

3.进入源码包的contrb文件夹下的uuid-ossp目录下，进行gmake && gmake install ：

cd contrib/uuid-ossp
gmake clean
gmake
gmake install

注意：我在安装PG时用的make install-world方式安装了contrib下所有插件，因为没有依赖包uuid、uuid-devel，所以没有成功安装uuid-ossp，所以我需要gmake clean清除一下。顺便说一下，如果在安装PG时安装了依赖包，那么就不需要在进行1~3步骤。直接进行第四步。如果在进行第4步时报如下错误，则说明没有成功安装uuid-ossp插件，还需要从第1步开始：
postgres=# create extension “uuid-ossp”;
ERROR: could not open extension control file “/data/pg12.1/share/postgresql/extension/uuid-ossp.control”: No such file or directory

4.登录postgresql，执行create extension，然后可以通过uuid_generate_v4()生成uuid：

[postgres@local130 ~]$ psql -U postgres -W -d postgres
psql (12.7)
Type "help" for help.

xzxt=# create extension "uuid-ossp";
CREATE EXTENSION
xzxt=# select uuid_generate_v4();
           uuid_generate_v4           
--------------------------------------
 b1cd147b-d39e-4805-8c09-dcfb7cf19737
(1 row)

注意：需要在哪个库下使用uuid就在登陆哪个库下执行，一般登陆时指定-d参数到指定的库下执行create extension，会在指定的库下的public模式下创建，并可以使用uuid，执行后会在指定的库的public模式的function中看到uuid开头的函数：

xzxt=# \df
                                                                         List of functions
 Schema |        Name        | Result data type  |                                           Argument data types                                            | Type 
--------+--------------------+-------------------+----------------------------------------------------------------------------------------------------------+------
 public | fn_xxcj_ngzbhzh    | character varying | unitcode character varying, tblname character varying, lrsj character varying, headstr character varying | func
 public | fn_xxcj_xxzjbh     | character varying | unitcode character varying, strdate character varying                                                    | func
 public | uuid_generate_v1   | uuid              |                                                                                                          | func
 public | uuid_generate_v1mc | uuid              |                                                                                                          | func
 public | uuid_generate_v3   | uuid              | namespace uuid, name text                                                                                | func
 public | uuid_generate_v4   | uuid              |                                                                                                          | func
 public | uuid_generate_v5   | uuid              | namespace uuid, name text                                                                                | func
 public | uuid_nil           | uuid              |                                                                                                          | func
 public | uuid_ns_dns        | uuid              |                                                                                                          | func
 public | uuid_ns_oid        | uuid              |                                                                                                          | func
 public | uuid_ns_url        | uuid              |                                                                                                          | func
 public | uuid_ns_x500       | uuid              |                                                                                                          | func
(12 rows)

8.2 pg_lsn类型

pg_lsn类型时PG9.1之后提供的表示LSN（Log Squence Number）的一种数据类型，LSN表示WAL日志的位置，在一些记录WAL日志信息的系统表中某些字段类型可以用pg_lsn类型。

9. Range类型

9.1 Range类型介绍

Range类型是PG 9.2之后开始出现的一种特有的类型，用于表现范围，如一个整数的范围、一个时间的范围，而范围底下的基本类型（如整数、时间）则被成为Range类型的subtype。
Range数据类型可以更快的在范围条件查询中检索到数据。
例如：
某个IP地址库记录了每个地区的IP地址的范围，现在需要查询指定IP地址在那个地区，IP库记录表为如下结构：

create table tb_ip_range(
begin_ip inet,
end_ip inet,
area text,
sp text);

现在查询IP地址192.188.17.120在那个地区，对应的查询语句如下：
select * from tb_ip_range where begin_ip <= ‘192.188.17.120’::inet and end_id >= ‘192.188.17.120’::inet
虽然在表上的begin_ip和end_ip列都创建了索引，但是在Postgresql中，虽然SQL语句会走索引，但是查询方式时分别扫描两个索引建位图，然后通过位图进行and操作，对比SQL语句的执行计划，索引的范围扫描还不是最高效的。
Range类型时通过创建空间索引的方式执行SQL语句，例如：
创建类似的IP地址库表：

现在查询IP地址192.188.17.120在那个地区，对应的查询语句如下：
select * from tb_ip_range where begin_ip <= ‘192.188.17.120’::inet and end_id >= ‘192.188.17.120’::inet
虽然在表上的begin_ip和end_ip列都创建了索引，但是在Postgresql中，虽然SQL语句会走索引，但是查询方式时分别扫描两个索引建位图，然后通过位图进行and操作，对比SQL语句的执行计划，索引的范围扫描还不是最高效的。
Range类型时通过创建空间索引的方式执行SQL语句，例如：
创建类似的IP地址库表：

1.创建Range类型：

create type inetrange as Range (subtype=inet);

2.创建IP范围表：

create table tb_ip_range(
ip_range inetrange，
area text,
sp text);

3.在ip_range列上创建索引，然后通过包含运算符 ”@>“ 查找对应的数据：

select * from tb_ip_range where ip_range @> ‘192.188.17.120’::inet

查看对应的SQL语句的执行计划可以明显看到性能提高很多。

9.2 Range类型的创建

在Postgresql中已经内置了一些常用的Range类型，可以不用执行create type xxx as range来创建：

int4range : 4字节整数的范围类型
int8range : 8字节大整数的范围类型
numrange : numeric的范围类型
tsrange : 无时区的时间戳范围类型
tstzrange : 带时区的时间戳范围类型
daterange : 日期的范围类型

示例：

# 时间范围
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

# 整数是否包含在范围内
postgres=# select int4range(10,20) @> 3;
 ?column? 
----------
 f
(1 row)

postgres=# select int4range(10,20) @> 11;
 ?column? 
----------
 t
(1 row)

# 判断两个范围是否有交集，有true，没有false
postgres=# select numrange(11.1,22.2) && numrange(20.0,30.0);
 ?column? 
----------
 t
(1 row)

postgres=# select numrange(11.1,22.2) && numrange(23.0,30.0);
 ?column? 
----------
 f
(1 row)

# 使用uper提取范围的最大边界
postgres=# select upper(int8range(15,30));
 upper 
-------
    30
(1 row)

postgres=# select upper(int8range(15,35));
 upper 
-------
    35
(1 row)

# 提取两个范围共同的交集
postgres=# select int4range(10, 20) * int4range(15, 25);
 ?column? 
----------
 [15,20)
(1 row)

postgres=# select int4range(10, 30) * int4range(15, 25);
 ?column? 
----------
 [15,25)
(1 row)


postgres=# select isempty(numrange(1,5));
 isempty 
---------
 f
(1 row)

如果以上内置的range类型不符合你的要求，可以使用CREATE TYPE来创建自定义的Range类型，语法如下：

CREATE TYPE name AS RANGE(
SUBTYPE = subtype
[ , SUBYTPE_OPCLASS = subtype_operator_class ]
[ , CLLATION = collation ]
[ , CANONICAL = canonical_function ]
[ , SUBTYPE_DIFF = subtype_diff_function ]

说明：
1.SUBTYPE = subtype：指定子类型
2.SUBYTPE_OPCLASS = subtype_operator_class：指定子类型的操作符
3.CLLATION = collation：指定排序规则
4.CANONICAL = canonical_function：如果要创建一个稀疏的Range类型，而不是一个连续的Range类型，那就定义此函数
5.SUBTYPE_DIFF = subtype_diff_function：定义子类型的差别函数

示例：

CREATE TYPE floatrange AS RANGE(
SUBTYPE=float8;
SUBTYPE_DIFF=float8mi
);

9.3 Range类型的输入

Range类型的输入格式：
’(lower-bound,upper-bound)‘
‘(lower-bound,upper-bound]’
’[lower-bound,upper-bound)‘
‘[lower-bound,upper-bound]’
’empty‘

其中，”(“ 和 ”)“ 表示定义的范围不包括此元素，”[“ 和 ”]“ 表示定义的范围包括此元素，’empty‘ 表示空，空表示范围内不包含任何东西。
示例：

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;

每个范围类型都有一个与范围类型同名的构造函数。使用构造函数通常比编写范围文字常量更方便，因为它避免了对绑定值的额外引用。构造函数接受两个或三个参数。两个参数的形式以标准形式构造一个范围(下界包括，上界排除)，而三个参数的形式构造一个范围，其边界由第三个参数指定。第三个参数必须是字符串“()”、“()”、“()”或“[]”中的一个。例如:

-- The full form is: lower bound, upper bound, and text argument indicating inclusivity/exclusivity of bounds.
postgres=# select numrange(1.0, 14.0, '(]');
  numrange  
------------
 (1.0,14.0]
(1 row)

-- If the third argument is omitted, '[)' is assumed.
postgres=# select numrange(1.0, 14.0);
  numrange  
------------
 [1.0,14.0)
(1 row)

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
postgres=# select int8range(1, 14, '(]');
 int8range 
-----------
 [2,15)
(1 row)

Range类型还可以表示极值的区间，例如：

1.表示从1开始到int4可以表示的最大值：

postgres=# select '[1,)'::int4range;
 int4range 
-----------
 [1,)
(1 row)

2.表示从int4可以表示的最小值到1的范围：

postgres=# select '[,1)'::int4range;
 int4range 
-----------
 (,1)
(1 row)

3.对于numrange范围，可以表示无穷大或者无穷小，例如，从1到无穷大与负无穷到1：

postgres=# select '[1,)'::numrange;
 numrange 
----------
 [1,)
(1 row)

postgres=# select '[,1)'::numrange;
 numrange 
----------
 (,1)
(1 row)

注意：与numrange不同的是，int4range里不是无穷大或者负无穷，因为int4有具体的范围。

9.4 Range类型的操作符和函数

1.Range类型支持的操作符：

操作符 描述 例子 结果
= 等于 select int4range’[1,5)’ = ‘[1,4]’::int4range; t
<> 不等于 select numrange(1.1,1.2) <> numrange(1.1,1.3); t
< 小于 select int4range ‘[1,10)’ < int4range’[2,3)’; t
> 大于 select int4range ‘[2,3)’ > int4range’[1,100)’; t
<= 小于等于 select int4range’[2,3)’<= int4range’[1,2)’; f
>= 大于等于 select int4range’[2,3)’>= int4range’[1,2)’; t
@> 包含（左边包含了右边） select int4range’[1,3)’ @> int4range’[1,2)’; t
<@ 被包含（右边包含左边） select int4range’[1,2)’ <@ int4range’[1,4)’; t
&& 重叠（两个范围有交集） select int4range’[1,2)’ && int4range’[1,4)’; t
<< 严格在左（没有重叠值） select int4range’[1,2)’ << int4range’[2,4)’; t
>> 严格在右 select int4range’[2,4)’ >> int4range’[1,2)’; t
&< 没有扩展到右边 select int4range’[1,2)’ &< int4range’[1,4)’; t
&> 没有扩展到左边 select int4range’[1,2)’ &> int4range’[1,4)’; t
-l- 链接在一起（值没有交集） select int4range’[1,2)’ - - int4range’[2,4)’;
+ union(将两个范围合并在一起) select int4range’[1,2)’ + int4range’[2,4)’; [1,4)
* intersection select int4range’[1,4)’ * int4range’[2,5)’; [2,4)
- difference select int4range’[1,4)’ - int4range’[2,5)’; [1,2)

2.Range类型的函数：

1.lower(anyrange) : 获得范围的起始值

postgres=# select lower(int4range '[11,22)');
 lower 
-------
    11
(1 row)

postgres=# select lower(int4range '[11,22)') is null;;
 ?column? 
----------
 f
(1 row)

postgres=# select lower(int4range '[11,22)') is not null;
 ?column? 
----------
 t
(1 row)

2.upper(anyrange)：获得范围的结束值

postgres=# select upper(int4range '[11,22)');
 upper 
-------
    22
(1 row)

postgres=# select upper(int4range '[11,34]');
 upper 
-------
    35
(1 row)

注意：获取的范围结束值是不包含在范围内的最大值！

3.isempty(anyrange)：是否是空范围

postgres=# select isempty(int4range'empty');
 isempty 
---------
 t
(1 row)

postgres=# select isempty(int4range'(,)');
 isempty 
---------
 f
(1 row)

postgres=# select isempty(int4range'(1,1)');
 isempty 
---------
 t
(1 row)

postgres=# select isempty(int4range'[1,2)');
 isempty 
---------
 f
(1 row)

4.lower_inc(anyrange)：起始值是否在范围内

postgres=# select lower_inc(int4range'(1,2)');
 lower_inc 
-----------
 f
(1 row)

postgres=# select lower_inc(int4range'[1,2)');
 lower_inc 
-----------
 t
(1 row)

postgres=# select lower_inc(int4range'(1,4]');
 lower_inc 
-----------
 t
(1 row)

5.upper_inc(anyrange)：结束值是否在范围内

postgres=# select upper_inc(int4range'(1,2)');
 upper_inc 
-----------
 f
(1 row)

postgres=# select upper_inc(int4range'(1,3]');
 upper_inc 
-----------
 f
(1 row)

6.lower_inf(anyrange)：起始值是否是一个无穷值
7.upper_inf(anyrange)：结束值是否是一个无穷值

9.5 Range类型的索引和约束

1.索引：

可以为范围类型的表列创建GiST和SP-GiST索引。例如，创建一个GiST索引:

CREATE INDEX reservation_idx ON reservation USING GIST (during);

索引创建后可以在SQL语句中使用Range类型的操作符来进行索引数据检索。

此外，可以为范围类型的表列创建B-tree和hash索引。对于这些索引类型，基本上惟一有用的范围操作是相等操作。有一个为范围值定义的B-tree排序，具有相应的<和>操作符，但是排序是相当随意的，在现实世界中通常没有用处。Range类型的b -树和散列支持主要是为了允许在查询内部进行排序和散列，而不是创建实际的索引。

2.约束

在Range类型的列上一般创建排除约束，让其范围总是不重叠的，例如：

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING GIST (during WITH &&)
);

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

由以上例子看出，列上创建了排他约束，插入数据时，如果与原有数据范围有重叠时就会报错。

如果有一个两个列的表，第一个列时普通类型是 id ，第二个时Range类型是 during ，插入数据时判断，如果 id 值相等时，如果Range列范围有重叠，插入报错，如果 id 值不相等，那么Range列即使范围重叠也不报错，可以进行数据插入。这时就需要Postgresql的扩展模块 btree_gist 来实现：

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    id intege,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

INSERT INTO room_reservation VALUES
    (1, '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO room_reservation VALUES
    (1, '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

INSERT INTO room_reservation VALUES
    (2, '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1

标签：PostgreSQL,postgres,timestamp,数据类型,2020,类型,select,row
From： https://www.cnblogs.com/zreo2home/p/18353395

相关文章

  • PostgreSQL的物理备份
    PostgreSQL的物理备份1.简单了解1.1pg_basebackup备份1.1.1简介pg_basebackup是从postgresql9.1版本开始提供的一个方便基础备份的工具。pg_basebackup用于对正在运行的PostgreSQL数据库集群进行基本备份。备份是在不影响数据库的其他客户端的情况下进行的，并且可以用于时......
  • PostgreSQL-连续归档和时间点恢复
    PostgreSQL-连续归档和时间点恢复(PITR)PostgreSQL始终在群集数据目录的pg_wal/子目录中维护一个预写日志(WAL)。该日志记录对数据库数据文件所做的每一项更改。此日志主要用于崩溃安全性：如果系统崩溃，可以通过“重放”自上次检查点以来所做的日志条目来将数据库恢复到一致性......
  • PostgreSQL的同步异步复制简介及互相转换
    PostgreSQL的同步/异步复制简介及互相转换一、介绍1.概念1.1.同步流复制同步流复制就是当主库发生变化，比如有一条DML语句产生了WAL日志后，通过后台进程传送到备库，备库必须要应用这个日志，然后向主库返回一个成功应用的信号，主库才可以成功的commit；否则主库会一直等待到备库成功......
  • PostgreSQL的一主一从集群搭建部署
    1.PostgreSQL的一主一从集群搭建部署1.目标​ 掌握Postgresql数据库主从部署搭建配置2.脉络部署规划PostgreSQL单节点安装PostgreSQL主从部署配置主从同步验证3.部署搭建3.1基础准备1.1创建两台虚拟机虚拟机名IPhostname主从划分test11192.168.198......
  • 搭建PostgreSQL高可用集群（基于Patroni+Etcd）
    搭建PostgreSQL高可用集群（基于Patroni+Etcd）1.主机环境准备节点名称主机名网卡IP地址OS安装软件角色作用PGSQL1pgsql1ens33192.168.198.162CentOS7PostgreSQL、ETCD、Patroni主数据库PGSQL2pgsql2ens33192.168.198.163CentOS7PostgreSQL、ETCD、Patr......
  • PostgreSQL15.6源码编译安装
    1.postgres数据库源码下载地址，自己选择对应版本，我这里是15.6https://www.postgresql.org/ftp/source/2.编译cd/data/make/postgresql-15.6./configure--prefix=/pgdata/postgres15make-j$(nproc)#加快编译速度，自动获取对应cpu核心数makeinstall3.创建postgres用户......
  • PostgreSQL 读取表主键和唯一键的SQL
    给定表名,读取对应的约束字段(主键,唯一键)SELECTtc.* FROMinformation_schema.table_constraintstc JOINinformation_schema.constraint_column_usageASccuUSING(constraint_schema,constraint_name) JOINinformation_schema.columnsAScONc.table_schem......
  • Arrays类、Random类和包装类（8大基本数据类型）的用法
    1、Arrays:是java提供专门针对数据做操作的工具类，该类没有构造方法，且方法都是静态的  成员方法：    publicstaticStringtoString(int[]a)将任意一个数组中的所有元素以字符串的形式拼接返回    publicstaticvoidsort(int[]a)冒泡排序  ......
  • Java基础语法：变量与数据类型
    变量1.概念：变量是用来存储数据的命名容器并在程序的不同部分使用它。2.语法：数据类型变量名称=初始值;可以这样理解：**数据类型：**鱼缸**变量名称：**鱼的名字**变量的值：**鱼缸里装的鱼或数量数量数据类型基本数据类型-整型：1.byte:8位，有符号整数（-128到127）2.short:......
  • Java中的8种基本数据类型及其存储方式
    文章目录基本数据类型存储方式整型数据浮点型数据char类型数据布尔类型数据其他数据类型的转换自动转换强制转换基本数据类型Java属于C类语言，有8种数据类型数据类型byteshortintlongfloatdoublecharboolean数据大小8bit16bit32bit64bit32bit64bit8bit/24bit/32bit......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99