PostgreSQL Oracle 相容性之 - nested table

标簽

PostgreSQL , Oracle , 相容性 , nested table

https://github.com/digoal/blog/blob/master/201901/20190113_01.md#%E8%83%8C%E6%99%AF 背景

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.  
           

https://github.com/digoal/blog/blob/master/201901/20190113_01.md#postgresql-nested-table%E5%85%BC%E5%AE%B9---array-complex-type 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)  
           

https://github.com/digoal/blog/blob/master/201901/20190113_01.md#%E5%85%8D%E8%B4%B9%E9%A2%86%E5%8F%96%E9%98%BF%E9%87%8C%E4%BA%91rds-postgresql%E5%AE%9E%E4%BE%8Becs%E8%99%9A%E6%8B%9F%E6%9C%BA 免費領取阿裡雲RDS PostgreSQL執行個體、ECS虛拟機