CREATE TABLE

-- Create a simple table.
gaussdb=# CREATE TABLE tpcds.warehouse_t1
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

gaussdb=# CREATE TABLE tpcds.warehouse_t2
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)         DICTIONARY,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

-- Create a table and set the default value of the W_STATE column to GA.
gaussdb=# CREATE TABLE tpcds.warehouse_t3
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)           DEFAULT 'GA',
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

-- Create a table and check whether the W_WAREHOUSE_NAME column is unique at the end of its creation.
gaussdb=# CREATE TABLE tpcds.warehouse_t4
(
    W_WAREHOUSE_SK            INTEGER                NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)               NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)   UNIQUE DEFERRABLE,
    W_WAREHOUSE_SQ_FT         INTEGER                        ,
    W_STREET_NUMBER           CHAR(10)                       ,
    W_STREET_NAME             VARCHAR(60)                    ,
    W_STREET_TYPE             CHAR(15)                       ,
    W_SUITE_NUMBER            CHAR(10)                       ,
    W_CITY                    VARCHAR(60)                    ,
    W_COUNTY                  VARCHAR(30)                    ,
    W_STATE                   CHAR(2)                        ,
    W_ZIP                     CHAR(10)                       ,
    W_COUNTRY                 VARCHAR(20)                    ,
    W_GMT_OFFSET              DECIMAL(5,2) 
);

-- Create a table with its fill factor set to 70%.
gaussdb=# CREATE TABLE tpcds.warehouse_t5
(
    W_WAREHOUSE_SK            INTEGER                NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)               NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                    ,
    W_WAREHOUSE_SQ_FT         INTEGER                        ,
    W_STREET_NUMBER           CHAR(10)                       ,
    W_STREET_NAME             VARCHAR(60)                    ,
    W_STREET_TYPE             CHAR(15)                       ,
    W_SUITE_NUMBER            CHAR(10)                       ,
    W_CITY                    VARCHAR(60)                    ,
    W_COUNTY                  VARCHAR(30)                    ,
    W_STATE                   CHAR(2)                        ,
    W_ZIP                     CHAR(10)                       ,
    W_COUNTRY                 VARCHAR(20)                    ,
    W_GMT_OFFSET              DECIMAL(5,2),
    UNIQUE(W_WAREHOUSE_NAME) WITH(fillfactor=70)
);

-- Alternatively, user the following syntax:
gaussdb=# CREATE TABLE tpcds.warehouse_t6
(
    W_WAREHOUSE_SK            INTEGER                NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)               NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)              UNIQUE,
    W_WAREHOUSE_SQ_FT         INTEGER                        ,
    W_STREET_NUMBER           CHAR(10)                       ,
    W_STREET_NAME             VARCHAR(60)                    ,
    W_STREET_TYPE             CHAR(15)                       ,
    W_SUITE_NUMBER            CHAR(10)                       ,
    W_CITY                    VARCHAR(60)                    ,
    W_COUNTY                  VARCHAR(30)                    ,
    W_STATE                   CHAR(2)                        ,
    W_ZIP                     CHAR(10)                       ,
    W_COUNTRY                 VARCHAR(20)                    ,
    W_GMT_OFFSET              DECIMAL(5,2)
) WITH(fillfactor=70);

-- Create a table and specify that its data is not written to WALs.
gaussdb=# CREATE UNLOGGED TABLE tpcds.warehouse_t7
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

-- Create a temporary table.
gaussdb=# CREATE TEMPORARY TABLE warehouse_t24
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

-- Create a temporary table in a transaction and specify that this table is deleted when the transaction is committed.
gaussdb=# CREATE TEMPORARY TABLE warehouse_t25
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
) ON COMMIT DELETE ROWS;

-- Create a table and specify that no error is reported for duplicate tables (if any).
gaussdb=# CREATE TABLE IF NOT EXISTS tpcds.warehouse_t8
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

-- Create a general tablespace.
gaussdb=# CREATE TABLESPACE DS_TABLESPACE1 RELATIVE LOCATION 'tablespace/tablespace_1';
-- Specify a tablespace when creating a table.
gaussdb=# CREATE TABLE tpcds.warehouse_t9
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
) TABLESPACE DS_TABLESPACE1;

-- Separately specify the index tablespace for W_WAREHOUSE_NAME when creating the table.
gaussdb=# CREATE TABLE tpcds.warehouse_t10
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)           UNIQUE USING INDEX TABLESPACE DS_TABLESPACE1,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

-- Create a table with a primary key constraint.
gaussdb=# CREATE TABLE tpcds.warehouse_t11
(
    W_WAREHOUSE_SK            INTEGER            PRIMARY KEY,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

-- An alternative for the preceding syntax is as follows:
gaussdb=# CREATE TABLE tpcds.warehouse_t12
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2),
    PRIMARY KEY(W_WAREHOUSE_SK)
);

-- Or use the following statement to specify the name of the constraint:
gaussdb=# CREATE TABLE tpcds.warehouse_t13
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2),
    CONSTRAINT W_CSTR_KEY1 PRIMARY KEY(W_WAREHOUSE_SK)
);

-- Create a table with a compound primary key constraint.
gaussdb=# CREATE TABLE tpcds.warehouse_t14
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2),
    CONSTRAINT W_CSTR_KEY2 PRIMARY KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)
);




-- Define a column check constraint.
gaussdb=# CREATE TABLE tpcds.warehouse_t19
(
    W_WAREHOUSE_SK            INTEGER               PRIMARY KEY CHECK (W_WAREHOUSE_SK > 0),
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)           CHECK (W_WAREHOUSE_NAME IS NOT NULL),
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
);

gaussdb=# CREATE TABLE tpcds.warehouse_t20
(
    W_WAREHOUSE_SK            INTEGER               PRIMARY KEY,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)           CHECK (W_WAREHOUSE_NAME IS NOT NULL),
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2),
    CONSTRAINT W_CONSTR_KEY2 CHECK(W_WAREHOUSE_SK > 0 AND W_WAREHOUSE_NAME IS NOT NULL) 
);

-- Define a table with each row stored in all DNs.
gaussdb=# CREATE TABLE tpcds.warehouse_t21
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2)
)DISTRIBUTE BY REPLICATION;

Enable the primarynode option of the replication table.
gaussdb=#  ALTER TABLE tpcds.warehouse_t21 SET (primarynode=on);

Check whether the option is enabled. (The content displayed in Options varies according to the version.)
gaussdb=#  \d+ tpcds.warehouse_t21
                                  Table "tpcds.warehouse_t21"
      Column       |         Type          | Modifiers | Storage  | Stats target | Description 
-------------------+-----------------------+-----------+----------+--------------+-------------
 w_warehouse_sk    | integer               | not null  | plain    |              | 
 w_warehouse_id    | character(16)         | not null  | extended |              | 
 w_warehouse_name  | character varying(20) |           | extended |              | 
 w_warehouse_sq_ft | integer               |           | plain    |              | 
 w_street_number   | character(10)         |           | extended |              | 
 w_street_name     | character varying(60) |           | extended |              | 
 w_street_type     | character(15)         |           | extended |              | 
 w_suite_number    | character(10)         |           | extended |              | 
 w_city            | character varying(60) |           | extended |              | 
 w_county          | character varying(30) |           | extended |              | 
 w_state           | character(2)          |           | extended |              | 
 w_zip             | character(10)         |           | extended |              | 
 w_country         | character varying(20) |           | extended |              | 
 w_gmt_offset      | numeric(5,2)          |           | main     |              | 
Has OIDs: no
Distribute By: REPLICATION
Location Nodes: ALL DATANODES
Options: orientation=row, logical_repl_node=-1, compression=no, primarynode=on

-- Define a hash table.
gaussdb=# CREATE TABLE tpcds.warehouse_t22
(
    W_WAREHOUSE_SK            INTEGER               NOT NULL,
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
    W_WAREHOUSE_NAME          VARCHAR(20)                   ,
    W_WAREHOUSE_SQ_FT         INTEGER                       ,
    W_STREET_NUMBER           CHAR(10)                      ,
    W_STREET_NAME             VARCHAR(60)                   ,
    W_STREET_TYPE             CHAR(15)                      ,
    W_SUITE_NUMBER            CHAR(10)                      ,
    W_CITY                    VARCHAR(60)                   ,
    W_COUNTY                  VARCHAR(30)                   ,
    W_STATE                   CHAR(2)                       ,
    W_ZIP                     CHAR(10)                      ,
    W_COUNTRY                 VARCHAR(20)                   ,
    W_GMT_OFFSET              DECIMAL(5,2),
    CONSTRAINT W_CONSTR_KEY3 UNIQUE(W_WAREHOUSE_SK)
)DISTRIBUTE BY HASH(W_WAREHOUSE_SK);

-- Define a table using RANGE distribution.
gaussdb=# CREATE TABLE tpcds.warehouse_t26
(
W_WAREHOUSE_SK            INTEGER               NOT NULL,
W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
W_WAREHOUSE_NAME          VARCHAR(20)                   ,
W_WAREHOUSE_SQ_FT         INTEGER                       ,
W_STREET_NUMBER           CHAR(10)                      ,
W_STREET_NAME             VARCHAR(60)                   ,
W_STREET_TYPE             CHAR(15)                      ,
W_SUITE_NUMBER            CHAR(10)                      ,
W_CITY                    VARCHAR(60)                   ,
W_COUNTY                  VARCHAR(30)                   ,
W_STATE                   CHAR(2)                       ,
W_ZIP                     CHAR(10)                      ,
W_COUNTRY                 VARCHAR(20)                   ,
W_GMT_OFFSET              DECIMAL(5,2)
)DISTRIBUTE BY RANGE(W_WAREHOUSE_ID)
(
   SLICE s1 VALUES LESS THAN (10) DATANODE dn1,
   SLICE s2 VALUES LESS THAN (20) DATANODE dn2,
   SLICE s3 VALUES LESS THAN (30) DATANODE dn3,
   SLICE s4 VALUES LESS THAN (MAXVALUE) DATANODE dn4
);
-- Example of specifying multiple DNs for a shard in the range distribution table
gaussdb=# create table lrt_range (f_int1 int, f_int2 int, f_varchar1 varchar2(100))
 distribute by range (f_int1, f_int2)
(
    slice s1 values less than (100, 100) datanode (datanode1,datanode2),
    slice s2 values less than (200, 200) datanode datanode2,
    slice s3 values less than (300, 300) datanode datanode2,
    slice s4 values less than (maxvalue, maxvalue) datanode (datanode1,datanode2)
);
-- Insert four data into shard s1.
gaussdb=# insert into lrt_range values(generate_series(1,4), generate_series(1,4));
gaussdb=# select node_name,node_type,node_id from pgxc_node;
  node_name   | node_type |  node_id
--------------+-----------+------------
 coordinator1 | C         | 1938253334
 datanode1    | D         |  888802358
 datanode2    | D         | -905831925
(3 rows)
-- View data distribution.
gaussdb=# select xc_node_id,* from lrt_range;
 xc_node_id | f_int1 | f_int2 | f_varchar1
------------+--------+--------+------------
  888802358 |      2 |      2 |
  888802358 |      3 |      3 |
  888802358 |      4 |      4 |
 -905831925 |      1 |      1 |
(4 rows)
-- Map the DN based on the hash value for the shards in the range distribution table, insert (1,1) into datanode2, and insert other data into datanode1.

-- Example of specifying multiple DNs for a shard in the list distribution table
gaussdb=# create table  t_news
(
    county  varchar(30),
    year    varchar(60),
    name    varchar(60),
    age     int,
    news    text
)distribute by list(county, year)
(
    slice s1 values (('china', '2020'),('china', '2021')) datanode (datanode1,datanode2),
    slice s2 values (('china', '2022'),('china', '2023'),('china', '2024')) datanode (datanode1,datanode2),
    slice s3 values (('china', '2025')) datanode (datanode1,datanode2),
    slice s4 values (('canada', '2021')) datanode datanode1,
    slice s5 values (('canada', '2022')) datanode datanode2,
    slice s6 values (('canada', '2023')) datanode datanode1,    
    slice s7 values (('uk','2021')) datanode datanode1,
    slice s8 values (('uk','2022')) datanode datanode2,
    slice s9 values (('uk','2023')) datanode datanode1,
    slice s0 values (default)       datanode (datanode1,datanode2)
);
-- Use the round robin mode to map DNs for the non-default shard.
-- For the shard s1, map ('china', '2020') to datanode1 and ('china', '2021') to datanode2.
-- For the shard s2, map ('china', '2022') to datanode1, ('china', '2023') to datanode2, and ('china', '2024') to datanode1.
-- For the shard s3, map data ('china', '2025') to datanode1.

-- Delete data.
gaussdb=# delete from t_news;
DELETE 6
-- Use hash value to map DN for the default shard.
-- For the shard s0, map ('Japan', '2020') to datanode1 and other data to datanode2.

-- Example of multi-column range shard policy
gaussdb=# create table t_ran1(c1 int, c2 int, c3 int, c4 int, c5 int)
distribute by range(c1,c2)
(
SLICE s1 VALUES LESS THAN (10,10) DATANODE datanode1,
SLICE s2 VALUES LESS THAN (10,20) DATANODE datanode2,
SLICE s3 VALUES LESS THAN (20,10) DATANODE datanode3
);
gaussdb=# insert into t_ran1 values(9,5,'a');
gaussdb=# insert into t_ran1 values(9,20,'a');
gaussdb=# insert into t_ran1 values(9,21,'a');
gaussdb=# insert into t_ran1 values(10,5,'a');
gaussdb=# insert into t_ran1 values(10,15,'a');
gaussdb=# insert into t_ran1 values(10,20,'a');
gaussdb=# insert into t_ran1 values(10,21,'a');
gaussdb=# insert into t_ran1 values(11,5,'a');
gaussdb=# insert into t_ran1 values(11,20,'a');
gaussdb=# insert into t_ran1 values(11,21,'a');
gaussdb=# select node_name,node_type,node_id from pgxc_node;
  node_name   | node_type |   node_id
--------------+-----------+-------------
 coordinator1 | C         |  1938253334
 datanode1    | D         |   888802358
 datanode2    | D         |  -905831925
 datanode3    | D         | -1894792127
(4 rows)
gaussdb=# select xc_node_id,* from t_ran1;
 xc_node_id  | c1 | c2 | c3 | c4 | c5
-------------+----+----+----+----+----
   888802358 |  9 |  5 |  0 |    |
   888802358 |  9 | 20 |  0 |    |
   888802358 |  9 | 21 |  0 |    |
   888802358 | 10 |  5 |  0 |    |
  -905831925 | 10 | 15 |  0 |    |
 -1894792127 | 10 | 20 |  0 |    |
 -1894792127 | 10 | 21 |  0 |    |
 -1894792127 | 11 |  5 |  0 |    |
 -1894792127 | 11 | 20 |  0 |    |
 -1894792127 | 11 | 21 |  0 |    |
(10 rows)
 
-- Create a table using SLICE REFERENCES.
gaussdb=# CREATE TABLE tpcds.warehouse_t27
(
W_WAREHOUSE_SK            INTEGER               NOT NULL,
W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
W_WAREHOUSE_NAME          VARCHAR(20)                   ,
W_WAREHOUSE_SQ_FT         INTEGER                       ,
W_STREET_NUMBER           CHAR(10)                      ,
W_STREET_NAME             VARCHAR(60)                   ,
W_STREET_TYPE             CHAR(15)                      ,
W_SUITE_NUMBER            CHAR(10)                      ,
W_CITY                    VARCHAR(60)                   ,
W_COUNTY                  VARCHAR(30)                   ,
W_STATE                   CHAR(2)                       ,
W_ZIP                     CHAR(10)                      ,
W_COUNTRY                 VARCHAR(20)                   ,
W_GMT_OFFSET              DECIMAL(5,2)
)DISTRIBUTE BY RANGE(W_WAREHOUSE_ID) SLICE REFERENCES warehouse_t26;
 
-- Define a table using LIST distribution.
gaussdb=# CREATE TABLE tpcds.warehouse_t28
(
W_WAREHOUSE_SK            INTEGER               NOT NULL,
W_WAREHOUSE_ID            CHAR(16)              NOT NULL,
W_WAREHOUSE_NAME          VARCHAR(20)                   ,
W_WAREHOUSE_SQ_FT         INTEGER                       ,
W_STREET_NUMBER           CHAR(10)                      ,
W_STREET_NAME             VARCHAR(60)                   ,
W_STREET_TYPE             CHAR(15)                      ,
W_SUITE_NUMBER            CHAR(10)                      ,
W_CITY                    VARCHAR(60)                   ,
W_COUNTY                  VARCHAR(30)                   ,
W_STATE                   CHAR(2)                       ,
W_ZIP                     CHAR(10)                      ,
W_COUNTRY                 VARCHAR(20)                   ,
W_GMT_OFFSET              DECIMAL(5,2)
)DISTRIBUTE BY LIST(W_COUNTRY)
(
    SLICE s1 VALUES ('USA') DATANODE dn1,
    SLICE s2 VALUES ('CANADA') DATANODE dn2,
    SLICE s3 VALUES ('UK') DATANODE dn3,
    SLICE s4 VALUES (DEFAULT) DATANODE dn4
);
-- Add a varchar column to the tpcds.warehouse_t19 table.

gaussdb=# ALTER TABLE tpcds.warehouse_t19 ADD W_GOODS_CATEGORY varchar(30);

-- Add a check constraint to the tpcds.warehouse_t19 table.
gaussdb=# ALTER TABLE tpcds.warehouse_t19 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_STATE IS NOT NULL);

-- Use one statement to alter the types of two existing columns.
gaussdb=# ALTER TABLE tpcds.warehouse_t19
    ALTER COLUMN W_GOODS_CATEGORY TYPE varchar(80),
    ALTER COLUMN W_STREET_NAME TYPE varchar(100);

-- This statement is equivalent to the preceding statement.
gaussdb=# ALTER TABLE tpcds.warehouse_t19 MODIFY (W_GOODS_CATEGORY varchar(30), W_STREET_NAME varchar(60));

-- Add a not-null constraint to an existing column.
gaussdb=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY SET NOT NULL;

-- Remove not-null constraints from an existing column.
gaussdb=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY DROP NOT NULL;



-- Move a table to another tablespace.
gaussdb=# ALTER TABLE tpcds.warehouse_t19 SET TABLESPACE PG_DEFAULT; 
-- Create the joe schema.
gaussdb=# CREATE SCHEMA joe;

-- Move a table to another schema.
gaussdb=# ALTER TABLE tpcds.warehouse_t19 SET SCHEMA joe;

-- Rename an existing table.
gaussdb=# ALTER TABLE joe.warehouse_t19 RENAME TO warehouse_t23;

-- Delete a column from the warehouse_t23 table.
gaussdb=# ALTER TABLE joe.warehouse_t23 DROP COLUMN W_STREET_NAME;

-- Create an encryption table.
gaussdb=# CREATE TABLE creditcard_info (id_number    int, name text encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC), credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id_number' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

-- Delete the tablespace, schema joe, and schema tables warehouse.
gaussdb=# DROP TABLE tpcds.warehouse_t1;
gaussdb=# DROP TABLE tpcds.warehouse_t2;
gaussdb=# DROP TABLE tpcds.warehouse_t3;
gaussdb=# DROP TABLE tpcds.warehouse_t4;
gaussdb=# DROP TABLE tpcds.warehouse_t5;
gaussdb=# DROP TABLE tpcds.warehouse_t6;
gaussdb=# DROP TABLE tpcds.warehouse_t7;
gaussdb=# DROP TABLE tpcds.warehouse_t8;
gaussdb=# DROP TABLE tpcds.warehouse_t9;
gaussdb=# DROP TABLE tpcds.warehouse_t10;
gaussdb=# DROP TABLE tpcds.warehouse_t11;
gaussdb=# DROP TABLE tpcds.warehouse_t12;
gaussdb=# DROP TABLE tpcds.warehouse_t13;
gaussdb=# DROP TABLE tpcds.warehouse_t14;
gaussdb=# DROP TABLE tpcds.warehouse_t18;
gaussdb=# DROP TABLE tpcds.warehouse_t20;
gaussdb=# DROP TABLE tpcds.warehouse_t21;
gaussdb=# DROP TABLE tpcds.warehouse_t22;
gaussdb=# DROP TABLE joe.warehouse_t23;
gaussdb=# DROP TABLE tpcds.warehouse_t24;
gaussdb=# DROP TABLE tpcds.warehouse_t25;
gaussdb=# DROP TABLE tpcds.warehouse_t26;
gaussdb=# DROP TABLE tpcds.warehouse_t27;
gaussdb=# DROP TABLE tpcds.warehouse_t28;
gaussdb=# DROP TABLE creditcard_info;
gaussdb=# DROP TABLESPACE DS_TABLESPACE1;
gaussdb=# DROP SCHEMA IF EXISTS joe CASCADE;