我的书签
添加书签
移除书签创建表
创建表时，如未指定表的存储方式，默认创建的是行存表；如未指定分布列时，取表的主键列（如果有的话）或首个可以作为分布列的列。
-
其中列约束column_constraint为：
{ NOT NULL |NULL |CHECK ( expression ) |DEFAULT default_expr |UNIQUE index_parameters |PRIMARY KEY index_parameters }[ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
-
{ DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
其中表约束table_constraint为：
-
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }
其中索引参数index_parameters为：
[ WITH ( {storage_parameter = value} [, ... ] ) ][ USING INDEX TABLESPACE tablespace_name ]
示例
--创建表，并指定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_NAME VARCHAR(20) ,W_WAREHOUSE_SQ_FT INTEGER ,W_STREET_NUMBER CHAR(10) ,W_STREET_NAME VARCHAR(60) ,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_NAME VARCHAR(60) ,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=# 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;
