唐抉的个人博客

PgSQL数据库学习笔记（二）

数据库 PgSQL
字数统计: 2.8k阅读时长: 11 min
2022/10/14

PostgreSQL数据类型

数值类型

数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。

下表列出了PgSQL所支持的数值类型：

名字 存储长度 描述 范围
smallint 2 字节 小范围整数 -32768 到 +32767
integer 4 字节 常用的整数 -2147483648 到 +2147483647
bigint 8 字节 大范围整数 -9223372036854775808 到 +9223372036854775807
decimal 可变长 用户指定的精度，精确 小数点前 131072 位；小数点后 16383 位
numeric 可变长 用户指定的精度，精确 小数点前 131072 位；小数点后 16383 位
real 4 字节 可变精度，不精确 6 位十进制数字精度
double precision 8 字节 可变精度，不精确 15 位十进制数字精度
smallserial 2 字节 自增的小范围整数 1 到 32767
serial 4 字节 自增整数 1 到 2147483647
bigserial 8 字节 自增的大范围整数 1 到 9223372036854775807

货币类型

money类型存储带有固定小数精度的货币金额。numeric、int和bigint类型的值可以转换为money。

名字 存储容量 描述 范围
money 8 字节 货币金额 -92233720368547758.08 到 +92233720368547758.07

字符类型

下表列出了PgSQL所支持的字符类型：

名字 描述
character varying(n),varchar(n) 变长，有长度限制
character(n), char(n) f定长,不足补空白
text 变长，无长度限制

日期/时间类型

下表列出了PgSQL所支持的日期和时间类型：

名字 存储空间 描述 最低值 最高值 分辨率
timestamp [ ( p ) ] [ without time zone ] 8 字节 日期和时间(无时区) 4713 BC 294276 AD 1 毫秒 / 14 位
timestamp [ ( p ) ] with time zone 8 字节 日期和时间，有时区 4713 BC 294276 AD 1 毫秒 / 14 位
date 4 字节 只用于日期 4713 BC 5874897 AD 1 天
time [ ( p ) ] [ without time zone ] 8 字节 只用于一日内时间 00:00:00 24:00:00 1 毫秒 / 14 位
time [ ( p ) ] with time zone 12 字节 只用于一日内时间，带时区 00:00:00+1459 24:00:00-1459 1 毫秒 / 14 位
interval [ fields ] [ ( p ) ] 12 字节 时间间隔 -178000000 年 178000000 年 1 毫秒 / 14 位

布尔类型

boolean有true或false两个状态，第三种未知状态（unknown）用NULL表示。

名称 存储格式 描述
boolean 1 字节 true/false

枚举类型

PgSQL种的枚举类型类似于C语言中的enum类型，包含静态和值的有序集合的数据类型。与其他类型不同的是枚举类型需要使用create type命令创建。枚举类型一旦创建，便可用于变和函数定义。

1
create type mood as enum('sad','ok','happy')

几何类型

几何类型表示二维的平面物体。

名字 存储空间 说明 表现形式
point 16 字节 平面中的点 (x,y)
line 32 字节 (无穷)直线(未完全实现) ( ( x1,y1 ) , ( x2,y2 ) )
lseg 32 字节 (有限)线段 ( ( x1,y1 ), ( x2,y2 ) )
box 32 字节 矩形 ( ( x1,y1 ), ( x2,y2 ) )
path 16+16n 字节 闭合路径(与多边形类似) ( ( x1,y1 ), ... )
path 16+16n 字节 开放路径 [ ( x1,y1 ) , ... ]
polygon 40+16n 字节 多边形(与闭合路径相似) ( ( x1,y1 ) , ... )
circle 24 字节 圆 < ( x,y ) , r > (圆心和半径)

网络地址类型

PgSQL提供用于存储IPv4、IPv6、MAC地址的数据类型。在对inet或cidr数据类型进行排序时，IPv4地址总是排在IPv6前面，包括那些封装或是映射在IPv6地址里的IPv4地址。

名字 存储空间 描述
cidr 7 或 19 字节 IPv4 或 IPv6 网络
inet 7 或 19 字节 IPv4 或 IPv6 主机和网络
macaddr 6 字节 MAC 地址

位串类型

位串是一串1和0的字符串，可以用于存储和直观化位掩码。

名字 描述
bit(n) 必须准确匹配长度n(n位正整数)
bit var ying(n) 最长n的变长类型

文本搜索类型

全文检索即通过自然语言文档的集合来找到那些匹配一个查询的检索。

名字 描述
tsvector 其值是一个无重复值的lexemes排序列表，即一些同一个词的不同变种的标准化
tsquery 存储用于检索的词汇，且使用布尔操作符&(AND),|(OR)和!(NOT)来组合它们，括号用来强调操作符的分组。

UUID类型

uuid 数据类型用来存储 RFC 4122，ISO/IEF 9834-8:2005 以及相关标准定义的通用唯一标识符（UUID）。在一些系统里也叫GUID。这个标识符是一个由算法产生的 128 位标识符，使它不可能在已知使用相同算法的模块中和其他方式产生的标识符相同。 其用小写的十六进制数字序列表示。

XML类型

要使用这个数据类型，编译时必须使用configure --with -libxml。

xmlvalue IS DOCUMENT 表达式可以用来判断一个特定的 xml 值是一个完整的文件还是内容片段。

使用xmlparse来从字符数据产生xml类型的值：

1
2
XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

json类型

json 数据类型可以用来存储 JSON（JavaScript Object Notation）数据， 这样的数据也可以存储为 text，但是 json 数据类型更有利于检查每个存储的数值是可用的 JSON 值。

此外还有相关的函数来处理 json 数据：

实例 实例结果
array_to_json('{ {1,5},{99,100} }'::int[]) [ [1,5],[99,100] ]
row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}

数组类型

数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

声明数组：

1
2
3
4
5
6
CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]，
    pay_by_quarter integer ARRAY[4]
);

插入值：

插入值使用花括号 {}，元素在 {} 使用逗号隔开：

1
2
3
4
INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{ { "meeting", "lunch" }, { "training", "presentation" } }');

访问数组：

数组的下标数字是写在方括号里的。

1
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

修改数组：

1
2
3
4
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

数组中检索：

要搜索一个数组中的数值，就必须检查该数组的每一个值。

1
2
3
4
5
6
7
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

复合类型

复合类型表示一行或一条记录的结构，它实际上只是一个字段名和它们的数据类型的列表。复合类型可以实现建表时可以自定义字段。

声明复合类型的语法类似于create table，只是这里只可以声明字段名字和类型，定义类型后就可以用它来创建表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);
CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

复合类型输入

要用文本常量属性复合类型值，在圆括号里包围字段值并用逗号分隔他们。可以在任何字段周围放上双引号，若值本身包含逗号或圆括号，则必须要用双引号括起来。

复合类型常量的一般格式如下：

1
2
'( value1 , value2 , ... )'
'("fuzzy dice",42,1.99)'

访问复合类型

若要访问复合类型字段的一个域，则必须要用圆括号把域的名字括起来：

1
2
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;#需要用到表名时的写法

范围类型

范围类型代表这某一元素类型在一定范围内的值。

PostgreSQL 内置的范围类型有：

  • integer的范围：int4range
  • bigint的范围：int8range
  • numeric的范围：numrange
  • timestamp without time zone的范围：tsrange
  • timestamp with time zone的范围：tstzrange
  • date的范围：daterange

此外也可以定义自己的范围类型：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
SELECT isempty(numrange(1, 5));

范围值的输入必须遵循下面的格式：()、(]、[)、[]和空。圆括号或者方括号显示下边界和上边界是不包含的还是包含的。注意最后的格式是 空，代表着一个空的范围（一个不含有值的范围）。

1
2
3
4
5
6
7
8
9
10
11
-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;

对象标识符类型

PgSQL 在内部使用对象标识符(OID)作为各种系统表的主键。

名字 引用 描述 数值例子
oid 任意 数字化的对象标识符 564182
regproc pg_proc 函数名字 sum
regprocedure pg_proc 带参数类型的函数 sum(int4)
regoper pg_operator 操作符名 +
regoperator pg_operator 带参数类型的操作符 *(integer,integer) 或 -(NONE,integer)
regclass pg_class 关系名 pg_type
regtype pg_type 数据类型名 integer
regconfig pg_ts_config 文本搜索配置 english
regdictionary pg_ts_dict 文本搜索字典 simple

伪类型

PgSQL类型系统包含一系列特殊用途的条目， 它们按照类别来说叫做伪类型。伪类型不能作为字段的数据类型， 但是它可以用于声明一个函数的参数或者结果类型。 伪类型在一个函数不只是简单地接受并返回某种SQL 数据类型的情况下很有用。

下表列出了所有的伪类型：

名字 描述
any 表示一个函数接受任何输入数据类型。
anyelement 表示一个函数接受任何数据类型。
anyarray 表示一个函数接受任意数组数据类型。
anynonarray 表示一个函数接受任意非数组数据类型。
anyenum 表示一个函数接受任意枚举数据类型。
anyrange 表示一个函数接受任意范围数据类型。
cstring 表示一个函数接受或者返回一个空结尾的 C 字符串。
internal 表示一个函数接受或者返回一种服务器内部的数据类型。
language_handler 一个过程语言调用处理器声明为返回language_handler。
fdw_handler 一个外部数据封装器声明为返回fdw_handler。
record 标识一个函数返回一个未声明的行类型。
trigger 一个触发器函数声明为返回trigger。
void 表示一个函数不返回数值。
opaque 一个已经过时的类型，以前用于所有上面这些用途。

原文作者：唐抉

原文链接：http://tangmenjue.top/2022/10/14/PgSQL%E6%95%B0%E6%8D%AE%E5%BA%93%E5%AD%A6%E4%B9%A0%E7%AC%94%E8%AE%B0%EF%BC%88%E4%BA%8C%EF%BC%89/

发表日期：October 14th 2022, 4:08:18 pm

更新日期：October 14th 2022, 4:08:18 pm

版权声明： © 2020-2023 本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. PostgreSQL数据类型
    1. 1.1. 数值类型
    2. 1.2. 货币类型
    3. 1.3. 字符类型
    4. 1.4. 日期/时间类型
    5. 1.5. 布尔类型
    6. 1.6. 枚举类型
    7. 1.7. 几何类型
    8. 1.8. 网络地址类型
    9. 1.9. 位串类型
    10. 1.10. 文本搜索类型
    11. 1.11. UUID类型
    12. 1.12. XML类型
    13. 1.13. json类型
    14. 1.14. 数组类型
    15. 1.15. 复合类型
      1. 1.15.1. 复合类型输入
      2. 1.15.2. 访问复合类型
    16. 1.16. 范围类型
    17. 1.17. 对象标识符类型
    18. 1.18. 伪类型
Total : 62
2023
2022
2020
CSS3 CentOS 7 Linux 网络管理 Docker 应用部署 数据库工具 Flask Web 后端 Web框架 Kubernetes 可视化 大屏设计 Lottie 数据库 PgSQL Vue Vue.js 前端 YAML Redis 闪退 Elasticsearch JavaScript python 企业微信 应用开发 异步IO CSS HTML 结构性思维 XAMPP 报错 设计模式
CSS3 Linux Docker 数据库工具 Web框架 大屏设计 操作系统 学习笔记 Kubernetes 可视化 数据库 Vue 前端 YAML edge JavaScript Elasticsearch Lottie 企业微信 python 前端框架 Redis 应用开发 异步IO XAMPP