例
次の例は、Amazon Redshift CREATE TABLE ステートメントのさまざまな列とテーブルの属性を示しています。パラメータ定義を含め、CREATE TABLE の詳細については、「CREATE TABLE」を参照してください。
例の多くは、TICKIT サンプルデータセットのテーブルとデータを使用しています。詳細については、「サンプルデータベース」を参照してください。
CREATE TABLE コマンドでは、テーブル名の前にデータベース名とスキーマ名を付けることができます。例えば、dev_database.public.sales。データベース名は、接続しているデータベースでなければなりません。別のデータベースにデータベースオブジェクトを作成しようとすると、無効な操作エラーで失敗します。
分散キー、複合ソートキー、圧縮を使用してテーブルを作成する
次の例では、複数の列に圧縮が定義された SALES テーブルを TICKIT データベースに作成します。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 分散を指定したテーブルの作成
次の例では、3 つの列を持つ 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)
別のテーブルの LIKE である一時テーブルの作成
次の例では、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
2 番目の行を挿入すると、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
3 番目の行を挿入すると、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;
