创建表

在当前数据库中创建一个新的空白表,该表由命令执行者所有。

创建表时,如未指定表的存储方式,默认创建的是行存表;如未指定分布列时,取表的主键列(如果有的话)或首个可以作为分布列的列。

语法格式

  • 创建表。

    1. CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    2. ({ column_name data_type [ compress_mode ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    3. | table_constraint
    4. | LIKE source_table [ like_option [...] ] }
    5. [, ... ])
    6. [ WITH ( {storage_parameter = value} [, ... ] ) ]
    7. [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    8. [ COMPRESS | NOCOMPRESS ]
    9. [ TABLESPACE tablespace_name ];
    • 其中列约束column_constraint为:

      1. [ CONSTRAINT constraint_name ]
      2. { NOT NULL |
      3. NULL |
      4. CHECK ( expression ) |
      5. DEFAULT default_expr |
      6. UNIQUE index_parameters |
      7. PRIMARY KEY index_parameters }
      8. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    • 其中列的压缩可选项compress_mode为:

      1. { DELTA | PREFIX | DICTIONARY | NUMSTR | NOCOMPRESS }
    • 其中表约束table_constraint为:

      1. [ CONSTRAINT constraint_name ]
      2. { CHECK ( expression ) |
      3. UNIQUE ( column_name [, ... ] ) index_parameters |
      4. PRIMARY KEY ( column_name [, ... ] ) index_parameters |
      5. PARTIAL CLUSTER KEY ( column_name [, ... ] ) }
      6. [ DEFERRABLE | NOT DEFERRABLE | INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
    • 其中like选项like_option为:

      1. { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | PARTITION | RELOPTIONS | ALL }

其中索引参数index_parameters为:

  1. [ WITH ( {storage_parameter = value} [, ... ] ) ]
  2. [ USING INDEX TABLESPACE tablespace_name ]

示例

  1. --创建简单的表。
  2. postgres=# CREATE TABLE tpcds.warehouse_t1
  3. (
  4. W_WAREHOUSE_SK INTEGER NOT NULL,
  5. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  6. W_WAREHOUSE_NAME VARCHAR(20) ,
  7. W_WAREHOUSE_SQ_FT INTEGER ,
  8. W_STREET_NUMBER CHAR(10) ,
  9. W_STREET_NAME VARCHAR(60) ,
  10. W_STREET_TYPE CHAR(15) ,
  11. W_SUITE_NUMBER CHAR(10) ,
  12. W_CITY VARCHAR(60) ,
  13. W_COUNTY VARCHAR(30) ,
  14. W_STATE CHAR(2) ,
  15. W_ZIP CHAR(10) ,
  16. W_COUNTRY VARCHAR(20) ,
  17. W_GMT_OFFSET DECIMAL(5,2)
  18. );
  19. postgres=# CREATE TABLE tpcds.warehouse_t2
  20. (
  21. W_WAREHOUSE_SK INTEGER NOT NULL,
  22. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  23. W_WAREHOUSE_NAME VARCHAR(20) ,
  24. W_WAREHOUSE_SQ_FT INTEGER ,
  25. W_STREET_NUMBER CHAR(10) ,
  26. W_STREET_NAME VARCHAR(60) DICTIONARY,
  27. W_STREET_TYPE CHAR(15) ,
  28. W_SUITE_NUMBER CHAR(10) ,
  29. W_CITY VARCHAR(60) ,
  30. W_COUNTY VARCHAR(30) ,
  31. W_STATE CHAR(2) ,
  32. W_ZIP CHAR(10) ,
  33. W_COUNTRY VARCHAR(20) ,
  34. W_GMT_OFFSET DECIMAL(5,2)
  35. );
  1. --创建表,并指定W_STATE字段的缺省值为GA
  2. postgres=# CREATE TABLE tpcds.warehouse_t3
  3. (
  4. W_WAREHOUSE_SK INTEGER NOT NULL,
  5. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  6. W_WAREHOUSE_NAME VARCHAR(20) ,
  7. W_WAREHOUSE_SQ_FT INTEGER ,
  8. W_STREET_NUMBER CHAR(10) ,
  9. W_STREET_NAME VARCHAR(60) ,
  10. W_STREET_TYPE CHAR(15) ,
  11. W_SUITE_NUMBER CHAR(10) ,
  12. W_CITY VARCHAR(60) ,
  13. W_COUNTY VARCHAR(30) ,
  14. W_STATE CHAR(2) DEFAULT 'GA',
  15. W_ZIP CHAR(10) ,
  16. W_COUNTRY VARCHAR(20) ,
  17. W_GMT_OFFSET DECIMAL(5,2)
  18. );
  19. --创建表,并在事务结束时检查W_WAREHOUSE_NAME字段是否有重复。
  20. postgres=# CREATE TABLE tpcds.warehouse_t4
  21. (
  22. W_WAREHOUSE_SK INTEGER NOT NULL,
  23. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  24. W_WAREHOUSE_NAME VARCHAR(20) UNIQUE DEFERRABLE,
  25. W_WAREHOUSE_SQ_FT INTEGER ,
  26. W_STREET_NUMBER CHAR(10) ,
  27. W_STREET_NAME VARCHAR(60) ,
  28. W_STREET_TYPE CHAR(15) ,
  29. W_SUITE_NUMBER CHAR(10) ,
  30. W_CITY VARCHAR(60) ,
  31. W_COUNTY VARCHAR(30) ,
  32. W_STATE CHAR(2) ,
  33. W_ZIP CHAR(10) ,
  34. W_COUNTRY VARCHAR(20) ,
  35. W_GMT_OFFSET DECIMAL(5,2)
  36. );
  1. --创建一个带有70%填充因子的表。
  2. postgres=# CREATE TABLE tpcds.warehouse_t5
  3. (
  4. W_WAREHOUSE_SK INTEGER NOT NULL,
  5. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  6. W_WAREHOUSE_NAME VARCHAR(20) ,
  7. W_WAREHOUSE_SQ_FT INTEGER ,
  8. W_STREET_NUMBER CHAR(10) ,
  9. W_STREET_NAME VARCHAR(60) ,
  10. W_STREET_TYPE CHAR(15) ,
  11. W_SUITE_NUMBER CHAR(10) ,
  12. W_CITY VARCHAR(60) ,
  13. W_COUNTY VARCHAR(30) ,
  14. W_STATE CHAR(2) ,
  15. W_ZIP CHAR(10) ,
  16. W_COUNTRY VARCHAR(20) ,
  17. W_GMT_OFFSET DECIMAL(5,2),
  18. UNIQUE(W_WAREHOUSE_NAME) WITH(fillfactor=70)
  19. );
  20. --或者用下面的语法。
  21. postgres=# CREATE TABLE tpcds.warehouse_t6
  22. (
  23. W_WAREHOUSE_SK INTEGER NOT NULL,
  24. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  25. W_WAREHOUSE_NAME VARCHAR(20) UNIQUE,
  26. W_WAREHOUSE_SQ_FT INTEGER ,
  27. W_STREET_NUMBER CHAR(10) ,
  28. W_STREET_NAME VARCHAR(60) ,
  29. W_STREET_TYPE CHAR(15) ,
  30. W_SUITE_NUMBER CHAR(10) ,
  31. W_CITY VARCHAR(60) ,
  32. W_COUNTY VARCHAR(30) ,
  33. W_STATE CHAR(2) ,
  34. W_ZIP CHAR(10) ,
  35. W_COUNTRY VARCHAR(20) ,
  36. W_GMT_OFFSET DECIMAL(5,2)
  37. ) WITH(fillfactor=70);
  38. --创建表,并指定该表数据不写入预写日志。
  39. postgres=# CREATE UNLOGGED TABLE tpcds.warehouse_t7
  40. (
  41. W_WAREHOUSE_SK INTEGER NOT NULL,
  42. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  43. W_WAREHOUSE_NAME VARCHAR(20) ,
  44. W_WAREHOUSE_SQ_FT INTEGER ,
  45. W_STREET_NUMBER CHAR(10) ,
  46. W_STREET_NAME VARCHAR(60) ,
  47. W_STREET_TYPE CHAR(15) ,
  48. W_SUITE_NUMBER CHAR(10) ,
  49. W_CITY VARCHAR(60) ,
  50. W_COUNTY VARCHAR(30) ,
  51. W_STATE CHAR(2) ,
  52. W_ZIP CHAR(10) ,
  53. W_COUNTRY VARCHAR(20) ,
  54. W_GMT_OFFSET DECIMAL(5,2)
  55. );
  56. --创建表临时表。
  57. postgres=# CREATE TEMPORARY TABLE warehouse_t24
  58. (
  59. W_WAREHOUSE_SK INTEGER NOT NULL,
  60. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  61. W_WAREHOUSE_NAME VARCHAR(20) ,
  62. W_WAREHOUSE_SQ_FT INTEGER ,
  63. W_STREET_NUMBER CHAR(10) ,
  64. W_STREET_NAME VARCHAR(60) ,
  65. W_STREET_TYPE CHAR(15) ,
  66. W_SUITE_NUMBER CHAR(10) ,
  67. W_CITY VARCHAR(60) ,
  68. W_COUNTY VARCHAR(30) ,
  69. W_STATE CHAR(2) ,
  70. W_ZIP CHAR(10) ,
  71. W_COUNTRY VARCHAR(20) ,
  72. W_GMT_OFFSET DECIMAL(5,2)
  73. );
  74. --事务中创建表临时表,并指定提交事务时删除该临时表数据。
  75. postgres=# CREATE TEMPORARY TABLE warehouse_t25
  76. (
  77. W_WAREHOUSE_SK INTEGER NOT NULL,
  78. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  79. W_WAREHOUSE_NAME VARCHAR(20) ,
  80. W_WAREHOUSE_SQ_FT INTEGER ,
  81. W_STREET_NUMBER CHAR(10) ,
  82. W_STREET_NAME VARCHAR(60) ,
  83. W_STREET_TYPE CHAR(15) ,
  84. W_SUITE_NUMBER CHAR(10) ,
  85. W_CITY VARCHAR(60) ,
  86. W_COUNTY VARCHAR(30) ,
  87. W_STATE CHAR(2) ,
  88. W_ZIP CHAR(10) ,
  89. W_COUNTRY VARCHAR(20) ,
  90. W_GMT_OFFSET DECIMAL(5,2)
  91. ) ON COMMIT DELETE ROWS;
  92. --创建表时,不希望因为表已存在而报错。
  93. postgres=# CREATE TABLE IF NOT EXISTS tpcds.warehouse_t8
  94. (
  95. W_WAREHOUSE_SK INTEGER NOT NULL,
  96. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  97. W_WAREHOUSE_NAME VARCHAR(20) ,
  98. W_WAREHOUSE_SQ_FT INTEGER ,
  99. W_STREET_NUMBER CHAR(10) ,
  100. W_STREET_NAME VARCHAR(60) ,
  101. W_STREET_TYPE CHAR(15) ,
  102. W_SUITE_NUMBER CHAR(10) ,
  103. W_CITY VARCHAR(60) ,
  104. W_COUNTY VARCHAR(30) ,
  105. W_STATE CHAR(2) ,
  106. W_ZIP CHAR(10) ,
  107. W_COUNTRY VARCHAR(20) ,
  108. W_GMT_OFFSET DECIMAL(5,2)
  109. );
  110. --创建普通表空间。
  111. postgres=# CREATE TABLESPACE DS_TABLESPACE1 RELATIVE LOCATION 'tablespace/tablespace_1';
  112. --创建表时,指定表空间。
  113. postgres=# CREATE TABLE tpcds.warehouse_t9
  114. (
  115. W_WAREHOUSE_SK INTEGER NOT NULL,
  116. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  117. W_WAREHOUSE_NAME VARCHAR(20) ,
  118. W_WAREHOUSE_SQ_FT INTEGER ,
  119. W_STREET_NUMBER CHAR(10) ,
  120. W_STREET_NAME VARCHAR(60) ,
  121. W_STREET_TYPE CHAR(15) ,
  122. W_SUITE_NUMBER CHAR(10) ,
  123. W_CITY VARCHAR(60) ,
  124. W_COUNTY VARCHAR(30) ,
  125. W_STATE CHAR(2) ,
  126. W_ZIP CHAR(10) ,
  127. W_COUNTRY VARCHAR(20) ,
  128. W_GMT_OFFSET DECIMAL(5,2)
  129. ) TABLESPACE DS_TABLESPACE1;
  130. --创建表时,单独指定W_WAREHOUSE_NAME的索引表空间。
  131. postgres=# CREATE TABLE tpcds.warehouse_t10
  132. (
  133. W_WAREHOUSE_SK INTEGER NOT NULL,
  134. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  135. W_WAREHOUSE_NAME VARCHAR(20) UNIQUE USING INDEX TABLESPACE DS_TABLESPACE1,
  136. W_WAREHOUSE_SQ_FT INTEGER ,
  137. W_STREET_NUMBER CHAR(10) ,
  138. W_STREET_NAME VARCHAR(60) ,
  139. W_STREET_TYPE CHAR(15) ,
  140. W_SUITE_NUMBER CHAR(10) ,
  141. W_CITY VARCHAR(60) ,
  142. W_COUNTY VARCHAR(30) ,
  143. W_STATE CHAR(2) ,
  144. W_ZIP CHAR(10) ,
  145. W_COUNTRY VARCHAR(20) ,
  146. W_GMT_OFFSET DECIMAL(5,2)
  147. );
  1. --创建一个有主键约束的表。
  2. postgres=# CREATE TABLE tpcds.warehouse_t11
  3. (
  4. W_WAREHOUSE_SK INTEGER PRIMARY KEY,
  5. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  6. W_WAREHOUSE_NAME VARCHAR(20) ,
  7. W_WAREHOUSE_SQ_FT INTEGER ,
  8. W_STREET_NUMBER CHAR(10) ,
  9. W_STREET_NAME VARCHAR(60) ,
  10. W_STREET_TYPE CHAR(15) ,
  11. W_SUITE_NUMBER CHAR(10) ,
  12. W_CITY VARCHAR(60) ,
  13. W_COUNTY VARCHAR(30) ,
  14. W_STATE CHAR(2) ,
  15. W_ZIP CHAR(10) ,
  16. W_COUNTRY VARCHAR(20) ,
  17. W_GMT_OFFSET DECIMAL(5,2)
  18. );
  19. ---或是用下面的语法,效果完全一样。
  20. postgres=# CREATE TABLE tpcds.warehouse_t12
  21. (
  22. W_WAREHOUSE_SK INTEGER NOT NULL,
  23. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  24. W_WAREHOUSE_NAME VARCHAR(20) ,
  25. W_WAREHOUSE_SQ_FT INTEGER ,
  26. W_STREET_NUMBER CHAR(10) ,
  27. W_STREET_NAME VARCHAR(60) ,
  28. W_STREET_TYPE CHAR(15) ,
  29. W_SUITE_NUMBER CHAR(10) ,
  30. W_CITY VARCHAR(60) ,
  31. W_COUNTY VARCHAR(30) ,
  32. W_STATE CHAR(2) ,
  33. W_ZIP CHAR(10) ,
  34. W_COUNTRY VARCHAR(20) ,
  35. W_GMT_OFFSET DECIMAL(5,2),
  36. PRIMARY KEY(W_WAREHOUSE_SK)
  37. );
  38. --或是用下面的语法,指定约束的名称。
  39. postgres=# CREATE TABLE tpcds.warehouse_t13
  40. (
  41. W_WAREHOUSE_SK INTEGER NOT NULL,
  42. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  43. W_WAREHOUSE_NAME VARCHAR(20) ,
  44. W_WAREHOUSE_SQ_FT INTEGER ,
  45. W_STREET_NUMBER CHAR(10) ,
  46. W_STREET_NAME VARCHAR(60) ,
  47. W_STREET_TYPE CHAR(15) ,
  48. W_SUITE_NUMBER CHAR(10) ,
  49. W_CITY VARCHAR(60) ,
  50. W_COUNTY VARCHAR(30) ,
  51. W_STATE CHAR(2) ,
  52. W_ZIP CHAR(10) ,
  53. W_COUNTRY VARCHAR(20) ,
  54. W_GMT_OFFSET DECIMAL(5,2),
  55. CONSTRAINT W_CSTR_KEY1 PRIMARY KEY(W_WAREHOUSE_SK)
  56. );
  57. --创建一个有复合主键约束的表。
  58. postgres=# CREATE TABLE tpcds.warehouse_t14
  59. (
  60. W_WAREHOUSE_SK INTEGER NOT NULL,
  61. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  62. W_WAREHOUSE_NAME VARCHAR(20) ,
  63. W_WAREHOUSE_SQ_FT INTEGER ,
  64. W_STREET_NUMBER CHAR(10) ,
  65. W_STREET_NAME VARCHAR(60) ,
  66. W_STREET_TYPE CHAR(15) ,
  67. W_SUITE_NUMBER CHAR(10) ,
  68. W_CITY VARCHAR(60) ,
  69. W_COUNTY VARCHAR(30) ,
  70. W_STATE CHAR(2) ,
  71. W_ZIP CHAR(10) ,
  72. W_COUNTRY VARCHAR(20) ,
  73. W_GMT_OFFSET DECIMAL(5,2),
  74. CONSTRAINT W_CSTR_KEY2 PRIMARY KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)
  75. );
  76. --创建列存表。
  77. postgres=# CREATE TABLE tpcds.warehouse_t15
  78. (
  79. W_WAREHOUSE_SK INTEGER NOT NULL,
  80. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  81. W_WAREHOUSE_NAME VARCHAR(20) ,
  82. W_WAREHOUSE_SQ_FT INTEGER ,
  83. W_STREET_NUMBER CHAR(10) ,
  84. W_STREET_NAME VARCHAR(60) ,
  85. W_STREET_TYPE CHAR(15) ,
  86. W_SUITE_NUMBER CHAR(10) ,
  87. W_CITY VARCHAR(60) ,
  88. W_COUNTY VARCHAR(30) ,
  89. W_STATE CHAR(2) ,
  90. W_ZIP CHAR(10) ,
  91. W_COUNTRY VARCHAR(20) ,
  92. W_GMT_OFFSET DECIMAL(5,2)
  93. ) WITH (ORIENTATION = COLUMN);
  94. --创建局部聚簇存储的列存表。
  95. postgres=# CREATE TABLE tpcds.warehouse_t16
  96. (
  97. W_WAREHOUSE_SK INTEGER NOT NULL,
  98. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  99. W_WAREHOUSE_NAME VARCHAR(20) ,
  100. W_WAREHOUSE_SQ_FT INTEGER ,
  101. W_STREET_NUMBER CHAR(10) ,
  102. W_STREET_NAME VARCHAR(60) ,
  103. W_STREET_TYPE CHAR(15) ,
  104. W_SUITE_NUMBER CHAR(10) ,
  105. W_CITY VARCHAR(60) ,
  106. W_COUNTY VARCHAR(30) ,
  107. W_STATE CHAR(2) ,
  108. W_ZIP CHAR(10) ,
  109. W_COUNTRY VARCHAR(20) ,
  110. W_GMT_OFFSET DECIMAL(5,2),
  111. PARTIAL CLUSTER KEY(W_WAREHOUSE_SK, W_WAREHOUSE_ID)
  112. ) WITH (ORIENTATION = COLUMN);
  113. --定义一个带压缩的列存表。
  114. postgres=# CREATE TABLE tpcds.warehouse_t17
  115. (
  116. W_WAREHOUSE_SK INTEGER NOT NULL,
  117. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  118. W_WAREHOUSE_NAME VARCHAR(20) ,
  119. W_WAREHOUSE_SQ_FT INTEGER ,
  120. W_STREET_NUMBER CHAR(10) ,
  121. W_STREET_NAME VARCHAR(60) ,
  122. W_STREET_TYPE CHAR(15) ,
  123. W_SUITE_NUMBER CHAR(10) ,
  124. W_CITY VARCHAR(60) ,
  125. W_COUNTY VARCHAR(30) ,
  126. W_STATE CHAR(2) ,
  127. W_ZIP CHAR(10) ,
  128. W_COUNTRY VARCHAR(20) ,
  129. W_GMT_OFFSET DECIMAL(5,2)
  130. ) WITH (ORIENTATION = COLUMN, COMPRESSION=HIGH);
  131. --定义一个带压缩的表。
  132. postgres=# CREATE TABLE tpcds.warehouse_t18
  133. (
  134. W_WAREHOUSE_SK INTEGER NOT NULL,
  135. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  136. W_WAREHOUSE_NAME VARCHAR(20) ,
  137. W_WAREHOUSE_SQ_FT INTEGER ,
  138. W_STREET_NUMBER CHAR(10) ,
  139. W_STREET_NAME VARCHAR(60) ,
  140. W_STREET_TYPE CHAR(15) ,
  141. W_SUITE_NUMBER CHAR(10) ,
  142. W_CITY VARCHAR(60) ,
  143. W_COUNTY VARCHAR(30) ,
  144. W_STATE CHAR(2) ,
  145. W_ZIP CHAR(10) ,
  146. W_COUNTRY VARCHAR(20) ,
  147. W_GMT_OFFSET DECIMAL(5,2)
  148. ) COMPRESS;
  149. --定义一个检查列约束。
  150. postgres=# CREATE TABLE tpcds.warehouse_t19
  151. (
  152. W_WAREHOUSE_SK INTEGER PRIMARY KEY CHECK (W_WAREHOUSE_SK > 0),
  153. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  154. W_WAREHOUSE_NAME VARCHAR(20) CHECK (W_WAREHOUSE_NAME IS NOT NULL),
  155. W_WAREHOUSE_SQ_FT INTEGER ,
  156. W_STREET_NUMBER CHAR(10) ,
  157. W_STREET_NAME VARCHAR(60) ,
  158. W_STREET_TYPE CHAR(15) ,
  159. W_SUITE_NUMBER CHAR(10) ,
  160. W_CITY VARCHAR(60) ,
  161. W_COUNTY VARCHAR(30) ,
  162. W_STATE CHAR(2) ,
  163. W_ZIP CHAR(10) ,
  164. W_COUNTRY VARCHAR(20) ,
  165. W_GMT_OFFSET DECIMAL(5,2)
  166. );
  167. postgres=# CREATE TABLE tpcds.warehouse_t20
  168. (
  169. W_WAREHOUSE_SK INTEGER PRIMARY KEY,
  170. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  171. W_WAREHOUSE_NAME VARCHAR(20) CHECK (W_WAREHOUSE_NAME IS NOT NULL),
  172. W_WAREHOUSE_SQ_FT INTEGER ,
  173. W_STREET_NUMBER CHAR(10) ,
  174. W_STREET_NAME VARCHAR(60) ,
  175. W_STREET_TYPE CHAR(15) ,
  176. W_SUITE_NUMBER CHAR(10) ,
  177. W_CITY VARCHAR(60) ,
  178. W_COUNTY VARCHAR(30) ,
  179. W_STATE CHAR(2) ,
  180. W_ZIP CHAR(10) ,
  181. W_COUNTRY VARCHAR(20) ,
  182. W_GMT_OFFSET DECIMAL(5,2),
  183. CONSTRAINT W_CONSTR_KEY2 CHECK(W_WAREHOUSE_SK > 0 AND W_WAREHOUSE_NAME IS NOT NULL)
  184. );
  185. --定义一个表,表中每一个行存在数据库节点中。
  186. postgres=# CREATE TABLE tpcds.warehouse_t21
  187. (
  188. W_WAREHOUSE_SK INTEGER NOT NULL,
  189. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  190. W_WAREHOUSE_NAME VARCHAR(20) ,
  191. W_WAREHOUSE_SQ_FT INTEGER ,
  192. W_STREET_NUMBER CHAR(10) ,
  193. W_STREET_NAME VARCHAR(60) ,
  194. W_STREET_TYPE CHAR(15) ,
  195. W_SUITE_NUMBER CHAR(10) ,
  196. W_CITY VARCHAR(60) ,
  197. W_COUNTY VARCHAR(30) ,
  198. W_STATE CHAR(2) ,
  199. W_ZIP CHAR(10) ,
  200. W_COUNTRY VARCHAR(20) ,
  201. W_GMT_OFFSET DECIMAL(5,2)
  202. );
  203. --定义一个表,使用HASH分布。
  204. postgres=# CREATE TABLE tpcds.warehouse_t22
  205. (
  206. W_WAREHOUSE_SK INTEGER NOT NULL,
  207. W_WAREHOUSE_ID CHAR(16) NOT NULL,
  208. W_WAREHOUSE_NAME VARCHAR(20) ,
  209. W_WAREHOUSE_SQ_FT INTEGER ,
  210. W_STREET_NUMBER CHAR(10) ,
  211. W_STREET_NAME VARCHAR(60) ,
  212. W_STREET_TYPE CHAR(15) ,
  213. W_SUITE_NUMBER CHAR(10) ,
  214. W_CITY VARCHAR(60) ,
  215. W_COUNTY VARCHAR(30) ,
  216. W_STATE CHAR(2) ,
  217. W_ZIP CHAR(10) ,
  218. W_COUNTRY VARCHAR(20) ,
  219. W_GMT_OFFSET DECIMAL(5,2),
  220. CONSTRAINT W_CONSTR_KEY3 UNIQUE(W_WAREHOUSE_SK)
  221. );
  222. --向tpcds.warehouse_t19表中增加一个varchar列。
  1. postgres=# ALTER TABLE tpcds.warehouse_t19 ADD W_GOODS_CATEGORY varchar(30);
  2. --给tpcds.warehouse_t19表增加一个检查约束。
  3. postgres=# ALTER TABLE tpcds.warehouse_t19 ADD CONSTRAINT W_CONSTR_KEY4 CHECK (W_STATE IS NOT NULL);
  4. --在一个操作中改变两个现存字段的类型。
  5. postgres=# ALTER TABLE tpcds.warehouse_t19
  6. ALTER COLUMN W_GOODS_CATEGORY TYPE varchar(80),
  7. ALTER COLUMN W_STREET_NAME TYPE varchar(100);
  8. --此语句与上面语句等效。
  9. postgres=# ALTER TABLE tpcds.warehouse_t19 MODIFY (W_GOODS_CATEGORY varchar(30), W_STREET_NAME varchar(60));
  10. --给一个已存在字段添加非空约束。
  11. postgres=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY SET NOT NULL;
  12. --移除已存在字段的非空约束。
  13. postgres=# ALTER TABLE tpcds.warehouse_t19 ALTER COLUMN W_GOODS_CATEGORY DROP NOT NULL;
  14. --如果列存表中还未指定局部聚簇,向在一个列存表中添加局部聚簇列。
  15. postgres=# ALTER TABLE tpcds.warehouse_t17 ADD PARTIAL CLUSTER KEY(W_WAREHOUSE_SK);
  16. --查看约束的名称,并删除一个列存表中的局部聚簇列。
  17. postgres=# \d+ tpcds.warehouse_t17
  18. Table "tpcds.warehouse_t17"
  19. Column | Type | Modifiers | Storage | Stats target | Description
  20. -------------------+-----------------------+-----------+----------+--------------+-------------
  21. w_warehouse_sk | integer | not null | plain | |
  22. w_warehouse_id | character(16) | not null | extended | |
  23. w_warehouse_name | character varying(20) | | extended | |
  24. w_warehouse_sq_ft | integer | | plain | |
  25. w_street_number | character(10) | | extended | |
  26. w_street_name | character varying(60) | | extended | |
  27. w_street_type | character(15) | | extended | |
  28. w_suite_number | character(10) | | extended | |
  29. w_city | character varying(60) | | extended | |
  30. w_county | character varying(30) | | extended | |
  31. w_state | character(2) | | extended | |
  32. w_zip | character(10) | | extended | |
  33. w_country | character varying(20) | | extended | |
  34. w_gmt_offset | numeric(5,2) | | main | |
  35. Partial Cluster :
  36. "warehouse_t17_cluster" PARTIAL CLUSTER KEY (w_warehouse_sk)
  37. Has OIDs: no
  38. Location Nodes: ALL DATANODES
  39. Options: compression=no, version=0.12
  40. postgres=# ALTER TABLE tpcds.warehouse_t17 DROP CONSTRAINT warehouse_t17_cluster;
  41. --将表移动到另一个表空间。
  42. postgres=# ALTER TABLE tpcds.warehouse_t19 SET TABLESPACE PG_DEFAULT;
  43. --创建模式joe
  44. postgres=# CREATE SCHEMA joe;
  45. --将表移动到另一个模式中。
  46. postgres=# ALTER TABLE tpcds.warehouse_t19 SET SCHEMA joe;
  47. --重命名已存在的表。
  48. postgres=# ALTER TABLE joe.warehouse_t19 RENAME TO warehouse_t23;
  49. --从warehouse_t23表中删除一个字段。
  50. postgres=# ALTER TABLE joe.warehouse_t23 DROP COLUMN W_STREET_NAME;
  51. --删除表空间、模式joe和模式表warehouse
  52. postgres=# DROP TABLE tpcds.warehouse_t1;
  53. postgres=# DROP TABLE tpcds.warehouse_t2;
  54. postgres=# DROP TABLE tpcds.warehouse_t3;
  55. postgres=# DROP TABLE tpcds.warehouse_t4;
  56. postgres=# DROP TABLE tpcds.warehouse_t5;
  57. postgres=# DROP TABLE tpcds.warehouse_t6;
  58. postgres=# DROP TABLE tpcds.warehouse_t7;
  59. postgres=# DROP TABLE tpcds.warehouse_t8;
  60. postgres=# DROP TABLE tpcds.warehouse_t9;
  61. postgres=# DROP TABLE tpcds.warehouse_t10;
  62. postgres=# DROP TABLE tpcds.warehouse_t11;
  63. postgres=# DROP TABLE tpcds.warehouse_t12;
  64. postgres=# DROP TABLE tpcds.warehouse_t13;
  65. postgres=# DROP TABLE tpcds.warehouse_t14;
  66. postgres=# DROP TABLE tpcds.warehouse_t15;
  67. postgres=# DROP TABLE tpcds.warehouse_t16;
  68. postgres=# DROP TABLE tpcds.warehouse_t17;
  69. postgres=# DROP TABLE tpcds.warehouse_t18;
  70. postgres=# DROP TABLE tpcds.warehouse_t20;
  71. postgres=# DROP TABLE tpcds.warehouse_t21;
  72. postgres=# DROP TABLE tpcds.warehouse_t22;
  73. postgres=# DROP TABLE joe.warehouse_t23;
  74. postgres=# DROP TABLE tpcds.warehouse_t24;
  75. postgres=# DROP TABLE tpcds.warehouse_t25;
  76. postgres=# DROP TABLESPACE DS_TABLESPACE1;
  77. postgres=# DROP SCHEMA IF EXISTS joe CASCADE;