我的书签
添加书签
移除书签简单数据管理
创建数据库
创建一个新的数据库。缺省情况下新数据库将通过复制标准系统数据库template0来创建，且仅支持使用template0来创建。
注意事项
- 只有拥有CREATEDB权限的用户才可以创建新数据库，系统管理员默认拥有此权限。
- 不能在事务块中执行创建数据库语句。
- 在创建数据库过程中，若出现类似“could not initialize database directory”的错误提示，可能是由于文件系统上数据目录的权限不足或磁盘满等原因引起。
语法格式
CREATE DATABASE database_name[ [ WITH ] { [ OWNER [=] user_name ] |[ TEMPLATE [=] template ] |[ ENCODING [=] encoding ] |[ LC_COLLATE [=] lc_collate ] |[ LC_CTYPE [=] lc_ctype ] |[ DBCOMPATIBILITY [=] compatibilty_type ] |[ TABLESPACE [=] tablespace_name ] |[ CONNECTION LIMIT [=] connlimit ]}[...] ];
示例
--创建jim和tom用户。postgres=# CREATE USER jim PASSWORD 'Bigdata@123';postgres=# CREATE USER tom PASSWORD 'Bigdata@123';--创建一个GBK编码的数据库music（本地环境的编码格式必须也为GBK）。postgres=# CREATE DATABASE music ENCODING 'GBK' template = template0;--创建数据库music2，并指定所有者为jim。postgres=# CREATE DATABASE music2 OWNER jim;--用模板template0创建数据库music3，并指定所有者为jim。postgres=# CREATE DATABASE music3 OWNER jim TEMPLATE template0;--设置music数据库的连接数为10。postgres=# ALTER DATABASE music CONNECTION LIMIT= 10;--将music名称改为music4。postgres=# ALTER DATABASE music RENAME TO music4;--将数据库music2的所属者改为tom。postgres=# ALTER DATABASE music2 OWNER TO tom;--设置music3的表空间为PG_DEFAULT。postgres=# ALTER DATABASE music3 SET TABLESPACE PG_DEFAULT;--关闭在数据库music3上缺省的索引扫描。postgres=# ALTER DATABASE music3 SET enable_indexscan TO off;--重置enable_indexscan参数。postgres=# ALTER DATABASE music3 RESET enable_indexscan;--删除数据库。postgres=# DROP DATABASE music2;postgres=# DROP DATABASE music3;postgres=# DROP DATABASE music4;--删除jim和tom用户。postgres=# DROP USER jim;postgres=# DROP USER tom;--创建兼容TD格式的数据库。postgres=# CREATE DATABASE td_compatible_db DBCOMPATIBILITY 'C';--创建兼容ORA格式的数据库。postgres=# CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'A';--删除兼容TD、ORA格式的数据库。postgres=# DROP DATABASE td_compatible_db;postgres=# DROP DATABASE ora_compatible_db;
创建表
在当前数据库中创建一个新的空白表，该表由命令执行者所有。
创建表时，如未指定表的存储方式，默认创建的是行存表；如未指定分布列时，取表的主键列（如果有的话）或首个可以作为分布列的列。
语法格式
创建表。
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 GLOBAL TEMPORARY TABLE gtt1(ID INTEGER NOT NULL,NAME CHAR(16) NOT NULL,ADDRESS VARCHAR(50) ,POSTCODE CHAR(6)) ON COMMIT PRESERVE 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;
查看对象
查看帮助信息
\?
切换数据库
\c dbname
列举数据库
\l
列举表
\dt
查看表结构
\d tablename
列举schema
\dn
查看索引
\di
