携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第37天，点击查看活动详情

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程😜😜😜
擅长Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理。
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

前言

本文讲解了postgreSQL数组类型及复合类型在实际数据分析中的应用

---

⛳️ 1.数组类型

PostgreSQL 允许将字段定义成变长的多维数组。 数组类型可以是任何基本类型或用户定义类型，枚举类型或复合类型。

🌈 1.1 声明数组

创建表的时候，我们可以声明数组，方式如下：
#pay_by_quarter 为一维整型数组、schedule 为二维文本类型数组。
postgres=# CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
##我们也可以使用 "ARRAY" 关键字
postgres=# CREATE TABLE sal_emp (
name text,
pay_by_quarter integer ARRAY[4],
schedule text[][]
);

🌈 1.2 插入值

#插入值使用花括号 {}，元素在 {} 使用逗号隔开：
postgres=# INSERT INTO sal_emp VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');

postgres=# INSERT INTO sal_emp VALUES ('Carol','{20000, 25000, 25000, 25000}', '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

🌈 1.3 访问数组

现在我们可以在这个表上运行一些查询。
首先，我们演示如何访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名：
postgres=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];
注意：数组的下标数字是写在方括弧内的，下标从1开始

🌈 1.4 修改数组

##我们可以对数组的值进行修改：
postgres=# UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
##或者使用 ARRAY 构造器语法
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';\

🌈 1.5 数组中检索

##要搜索一个数组中的数值，你必须检查该数组的每一个值
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
##另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

⛳️ 2.复合类型

复合类型表示一行或者一条记录的结构； 它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL 允许像简单数据类型那样使用复合类型。
比如，一个表的某个字段可以声明为一个复合类型。

🌈 2.1 声明复合类型

##下面是两个定义复合类型的简单例子：
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);

语法类似于 CREATE TABLE，只是这里只可以声明字段名字和类型。
定义了类型，我们就可以用它创建表：\

CREATE TABLE on_hand (
item inventory_item,
count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

🌈 2.2 复合类型值输入

要以文本常量书写复合类型值，在圆括弧里包围字段值并且用逗号分隔他们。
你可以在任何字段值周围放上双引号，如果值本身包含逗号或者圆括弧， 你必须用双引号括起。
复合类型常量的一般格式为：'("fuzzy dice",42,1.99)'

🌈 2.3 访问复合类型

要访问复合类型字段的一个域，我们写出一个点以及域的名字，
非常类似从一个表名字里选出一个字段。
实际上，因为实在太像从表名字中选取字段，
所以我们经常需要用圆括弧来避免分析器混淆。
比如，你可能需要从on_hand 例子表中选取一些子域，像下面这样：
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;
注：现在圆括弧对象正确地解析为一个指向item字段的引用，然后就可以从中选取子域。