PostgreSQL入门之数据类型(2)

最新推荐文章于 2024-05-08 16:02:39 发布

Darion@pg

最新推荐文章于 2024-05-08 16:02:39 发布

阅读量865

点赞数

分类专栏： PostgreSQL入门文章标签： postgresql sql

本文链接：https://blog.csdn.net/qq_39727113/article/details/105371119

版权

PostgreSQL入门专栏收录该内容

9 篇文章 0 订阅

订阅专栏

7、枚举类型

枚举类型是一个包含静态和值得有序集合的数据类型。

PostgreSQL中的枚举类型类似于C语言中的enum。

需要使用CREATE TYPE命令创建

如下例子

创建星期枚举类型：

CREATE TYPE week AS enum ('Mon','Tue','Wed',

'Thu','Fri','Sat','Sun');

创建表列类型为枚举week

CREATE TABLE t_week(id serial,create_mood week);

插入数据：由于字段create_mood数据类型为枚举类

型，则字段的值只能为枚举类型week设定的值。

INSERT INTO t_week(create_mood) values('Mon');

如上插入的值为Mon,是枚举中的值，可插入成功。如下,插入的值MMM不是枚举week中的值，则会报错：

INSERT INTO t_week(create_mood) values('MMM');

ERROR: 22P02: invalid input value for enum week: "MMM"

8、JSON类型

PostgreSQL支持非结构数据类型json、jsonb。

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

JSON的基础类型有：

JSON 基本类型	PostgreSQL类型	注释
string	text	不允许\u0000，如果数据库编码不是 UTF8，非 ASCII Unicode 转义也是这样
number	numeric	不允许NaN 和 infinity值
boolean	boolean	只接受小写true和false拼写
null	(无)	SQL NULL是一个不同的概念

此外还有相关的函数来处理 json 数据：https://mp.csdn.net/console/editor/html/105371323 ，详情也可翻阅PostgreSQL官方手册。

json与jsonb的区别在于json存储快，而jsonb解析快（原因是json以文本数据存储，jsonb以二进制数据存储）。

示例:

testdb=# create table t_jsons(id serial, name varchar, content json);

CREATE TABLE

testdb=# insert into t_jsons(name ,content) values('duke','{"sex":"男","age":18}');

INSERT 0 1

testdb=# select * from t_jsons;

id | name | content

----+------+-----------------------

1 | duke | {"sex":"男","age":18}

(1 行记录)

testdb=# select name,content -> 'sex' from t_jsons;

name | ?column?

------+----------

duke | "男"

(1 行记录)

显示成文本

testdb=# select name,content ->> 'sex' from t_jsons;

name | ?column?

------+----------

duke | 男

(1 行记录)

9、网络地址类型

PostgreSQL提供用于存储IPv4、IPv6、MAC地址的数据类型。

用这些数据类型存储网络地址比用纯文本类型好，因为这些提供输入错误检查和特殊的操作功能。

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

在对Inet或cidr数据类型进行排序的时候，IPv4地址总是排在IPv6前面。

9.1 cidr例子

cidr输入	cidr输出	abbrev(cidr)
192.168.100.128/25	192.168.100.128/25	192.168.100.128/25
192.168/24	192.168.0.0/24	192.168.0/24
192.168/25	192.168.0.0/25	192.168.0.0/25
192.168.1	192.168.1.0/24	192.168.1/24
192.168	192.168.0.0/24	192.168.0/24
128.1	128.1.0.0/16	128.1/16
128	128.0.0.0/16	128.0/16
128.1.2	128.1.2.0/24	128.1.2/24
10.1.2	10.1.2.0/24	10.1.2/24
10.1	10.1.0.0/16	10.1/16
10	10.0.0.0/8	10/8
10.1.2.3/32	10.1.2.3/32	10.1.2.3/32
2001:4f8:3:ba::/64	2001:4f8:3:ba::/64	2001:4f8:3:ba::/64
2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128	2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128	2001:4f8:3:ba:2e0:81ff:fe22:d1f1
::ffff:1.2.3.0/120	::ffff:1.2.3.0/120	::ffff:1.2.3/120
::ffff:1.2.3.0/128	::ffff:1.2.3.0/128	::ffff:1.2.3.0/128

inet和cidr类型之间的本质区别是inet接受右边有非零位的网络掩码，而cidr不接受。例如，192.168.0.1/24对inet来说是有效的，但是cidr来说是无效的。

9.2 macaddr例子

macaddr类型存储 MAC 地址，也就是以太网卡硬件地址（尽管 MAC 地址还用于其它用途）。可以接受下列格式的输入：

'08:00:2b:01:02:03'

'08-00-2b-01-02-03'

'08002b:010203'

'08002b-010203'

'0800.2b01.0203'

'0800-2b01-0203'

'08002b010203'

这些例子指定的都是同一个地址。对于位a到f，大小写都可以接受

10、位串类型

位串就是一串 1 和 0 的字符串。它们可以用于存储和直观化位掩码。我们有两种 SQL 位类型：bit(n) 和bit varying(n)，这里的n是一个正整数。

bit 类型的数据必须准确匹配长度 n，试图存储短些或者长一些的数据都是错误的。bit varying 类型数据是最长 n 的变长类型；更长的串会被拒绝。写一个没有长度的bit 等效于 bit(1)，没有长度的 bit varying 意思是没有长度限制。

例子：

testdb=# create table t_bit(a bit(3),b bit varying);

CREATE TABLE

testdb=# insert into t_bit values(B'101',B'00'),(B'110',B'10010');

INSERT 0 2

testdb=# select * from t_bit;

a | b

-----+-------

101 | 00

110 | 10010

(2 行记录)

11、XML类型

xml 数据类型可以用于存储XML数据。将 XML 数据存到 text 类型中的优势在于它能够为结构良好性来检查输入值，并且还支持函数对其进行类型安全性检查。要使用这个数据类型，编译安装时必须使用 configure --with-libxml。

xml 可以存储由XML标准定义的格式良好的"文档"，以及由 XML 标准中的 XMLDecl? content 定义的"内容"片段，大致上，这意味着内容片段可以有多个顶级元素或字符节点。 xmlvalue IS DOCUMENT 表达式可以用来判断一个特定的 xml 值是一个完整的文件还是内容片段。

示例：

SQL标准：XMLPARSE ( { DOCUMENT | CONTENT } value) 生成XML类型的值

PostgreSQL特有语法 values::xml

如：

XMLPARSE ( DOCUMENT '<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="WebApp_ID" version="3.0">

<welcome-file-list>

<welcome-file>index.html</welcome-file>

</welcome-file-list>

</web-app>')

'<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="WebApp_ID" version="3.0">

<welcome-file-list>

<welcome-file>index.html</welcome-file>

</welcome-file-list>

</web-app>'::xml

例子：

create table t_xml(id serial,content xml);

CREATE TABLE

testdb=# insert into t_xml(content) values('<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="WebApp_ID" version="3.0"> <welcome-file-list> <welcome-file>index.html</welcome-file> </welcome-file-list> </web-app>'::xml);

INSERT 0 1

testdb=# select * from t_xml;

id | content

----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 | <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="WebApp_ID" version="3.0"> <welcome-file-list> <welcome-file>index.html</welcome-file> </welcome-file-list> </web-app>

(1 行记录)