viewdatabase-partitioning

View and partition in PieCloudDB database


Does PieCloudDB database support partition and view? How to partition a table by time or enumerated values?


Solution

  • Examples of partitioned tables and views in PieCloudDB database are as follows:

    1.Create partitioned table.

    create table test_partition_list(
      member_id numeric,
      city character varying(32)
    )
    partition by list(city)
    (
      partition guangzhou values('guangzhou'),
      partition hangzhou values('hangzhou'),
      default partition other_city
    );
    

    2.Views

    create table lineitem ( l_orderkey    integer not null,
                            l_partkey     integer not null,
                            l_suppkey     integer not null,
                            l_linenumber  integer not null,
                            l_quantity    decimal(15,2) not null,
                            l_extendedprice  decimal(15,2) not null,
                            l_discount    decimal(15,2) not null,
                            l_tax         decimal(15,2) not null,
                            l_returnflag  char(1) not null,
                            l_linestatus  char(1) not null,
                            l_shipdate    date not null,
                            l_commitdate  date not null,
                            l_receiptdate date not null,
                            l_shipinstruct char(25) not null,
                            l_shipmode     char(10) not null,
                            l_comment      varchar(44) not null);
    
    create view vw_lineitem as 
      select l_orderkey,l_quantity,l_discount,l_tax from lineitem 
      where l_orderkey in (select l_orderkey from lineitem where l_linestatus='0');