TAG 21
作者
digoal
日期
2019-01-13
标签
PostgreSQL , Oracle , 兼容性 , nested table
背景
Oracle nested table功能介绍如下
http://www.orafaq.com/wiki/NESTED_TABLE
NESTED TABLE is an Oracle data type used to support columns containing multivalued attributes, in this case, columns that can hold an entire sub-table.
Create a table with NESTED TABLE column:
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
/
CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
NESTED TABLE col1 STORE AS col1_tab;
Insert data into table:
INSERT INTO nested_table VALUES (1, my_tab_t('A'));
INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
COMMIT;
Select from nested table:
```
SQL> SELECT * FROM nested_table;
ID COL1
1 MY_TAB_T('A')
2 MY_TAB_T('B', 'C')
3 MY_TAB_T('D', 'E', 'F')
```
Unnesting the subtable:
```
SQL> SELECT id, COLUMN_VALUE FROM nested_table t1, TABLE(t1.col1) t2;
ID COLUMN_VALUE
1 A
2 B
2 C
3 D
3 E
3 F
6 rows selected.
```
PostgreSQL nested table兼容 - array, complex type
PostgreSQL 使用数组+复合类型，实现同样场景需求。
1、创建复合类型(如果系统中曾经已经创建了这个类型，或者曾经已经创建过一个即将使用的TABLE，则不需要再次创建)
```
postgres=# create type thisisnesttable1 as (c1 int, c2 int, c3 text, c4 timestamp);
CREATE TYPE
or
create table nesttablename (...); -- 隐含创建composite type ```
2、创建nested table (thisisnesttable1作为hello表的nested table)
postgres=# create table hello (id int, info text, nst thisisnesttable1[]);
CREATE TABLE
3、插入数据（多行以数组存入，一个nested table的最大限制1GB(即PostgreSQL varying type的存储上限)）
```
postgres=# insert into hello values (1,'test',array['(1,2,"abcde","2018-01-01 12:00:00")'::thisisnesttable1, '(2,3,"abcde123","2018-01-01 12:00:00")'::thisisnesttable1]);
INSERT 0 1
或使用row构造法
insert into hello values (
1,
'test',
(array
[
row(1,2,'hello',now()),
row(1,3,'hello',now())
]
)::thisisnesttable1[]
);
```
https://www.postgresql.org/docs/11/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS
4、查询
postgres=# select * from hello ;
id | info | nst
----+------+----------------------------------------------------------------------------------
1 | test | {"(1,2,abcde,\"2018-01-01 12:00:00\")","(2,3,abcde123,\"2018-01-01 12:00:00\")"}
(1 row)
5、使用unnest可以解开nested table的内容
```
postgres=# select id,info,(unnest(nst)).* from hello ;
id | info | c1 | c2 | c3 | c4
----+------+----+----+----------+---------------------
1 | test | 1 | 2 | abcde | 2018-01-01 12:00:00
1 | test | 2 | 3 | abcde123 | 2018-01-01 12:00:00
(2 rows)
postgres=# select id,info,(unnest(nst)).c1 from hello ;
id | info | c1
----+------+----
1 | test | 1
1 | test | 2
(2 rows)
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
