github.com/tuhaihe/gpbackup@v1.0.3/end_to_end/resources/test_tables_ddl.sql (about)

     1  --
     2  -- Greenplum Database database dump
     3  --
     4  
     5  SET statement_timeout = 0;
     6  SET client_encoding = 'UTF8';
     7  SET standard_conforming_strings = on;
     8  SET check_function_bodies = false;
     9  SET client_min_messages = warning;
    10  
    11  SET default_with_oids = false;
    12  
    13  --
    14  --
    15  
    16  CREATE SCHEMA schema2;
    17  
    18  
    19  
    20  SET search_path = public, pg_catalog;
    21  
    22  SET default_tablespace = '';
    23  
    24  --
    25  --
    26  
    27  CREATE SEQUENCE myseq1 START 100;
    28  
    29  CREATE SEQUENCE myseq2 START 100;
    30  
    31  CREATE VIEW myview2 AS SELECT '1';
    32  
    33  --
    34  --
    35  
    36  CREATE TABLE foo (
    37      i integer DEFAULT nextval('myseq1') NOT NULL
    38  ) DISTRIBUTED BY (i);
    39  
    40  CREATE VIEW myview1 AS SELECT * from foo;
    41  
    42  --
    43  --
    44  
    45  CREATE TABLE holds (
    46      i integer
    47  ) DISTRIBUTED BY (i);
    48  
    49  --
    50  --
    51  
    52  
    53  CREATE TABLE sales (
    54      id integer,
    55      date date,
    56      amt numeric(10,2)
    57  ) DISTRIBUTED BY (id) PARTITION BY RANGE(date)
    58            (
    59            PARTITION jan17 START ('2017-01-01'::date) END ('2017-02-01'::date) WITH (tablename='sales_1_prt_jan17', appendonly=false ),
    60            PARTITION feb17 START ('2017-02-01'::date) END ('2017-03-01'::date) WITH (tablename='sales_1_prt_feb17', appendonly=false ),
    61            PARTITION mar17 START ('2017-03-01'::date) END ('2017-04-01'::date) WITH (tablename='sales_1_prt_mar17', appendonly=false ),
    62            PARTITION apr17 START ('2017-04-01'::date) END ('2017-05-01'::date) WITH (tablename='sales_1_prt_apr17', appendonly=false ),
    63            PARTITION may17 START ('2017-05-01'::date) END ('2017-06-01'::date) WITH (tablename='sales_1_prt_may17', appendonly=false ),
    64            PARTITION jun17 START ('2017-06-01'::date) END ('2017-07-01'::date) WITH (tablename='sales_1_prt_jun17', appendonly=false ),
    65            PARTITION jul17 START ('2017-07-01'::date) END ('2017-08-01'::date) WITH (tablename='sales_1_prt_jul17', appendonly=false ),
    66            PARTITION aug17 START ('2017-08-01'::date) END ('2017-09-01'::date) WITH (tablename='sales_1_prt_aug17', appendonly=false ),
    67            PARTITION sep17 START ('2017-09-01'::date) END ('2017-10-01'::date) WITH (tablename='sales_1_prt_sep17', appendonly=false ),
    68            PARTITION oct17 START ('2017-10-01'::date) END ('2017-11-01'::date) WITH (tablename='sales_1_prt_oct17', appendonly=false ),
    69            PARTITION nov17 START ('2017-11-01'::date) END ('2017-12-01'::date) WITH (tablename='sales_1_prt_nov17', appendonly=false ),
    70            PARTITION dec17 START ('2017-12-01'::date) END ('2018-01-01'::date) WITH (tablename='sales_1_prt_dec17', appendonly=false )
    71            );
    72  
    73  
    74  SET search_path = schema2, pg_catalog;
    75  
    76  --
    77  --
    78  
    79  CREATE TABLE foo2 (
    80      i integer
    81  ) DISTRIBUTED BY (i);
    82  
    83  
    84  
    85  --
    86  --
    87  
    88  
    89  
    90  CREATE TABLE foo3 (
    91      i integer
    92  ) DISTRIBUTED BY (i);
    93  
    94  --
    95  --
    96  
    97  CREATE TABLE returns (
    98      id integer,
    99      date date,
   100      amt numeric(10,2)
   101  ) DISTRIBUTED BY (id) PARTITION BY RANGE(date)
   102            (
   103            PARTITION jan17 START ('2017-01-01'::date) END ('2017-02-01'::date) WITH (tablename='returns_1_prt_jan17', appendonly=false ),
   104            PARTITION feb17 START ('2017-02-01'::date) END ('2017-03-01'::date) WITH (tablename='returns_1_prt_feb17', appendonly=false ),
   105            PARTITION mar17 START ('2017-03-01'::date) END ('2017-04-01'::date) WITH (tablename='returns_1_prt_mar17', appendonly=false ),
   106            PARTITION apr17 START ('2017-04-01'::date) END ('2017-05-01'::date) WITH (tablename='returns_1_prt_apr17', appendonly=false ),
   107            PARTITION may17 START ('2017-05-01'::date) END ('2017-06-01'::date) WITH (tablename='returns_1_prt_may17', appendonly=false ),
   108            PARTITION jun17 START ('2017-06-01'::date) END ('2017-07-01'::date) WITH (tablename='returns_1_prt_jun17', appendonly=false ),
   109            PARTITION jul17 START ('2017-07-01'::date) END ('2017-08-01'::date) WITH (tablename='returns_1_prt_jul17', appendonly=false ),
   110            PARTITION aug17 START ('2017-08-01'::date) END ('2017-09-01'::date) WITH (tablename='returns_1_prt_aug17', appendonly=false ),
   111            PARTITION sep17 START ('2017-09-01'::date) END ('2017-10-01'::date) WITH (tablename='returns_1_prt_sep17', appendonly=false ),
   112            PARTITION oct17 START ('2017-10-01'::date) END ('2017-11-01'::date) WITH (tablename='returns_1_prt_oct17', appendonly=false ),
   113            PARTITION nov17 START ('2017-11-01'::date) END ('2017-12-01'::date) WITH (tablename='returns_1_prt_nov17', appendonly=false ),
   114            PARTITION dec17 START ('2017-12-01'::date) END ('2018-01-01'::date) WITH (tablename='returns_1_prt_dec17', appendonly=false )
   115            );
   116  
   117  CREATE TABLE ao1 (
   118      i integer
   119  ) WITH (appendonly=true);
   120  
   121  CREATE TABLE ao2 (
   122      i integer
   123  ) WITH (appendonly=true, orientation=column);
   124  
   125  --
   126  --
   127  
   128  REVOKE ALL ON SCHEMA public FROM PUBLIC;
   129  GRANT ALL ON SCHEMA public TO PUBLIC;
   130  
   131  CREATE TABLE public."FOObar" (i int);
   132  insert into public."FOObar" values (1);
   133  
   134  --
   135  -- Greenplum Database database dump complete
   136  --
   137