示例
以下示例演示 Amazon Redshift CREATE TABLE 语句中的各种列和表属性。有关 CREATE TABLE 的更多信息,包括参数定义,请参阅 CREATE TABLE。
许多示例使用来自 TICKIT 示例数据集的表和数据。有关更多信息,请参阅示例数据库。
在 CREATE TABLE 命令中,您可以使用数据库名称和架构名称作为表名称前缀。例如,dev_database.public.sales
。数据库名称必须是您已连接到的数据库。在其他数据库中创建数据库对象的任何尝试都会失败,并出现无效的操作错误。
使用分配键、复合排序键和压缩创建表
以下示例利用为多个列定义的压缩在 TICKIT 数据库中创建 SALES 表。LISTID 声明为分配键,LISTID 和 SELLERID 声明为多列复合排序键。还为表定义了主键约束和外键约束。创建示例中的表之前,如果不存在约束,则可能需要向外键引用的每个列添加 UNIQUE 约束。
create table sales( salesid integer not null, listid integer not null, sellerid integer not null, buyerid integer not null, eventid integer not null encode mostly16, dateid smallint not null, qtysold smallint not null encode mostly8, pricepaid decimal(8,2) encode delta32k, commission decimal(8,2) encode delta32k, saletime timestamp, primary key(salesid), foreign key(listid) references listing(listid), foreign key(sellerid) references users(userid), foreign key(buyerid) references users(userid), foreign key(dateid) references date(dateid)) distkey(listid) compound sortkey(listid,sellerid);
结果如下:
schemaname | tablename | column | type | encoding | distkey | sortkey | notnull -----------+-----------+------------+-----------------------------+----------+---------+---------+-------- public | sales | salesid | integer | lzo | false | 0 | true public | sales | listid | integer | none | true | 1 | true public | sales | sellerid | integer | none | false | 2 | true public | sales | buyerid | integer | lzo | false | 0 | true public | sales | eventid | integer | mostly16 | false | 0 | true public | sales | dateid | smallint | lzo | false | 0 | true public | sales | qtysold | smallint | mostly8 | false | 0 | true public | sales | pricepaid | numeric(8,2) | delta32k | false | 0 | false public | sales | commission | numeric(8,2) | delta32k | false | 0 | false public | sales | saletime | timestamp without time zone | lzo | false | 0 | false
以下示例使用不区分大小写的列 col1 创建表 t1。
create table T1 ( col1 Varchar(20) collate case_insensitive ); insert into T1 values ('bob'), ('john'), ('Tom'), ('JOHN'), ('Bob');
查询表:
select * from T1 where col1 = 'John';
col1 ------ john JOHN (2 rows)
使用交错排序键创建表
以下示例使用交错排序键创建 CUSTOMER 表。
create table customer_interleaved ( c_custkey integer not null, c_name varchar(25) not null, c_address varchar(25) not null, c_city varchar(10) not null, c_nation varchar(15) not null, c_region varchar(12) not null, c_phone varchar(15) not null, c_mktsegment varchar(10) not null) diststyle all interleaved sortkey (c_custkey, c_city, c_mktsegment);
使用 IF NOT EXISTS 创建表
以下示例创建 CITIES 表,如果该表已存在,则不执行任何操作并返回一条消息:
create table if not exists cities( cityid integer not null, city varchar(100) not null, state char(2) not null);
使用 ALL 分配创建表
以下示例使用 ALL 分配创建 VENUE 表。
create table venue( venueid smallint not null, venuename varchar(100), venuecity varchar(30), venuestate char(2), venueseats integer, primary key(venueid)) diststyle all;
使用 EVEN 分配创建表
以下示例创建一个包含三个列的名为 MYEVENT 的表。
create table myevent( eventid int, eventname varchar(200), eventcity varchar(30)) diststyle even;
均匀分配表,并且不对表进行排序。表没有声明的 DISTKEY 或 SORTKEY 列。
select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'myevent';
column | type | encoding | distkey | sortkey -----------+------------------------+----------+---------+--------- eventid | integer | lzo | f | 0 eventname | character varying(200) | lzo | f | 0 eventcity | character varying(30) | lzo | f | 0 (3 rows)
创建与另一个表类似的临时表
以下示例创建一个名为“TEMPEVENT”的临时表,该表从 EVENT 表继承其列。
create temp table tempevent(like event);
此表还继承其父表的 DISTKEY 和 SORTKEY 属性:
select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 'tempevent';
column | type | encoding | distkey | sortkey -----------+-----------------------------+----------+---------+--------- eventid | integer | none | t | 1 venueid | smallint | none | f | 0 catid | smallint | none | f | 0 dateid | smallint | none | f | 0 eventname | character varying(200) | lzo | f | 0 starttime | timestamp without time zone | bytedict | f | 0 (6 rows)
创建具有 IDENTITY 列的表
以下示例创建一个名为 VENUE_IDENT 的表,该表具有名为 VENUEID 的 IDENTITY 列。该列从 0 开始,并为每个记录增加 1。VENUEID 还被声明为表的主键。
create table venue_ident(venueid bigint identity(0, 1), venuename varchar(100), venuecity varchar(30), venuestate char(2), venueseats integer, primary key(venueid));
创建具有默认 IDENTITY 列的表
下面的示例创建了一个名为 t1
的表。此表拥有名为 hist_id
的 IDENTITY 列和名为 base_id
的默认 IDENTITY 列。
CREATE TABLE t1( hist_id BIGINT IDENTITY NOT NULL, /* Cannot be overridden */ base_id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, /* Can be overridden */ business_key varchar(10) , some_field varchar(10) );
在表中插入一行,表明 hist_id
和 base_id
值均已生成。
INSERT INTO T1 (business_key, some_field) values ('A','MM');
SELECT * FROM t1;
hist_id | base_id | business_key | some_field ---------+---------+--------------+------------ 1 | 1 | A | MM
插入第二行,表明 base_id
的默认值已生成。
INSERT INTO T1 (base_id, business_key, some_field) values (DEFAULT, 'B','MNOP');
SELECT * FROM t1;
hist_id | base_id | business_key | some_field ---------+---------+--------------+------------ 1 | 1 | A | MM 2 | 2 | B | MNOP
插入第三行,表明 base_id
的值不需要是唯一的。
INSERT INTO T1 (base_id, business_key, some_field) values (2,'B','MNNN');
SELECT * FROM t1;
hist_id | base_id | business_key | some_field ---------+---------+--------------+------------ 1 | 1 | A | MM 2 | 2 | B | MNOP 3 | 2 | B | MNNN
创建具有 DEFAULT 列值的表
以下示例创建一个 CATEGORYDEF 表,该表声明每个列的默认值:
create table categorydef( catid smallint not null default 0, catgroup varchar(10) default 'Special', catname varchar(10) default 'Other', catdesc varchar(50) default 'Special events', primary key(catid)); insert into categorydef values(default,default,default,default);
select * from categorydef;
catid | catgroup | catname | catdesc -------+----------+---------+---------------- 0 | Special | Other | Special events (1 row)
DISTSTYLE、DISTKEY 和 SORTKEY 选项
以下示例显示 DISTKEY、SORTKEY 和 DISTSTYLE 选项的工作方式。在此示例中,COL1 是分配键;因此,必须将分配样式设置为 KEY 或不设置分配样式。默认情况下,该表没有排序键,所以不会进行排序:
create table t1(col1 int distkey, col2 int) diststyle key;
select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 't1';
column | type | encoding | distkey | sortkey -------+---------+----------+---------+--------- col1 | integer | az64 | t | 0 col2 | integer | az64 | f | 0
在以下示例中,将同一个列定义为分配键和排序键。同样,必须将分配样式设置为 KEY 或者不设置分配样式。
create table t2(col1 int distkey sortkey, col2 int);
select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 't2';
column | type | encoding | distkey | sortkey -------+---------+----------+---------+--------- col1 | integer | none | t | 1 col2 | integer | az64 | f | 0
在以下示例中,未将任何列设置为分配键,COL2 设置为排序键,分配键设置为 ALL:
create table t3(col1 int, col2 int sortkey) diststyle all;
select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 't3';
Column | Type | Encoding | DistKey | SortKey -------+---------+----------+---------+-------- col1 | integer | az64 | f | 0 col2 | integer | none | f | 1
在以下示例中,分配样式设置为 EVEN,并且未显式定义排序键;因此,表将均匀分配而不进行排序。
create table t4(col1 int, col2 int) diststyle even;
select "column", type, encoding, distkey, sortkey from pg_table_def where tablename = 't4';
column | type |encoding | distkey | sortkey --------+---------+---------+---------+-------- col1 | integer | az64 | f | 0 col2 | integer | az64 | f | 0
使用 ENCODE AUTO 选项创建表
下面的示例使用自动压缩编码创建表 t1
。不为任何列指定编码类型时,ENCODE AUTO 是表的默认设置。
create table t1(c0 int, c1 varchar);
下面的示例通过指定 ENCODE AUTO 使用自动压缩编码创建表 t2
。
create table t2(c0 int, c1 varchar) encode auto;
下面的示例通过指定 ENCODE AUTO 使用自动压缩编码创建表 t3
。列 c0
是用 DELTA 的初始编码类型定义的。如果另一个编码可以提供更好的查询性能,则 Amazon Redshift 可以更改编码。
create table t3(c0 int encode delta, c1 varchar) encode auto;
下面的示例通过指定 ENCODE AUTO 使用自动压缩编码创建表 t4
。列 c0
使用 DELTA 的初始编码进行定义,而列 c1
则是用 LZO 的初始编码定义的。如果另一个编码可以提供更好的查询性能,则 Amazon Redshift 可以更改这些编码。
create table t4(c0 int encode delta, c1 varchar encode lzo) encode auto;