All Products
Search
Document Center

Data Transmission Service:NESTED TABLE

Last Updated:Oct 31, 2023

Background information

For Oracle nested table detailed features, see http://www.orafaq.com/wiki/NESTED_TABLE.

NESTED TABLE is an Oracle data type used to support columns that contain multi-value attributes. In this example, the column can accommodate the entire sub-TABLE.

Create a TABLE with a NESTED TABLE:

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 a 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')

Unnest child table:

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.

Compatible with PostgreSQL Nested Table

PostgreSQL uses array and composite types to meet the same scenario requirements.

  1. Create a composite type.

    postgres=# create type thisisnesttable1 as (c1 int, c2 int, c3 text, c4 timestamp);  
    CREATE TYPE  
      
    or
    create table nesttablename (...) ; -- Implicitly create a composite type.
    Note

    If this type has already been created in the system or a TABLE to be used has already been created, you do not need to create it again.

  2. Create a Nested Table.

    postgres=# create table hello (id int, info text, nst thisisnesttable1[]);  
    CREATE TABLE
    Note

    thisisnesttable 1 as the Nested Table of the hello Table

  3. Insert data.

    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  
      
    Or use the row construction method
    insert into hello values (
      1,
      'test', 
      (array
        [
          row(1,2,'hello',now()),  
          row(1,3,'hello',now())
        ]
      )::thisisnesttable1[]
    );
    Note

    Multiple rows are stored as an array, and the maximum limit for a nested table is 1GB (that is, the upper limit of storage for PostgreSQL varying type).

    For more information, see https://www.postgresql.org/docs/11/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS.

  4. Query

    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. You can use unnest to unnest the content of the 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)