PostgreSQL 08 数据类型补完

1月 07 2020

继续看一些开发中很实用的数据类型, 看完以后就可以进入到函数等高级内容了, Hibernate也可以尽快搞起了.

UUID类型
Array类型
hstore类型
JSON类型
自定义类型
Schema操作

UUID类型

UUID指的是RFC 4122标准中规定的128位的二进制码, 通常用32位16进制数表示, 并且在特定的位置用减号连接, 如下是一些UUID的例子:

40e6215d-b5c6-4896-987c-f30f3678f608
6ecd8c99-4036-403d-bf84-cf8400f67836
3f333df6-90a4-4fda-8dd3-9485d27cee36

在分布式环境下, UUID要比使用SERIAL更能确保数据的唯一性.

在Java中使用UUID, 简单的做法是直接使用UUID库:

public static void main(String[] args) {

    UUID uuid = UUID.randomUUID();

    System.out.println(uuid);

}

结果就会打印出一个UUID, 比如 0623b7fb-f975-4217-bb93-0f604313f97a. 在PgSQL中, 可以使用一个插件来生成UUID.

剩下就是在使用Hibernate的时候, 来试验一下这个新的数据类型吧.

Array类型

ARRAY类型很重要, 在PgSQL中每一个数据类型, PgSQL都会创建一个对应的数组类型, 甚至自定义一个数据类型的同时, PgSQL也会在后台创建对应的数组类型.

数组类型采取的是C系风格, 即在数据类型后加上[], 创建一个表带有数组类型如下:

CREATE TABLE contacts (
   id serial PRIMARY KEY,
   name VARCHAR (100),
   phones TEXT []
);

插入数组的时候, 使用特殊的 ARRAY 关键字, 然后用方括号括住数组中的每一个值:

INSERT INTO contacts (name, phones)
VALUES
(
    'John Doe',
    ARRAY [ '(408)-589-5846',
        '(408)-589-5555' ]
);

之后查询, 可以得到如下结果:

id   name      phones
1    John Doe  {(408)-589-5846,(408)-589-5555}

如果不使用ARRAY[], 也可以使用{}大括号:

INSERT INTO contacts (name, phones)
VALUES
   (
      'Lily Bush',
      '{"(408)-589-5841"}'
   ),
   (
      'William Gate',
      '{"(408)-589-5842","(408)-589-58423"}'
   );

要注意的是, 单引号括在大括号外边, 内部是用双引号括住字符串类型.

在查询或者取出数组的时候, 使用索引即可, 比如:

UPDATE contacts
SET phones [ 2 ] = '(408)-589-5843'
WHERE
   ID = 3;

这是更新其中的一个元素, 也可以直接更新整个字段, 这个时候就无须使用索引, 当然其后的类型也要正确.

数组的一个特点是, 可以将其当成一个结果集, 使用ANY, SOME之类进行引用:

SELECT
   name,
   phones
FROM
   contacts
WHERE
   '(408)-589-5555' = ANY (phones);

unnest()函数可以将数组展开成一列, 比如:

SELECT
   name,
   unnest(phones)
FROM
   contacts;

结果中一个name对应的数组有几个值, 就会被展开成几行.

hstore类型

这个需要安装扩展, 被包含在contrib包中, 在VPS上运行:

yum search postgresql12

可以找到PgSQL的相关安装包:

postgresql12-debuginfo.x86_64 : Debug information for package postgresql12
postgresql12.x86_64 : PostgreSQL client programs and libraries
postgresql12-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
postgresql12-devel.x86_64 : PostgreSQL development header files and libraries
postgresql12-docs.x86_64 : Extra documentation for PostgreSQL
postgresql12-libs.x86_64 : The shared libraries required for any PostgreSQL clients
postgresql12-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql12-odbc.x86_64 : PostgreSQL ODBC driver
postgresql12-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql12-plpython.x86_64 : The Python procedural language for PostgreSQL
postgresql12-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql12-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql12-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql12-test.x86_64 : The test suite distributed with PostgreSQL

要安装的其实也就是contrib, server和posgresql12.x86_64之前都安装过了, 一个是服务器, 一个是客户端.

yum install postgresql12-contrib.x86_64

安装完之后, 扩展的文件都有了, 但是扩展不是自动启用的, 而是要在每个数据库中执行创建扩展的命令, 要使用hstore就需要执行:

CREATE EXTENSION hstore;

没有报错就说明安装扩展完成, 扩展仅仅对使用了这个命令的数据库生效, 这样可以有效的隔离不同的数据库, 避免因装了太多扩展互相污染.

通过PgAdmin4查看, 可以看到dvdrental中的Extension中增加了hstore.

hstore类似于一个Map类型, 里边存放一个一个的键值对, 创建一个表然后插入数据的方式如下:

CREATE TABLE books (
   id serial primary key,
   title VARCHAR (255),
   attr hstore
);

INSERT INTO books (title, attr)
VALUES
   (
      'PostgreSQL Tutorial',
      '"paperback" => "243",
      "publisher" => "postgresqltutorial.com",
      "language"  => "English",
      "ISBN-13"   => "978-1449370000",
       "weight"    => "11.2 ounces"'
   );

INSERT INTO books (title, attr)
VALUES
(
    'PostgreSQL Cheat Sheet',
    '
"paperback" => "5",
"publisher" => "postgresqltutorial.com",
"language"  => "English",
"ISBN-13"   => "978-1449370001",
"weight"    => "1 ounces"'
);

可以看到, 用一个单引号包围住以=>分割的字符串键值对. 如果直接查询整个字段, 是如下结果:

ISBN-13 => 978-1449370000, weight => "11.2 ounces", paperback => 243, publisher => postgresqltutorial.com, language => English

如果要查询其中的一个键对应的值, 采取如下写法:

SELECT
       title, attr -> 'ISBN-13' AS isbn
FROM
    books;

这会在attr这个hstore对象中, 寻找键为 ‘ISBN-13’ 的值, 然后列名叫做ISBN, 返回结果集.

用在其他地方也一样, 只要记住 attr -> ‘key’ 代表了一个值.

如果要向hstore中添加一个键, 使用如下语句:

UPDATE books
SET attr = attr || '"freeshipping"=>"yes"' :: hstore;

注意这里使用了字符串连接符, 然后加上了一个键值对, 之后显式指明了数据类型为 hstore.

更新一个键也类似, 只要键存在就是更新:

UPDATE books
SET attr = attr || '"freeshipping"=>"yes"' :: hstore;

删除的语法有些特别:

UPDATE books
SET attr = delete(attr, 'freeshipping');

然后是几个特别的用法:

WHERE attr ? 'publisher';, 表示选出所有attr属性中键名包含publisher的元组
WHERE attr @> '"weight"=>"11.2 ounces"' :: hstore;, 这个表示选出attr中存在这个键值对的元组
WHERE attr ?& ARRAY [ 'language', 'weight' ];, 这个表示选出键同时包含language和weight两个键的元组.

之后是一系列函数:

akeys(),获取一个hstore对象内所有的键
skeys(),将键拆开成序列
avals(),获取一个hstore对象内所有的值
svals(),将值拆开成序列
hstore_to_json(),将hstore转换成JSON, 超级好用的函数
each(),将hstore拆开成键值, 然后按行显示

最后一个函数用法如下:

SELECT
   title,
   (EACH(attr) ).*
FROM
   books;

JSON类型

JSON类型感觉是以后常用的一个类型, 也要仔细来看看, 创建JSON类型的列如下:

CREATE TABLE orders (
   ID serial NOT NULL PRIMARY KEY,
   info json NOT NULL
);

info就是json类型的列, 之后插入数据的时候, 可以插入标准的JSON字符串:

INSERT INTO orders (info)
VALUES
   (
      '{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'
   ),
   (
      '{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'
   ),
   (
      '{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}'
   );

如果要取出来的时候, PgSQL提供了两个操作符, ->用于取对象, ->>用于取出字符串. 写法都是列名 ->/->> 属性名的写法. 这两个操作符的区别在于是否可以对嵌套的对象继续执行操作:

-- 选出JSON中customer对应的值, 结果是一个JSON对象
SELECT info -> 'customer' as name FROM orders;
-- 选出JSON中items对应的值,结果依然是一个JSON对象
SELECT info -> 'items' as item FROM orders;
-- 验证上一个查询的结果, 可以对对象继续取属性, 返回字符串
SELECT info -> 'items' ->> 'product' as product FROM orders;

-- 这行会报错! 选出JSON中items对应的值, 返回字符串, 不能对字符串进行进一步取值操作
SELECT info ->> 'items' -> 'product' as product FROM orders;

知道了如何从JSON中取值后, 这个表达式就可以用在任意地方了, 比如WHERE或者其他子句中. 取出的值可以转换成其他类型进行比较, 现在还没有学CAST函数, 但是可以先看一个例子:

SELECT
   info ->> 'customer' AS customer,
   info -> 'items' ->> 'product' AS product
FROM
   orders
WHERE
   CAST (
      info -> 'items' ->> 'qty' AS INTEGER
   ) = 2

这个例子选出所有items的数量为2的客户名称和产品名称.

对于一个JSON, 就可以认为是一个元组, 在其上选一个属性, 就和从普通关系中选一列一样, 可以进行对应的操作, 比如聚集函数:

SELECT
    MIN (
            CAST (
                            info -> 'items' ->> 'qty' AS INTEGER
                )
        ),
    MAX (
            CAST (
                            info -> 'items' ->> 'qty' AS INTEGER
                )
        ),
    SUM (
            CAST (
                            info -> 'items' ->> 'qty' AS INTEGER
                )
        ),
    AVG (
            CAST (
                            info -> 'items' ->> 'qty' AS INTEGER
                )
        )

FROM
    orders;

来看一些操作JSON的函数:

json_each(), 可以将一个JSON展开成键值对, 展开的各个结果依然是JSON对象, 仅展开第一层. 需要的话可以再对获取的结果继续调用.
json_each_text(), 上一个函数的字符串版本.
json_object_keys(), 展开并返回所有键, 一般搭配其他函数针对单个JSON对象使用.
json_typeof(), 返回某个键对应的值的类型, 参数必须是一个JSON对象, 不能是字符串. 结果是object就表示是一个JSON对象. 例如
```
SELECT json_typeof(info -> 'items' -> 'qty') FROM orders;
```
结果是number类型.

官网的JSON函数页面还有更多函数.

自定义类型

自定义类型涉及后边很多函数等相关的内容, 还没有学会. 这里先记录下来.

创建自定义有两种:

创建一个自定义的单个类型, 使用 CREATE DOMAIN 新类型名称 AS 已知类型约束; 语句, 很明显创建了一个域对应的类型, 这个实际上看上去像一个别名.
创建复合类型, 使用CREATE TYPE. 详情见下.

创建的类型, 仅在对应的schema中生效. 如果创建DOMAIN类型, 通过PgADmin4可以看到当前Schema下的Domains中新增了一个内容. 而新创建的TYPE复合类型, 则是在Types对象下.

看两个从网站上抄的例子:

CREATE DOMAIN contact_name AS VARCHAR NOT NULL CHECK (value !~ '\s');

CREATE TABLE mail_list (
    id serial PRIMARY KEY,
    first_name contact_name,
    last_name contact_name,
    email VARCHAR NOT NULL
);

创建了一个域类型和使用这个类型的表, !~操作符是PgSQL中不匹配正则表达式的意思, 也就是不能有空白.

创建一个复合类型和返回此类型结果的函数:

CREATE TYPE film_summary AS (
    film_id INT,
    title VARCHAR,
    release_year YEAR
);

CREATE OR REPLACE FUNCTION get_film_summary (f_id INT)
    RETURNS film_summary AS
$$
SELECT
    film_id,
    title,
    release_year
FROM
    film
WHERE
    film_id = f_id ;
$$
LANGUAGE SQL;

这个函数接受一个INT类型的参数, 然后到film中查找与参数相等的ID, 然后返回film_summary类型, film_summary复合类型中的三个字段由函数中的查询结果填充.

Schema操作

Schema的操作也不外乎增删改, 简明列出如下:

可以显式指定schema_name.object_name来使用schema中的对象(不仅仅是表).
SELECT current_schema();,获得当前schema名称.
SHOW search_path;,查看当前默认寻找路径.
SET search_path TO schema_name1, schema_name2...;,设置搜索路径, 设置了之后就可以不显式写schema路径
GRANT USAGE/CRUD ON SCHEMA schema_name TO user_name;,授权, schema必须先授予USAGE权限, 才能让其他权限发挥作用
CREATE SCHEMA [IF NOT EXISTS] schema_name;,创建Schema
CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION user_name;,创建一个与user_name同名的schema, 且这个用户拥有schema权限.
ALTER SCHEMA schema_name RENAME TO new_name;,修改schema名称
ALTER SCHEMA schema_name OWNER TO { new_owner | CURRENT_USER | SESSION_USER};,修改schema所属用户, 用户这里还分SESSION_USER, 先暂时不讨论.
DROP SCHEMA [IF EXISTS] schema_name [ CASCADE | RESTRICT ];,删除schema, 最后一个级联默认是RESTRICT, 即仅在schema为空的情况下删除. CASCADE会删除schema中所有对象和依赖这些对象的内容, 但可以在schema不为空的情况下删除schema.

到目前为止, 就把PgSQL操作的部分基本上都看完了, 还剩下几大部分: 索引, 触发器, 视图, 以及函数, 还有现在已经用不到的存储过程.

这几部分在纯操作层面简单看了一下, 也不会很难. 现在应该是可以进军Hibernate了.

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