我的书签
添加书签
移除书签创建表
在当前数据库中创建一个新的空白表，该表由命令执行者所有。
创建表时，如未指定表的存储方式，默认创建的是行存表；如未指定分布列时，取表的主键列（如果有的话）或首个可以作为分布列的列。
语法格式
创建表。
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]| table_constraint| LIKE source_table [ like_option [...] ] }[, ... ])[ WITH ( {storage_parameter = value} [, ... ] ) ][ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ][ COMPRESS | NOCOMPRESS ][ TABLESPACE tablespace_name ];
其中列约束column_constraint为：
[ CONSTRAINT constraint_name ]{ NOT NULL |NULL |CHECK ( expression ) |DEFAULT default_expr |UNIQUE index_parameters |PRIMARY KEY index_parameters }[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
其中列的压缩可选项compress_mode为：
{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
其中表约束table_constraint为：
[ CONSTRAINT constraint_name ]{ CHECK ( expression ) |UNIQUE ( column_name [, ... ] ) index_parameters |PRIMARY KEY ( column_name [, ... ] ) index_parameters |PARTIAL CLUSTER KEY ( column_name [, ... ] ) }[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
其中like选项like_option为：
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }
其中索引参数index_parameters为：
[ WITH ( {storage_parameter = value} [, ... ] ) ][ USING INDEX TABLESPACE tablespace_name ]
示例
--创建简单的表。postgres=# 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));postgres=# 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));
--创建表，并指定W_STATE字段的缺省值为GA。postgres=# 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));--创建表，并在事务结束时检查W_WAREHOUSE_NAME字段是否有重复。postgres=# 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));
--创建一个带有70%填充因子的表。postgres=# 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));--或者用下面的语法。postgres=# 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);--创建表，并指定该表数据不写入预写日志。postgres=# 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));--创建表临时表。postgres=# 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));--事务中创建表临时表，并指定提交事务时删除该临时表数据。postgres=# 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;--创建表时，不希望因为表已存在而报错。postgres=# 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));--创建普通表空间。postgres=# CREATE TABLESPACE DS_TABLESPACE1 RELATIVE LOCATION 'tablespace/tablespace_1';--创建表时，指定表空间。postgres=# 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;--创建表时，单独指定W_WAREHOUSE_NAME的索引表空间。postgres=# 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));
--创建一个有主键约束的表。postgres=# 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));---或是用下面的语法，效果完全一样。postgres=# 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));--或是用下面的语法，指定约束的名称。postgres=# 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));--创建一个有复合主键约束的表。postgres=# 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));--创建列存表。postgres=# CREATE TABLE tpcds.warehouse_t15(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)) WITH (ORIENTATION = COLUMN);--创建局部聚簇存储的列存表。postgres=# CREATE TABLE tpcds.warehouse_t16(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),PARTIAL CLUSTER KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)) WITH (ORIENTATION = COLUMN);--定义一个带压缩的列存表。postgres=# CREATE TABLE tpcds.warehouse_t17(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)) WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH);--定义一个带压缩的表。postgres=# CREATE TABLE tpcds.warehouse_t18(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)) COMPRESS;--定义一个检查列约束。postgres=# 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));postgres=# 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));--定义一个表，表中每一个行存在数据库节点中。postgres=# 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));--定义一个表，使用HASH分布。postgres=# 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));--向tpcds.warehouse_t19表中增加一个varchar列。
postgres=# ALTER TABLE tpcds.warehouse_t19 ADD W_GOODS_CATEGORY varchar(30);--给tpcds.warehouse_t19表增加一个检查约束。postgres=# ALTER TABLE tpcds.warehouse_t19 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_STATE IS NOT NULL);--在一个操作中改变两个现存字段的类型。postgres=# ALTER TABLE tpcds.warehouse_t19ALTER COLUMN W_GOODS_CATEGORY TYPE varchar(80),ALTER COLUMN W_STREET_NAME TYPE varchar(100);--此语句与上面语句等效。postgres=# ALTER TABLE tpcds.warehouse_t19 MODIFY (W_GOODS_CATEGORY varchar(30), W_STREET_NAME varchar(60));--给一个已存在字段添加非空约束。postgres=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY SET NOT NULL;--移除已存在字段的非空约束。postgres=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY DROP NOT NULL;--如果列存表中还未指定局部聚簇，向在一个列存表中添加局部聚簇列。postgres=# ALTER TABLE tpcds.warehouse_t17 ADD PARTIAL CLUSTER KEY(W_WAREHOUSE_SK);--查看约束的名称，并删除一个列存表中的局部聚簇列。postgres=# \d+ tpcds.warehouse_t17Table "tpcds.warehouse_t17"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 | |Partial Cluster :"warehouse_t17_cluster" PARTIAL CLUSTER KEY (w_warehouse_sk)Has OIDs: noLocation Nodes: ALL DATANODESOptions: compression=no, version=0.12postgres=# ALTER TABLE tpcds.warehouse_t17 DROP CONSTRAINT warehouse_t17_cluster;--将表移动到另一个表空间。postgres=# ALTER TABLE tpcds.warehouse_t19 SET TABLESPACE PG_DEFAULT;--创建模式joe。postgres=# CREATE SCHEMA joe;--将表移动到另一个模式中。postgres=# ALTER TABLE tpcds.warehouse_t19 SET SCHEMA joe;--重命名已存在的表。postgres=# ALTER TABLE joe.warehouse_t19 RENAME TO warehouse_t23;--从warehouse_t23表中删除一个字段。postgres=# ALTER TABLE joe.warehouse_t23 DROP COLUMN W_STREET_NAME;--删除表空间、模式joe和模式表warehouse。postgres=# DROP TABLE tpcds.warehouse_t1;postgres=# DROP TABLE tpcds.warehouse_t2;postgres=# DROP TABLE tpcds.warehouse_t3;postgres=# DROP TABLE tpcds.warehouse_t4;postgres=# DROP TABLE tpcds.warehouse_t5;postgres=# DROP TABLE tpcds.warehouse_t6;postgres=# DROP TABLE tpcds.warehouse_t7;postgres=# DROP TABLE tpcds.warehouse_t8;postgres=# DROP TABLE tpcds.warehouse_t9;postgres=# DROP TABLE tpcds.warehouse_t10;postgres=# DROP TABLE tpcds.warehouse_t11;postgres=# DROP TABLE tpcds.warehouse_t12;postgres=# DROP TABLE tpcds.warehouse_t13;postgres=# DROP TABLE tpcds.warehouse_t14;postgres=# DROP TABLE tpcds.warehouse_t15;postgres=# DROP TABLE tpcds.warehouse_t16;postgres=# DROP TABLE tpcds.warehouse_t17;postgres=# DROP TABLE tpcds.warehouse_t18;postgres=# DROP TABLE tpcds.warehouse_t20;postgres=# DROP TABLE tpcds.warehouse_t21;postgres=# DROP TABLE tpcds.warehouse_t22;postgres=# DROP TABLE joe.warehouse_t23;postgres=# DROP TABLE tpcds.warehouse_t24;postgres=# DROP TABLE tpcds.warehouse_t25;postgres=# DROP TABLESPACE DS_TABLESPACE1;postgres=# DROP SCHEMA IF EXISTS joe CASCADE;
