PostgreSQL テーブル作成/削除

テーブル作成/削除

・作成
    DB=> create  table  テーブル名 (
                    列名  データ型  [制約, ...],
                    列名  データ型  [制約, ...],
                            ・
                            ・
                );
・データ型
serial通し番号(1 ~ 2147483647)
bigserial通し番号(1 ~ 9223372036854775807)
smallint整数(-32768 ~ +32767)
integer, int整数(-2147483648 ~ +2147483647)
bigint整数(± 9223372036854775807)
numeric(全桁数, 小数点以下桁数)数値(低速)
"char"1バイト1文字
char(文字数)固定長文字列(低速)
varchar(文字数)制限付き可変長文字列(文字数以下)
text制限なし可変長文字列
boolean真偽値(1,0, yes,no, などを t,f で保存)
date日付(現在の値 : current_date)
time[(秒小数以下)] [with time zone] 時刻(現在の値 : current_time)
timestamp[(秒小数以下)] [with time zone] 日時(現在の値 : current_timestamp)
inetIPv4, IPv6

数値型以外のデータ型の列では、挿入、検索するデータが数値でも'(シングルクォート)で囲む。

・確認
    [user]$ pg_dump  -U  ユーザー名  --schema-only  データベース名  --table=テーブル名

    DB=> \d  テーブル名
・削除
    DB=> drop  table  テーブル名;

シーケンス(通し番号)

・作成
    DB=> create  sequence  シーケンス名
                [increment  増加数(省略時 1)]
                [minvalue   最小値(省略時 1)]
                [maxvalue   最大値(省略時 2147483647)]
                [start      初期値(省略時 minvalue)]
                [cycle(最大値に達した時に初期値に戻る)];
・確認
    DB=> \ds
・値の取り出し

現在の値

    DB=> select  last_value  from  シーケンス名;

次の値(現在の値 + 増加数)

    DB=> select  nextval ('シーケンス名');
    DB=> insert  into  テーブル名 (列名)  values  ( nextval ('シーケンス名') );

値のリセット

    DB=> select  setval ('シーケンス名', 現在の値);
・削除
    DB=> drop  sequence  シーケンス名;

制約

    DB=> create  table  member (
                    no    serial  primary  key,
                    flag  smallint  not  null  check  ( flag  in  (1,0) ),
                    id    varchar(15)  not  null  unique,
                    name  varchar(30)  not  null,
                    score smallint not null default 50
                );
・NOT NULL 制約(not null)

列が NULL 値を取れなくなる。

・デフォルト値
    create  table  テーブル名 (
        列名  データ型  not  null  default  デフォルト値,
    );
・一意性制約(unique)

列のデータが、テーブル内の全ての行で一意となる。NULL 値は例外。

・主キー(primary key)

not null と unique を合わせたもの、重複も NULL 値も許されない。

列または列のグループを、行の一意の識別子として使用できる。

    連結主キー
    create  table  テーブル名 (
        列名  データ型,
        列名  データ型,
        primary  key  (列名, 列名)
    );
・検査制約(check)

列の値が条件を満たすように指定。 検査式が真または NULL 値の場合のみ、データが挿入される。 制約名を省略した場合、自動的に設定される。

    create table テーブル名 (
        列1  データ型  not null  [constraint 制約名]  check (列名1 > 0),  # 列制約
        列2  データ型  not null  [constraint 制約名]  check (列名2 > 0),  # 列制約
        [constraint 制約名]  check (列1 > 列2)  # テーブル制約
    );
・参照整合性制約

列の値が、他のテーブルの列と一致しなければならない事を指定。 参照する側(参照テーブルの参照列)は、参照される側(被参照テーブルの被参照列) にない値を挿入できなくなる。 複数のテーブルを関連付ける、列を外部キーと呼ぶ。 制約名を省略した場合、自動的に設定される。

    create  table  参照テーブル (
            参照列  データ型  [constraint  制約名]  references  被参照テーブル(被参照列),
    );

    create  table  参照テーブル (
            参照列  データ型  制約,
                    ・
            [constraint  制約名]  foreign  key( 参照列,... )  references  被参照テーブル( 被参照列,... )
    );

    参照される側(被参照テーブルの被参照列)の変化に対応したい場合

    on  update  cascade  # 被参照列の値が変更されると参照列の値も変更される
    on  delete  cascade  # 被参照列の行が削除されると参照列の行も削除される

    references  被参照テーブル(被参照列)  on  update  cascade  on  delete  cascade,

インデックス

検索の高速化。primary key, unique 制約を設定した列には、自動的にインデックスが作成される。

・作成
    DB=> create  index  インデックス名  on  テーブル名 (列名);

    where 句に複数の列が and 条件で結合している場合、複合インデックス
    DB=> create  index  インデックス名  on  テーブル名 (列名,列名,...);
・確認
    DB=> \di
・削除
    DB=> drop  index  インデックス名;

テーブルの変更

・テーブル名の変更
    DB=> alter  table  元テーブル名  rename  to  新テーブル名;
・列名の変更
    DB=> alter  table  テーブル名  rename  元列名  to  新列名;
・データ型の変更

変更前に列に関する制約を全て削除し、変更後に適切な制約を付け直す。

    DB=> alter  table テーブル名  alter  列名  type  データ型;

    varchar 型を integer 型に変更
    DB=> alter  table  テーブル名  alter  列名  type  integer  using  cast (列名  as  integer);
・列の追加
    DB=> alter  table  テーブル名  add  列名  データ型  [制約];
・列の削除
    DB=> alter  table  テーブル名  drop  列名  [cascade];

    cascade : 参照整合性制約の被参照列に依存する全てを削除 
・not null 制約の追加
    DB=> alter  table  テーブル名  alter  列名  set  not  null;
・not null 制約の削除
    DB=> alter  table  テーブル名  alter  列名  drop  not  null;
・デフォルト値の追加
    DB=> alter  table  テーブル名  alter  列名  set  default デフォルト値;
・デフォルト値の削除
    DB=> alter  table  テーブル名  alter  列名  drop  default;
・制約の追加

制約名を省略した場合、自動的に設定される。

    DB=> alter  table  テーブル名  add  [constraint  制約名]  制約;
・制約の削除
    DB=> alter  table  テーブル名  drop  constraint  制約名  [cascade];
    
    cascade : 参照整合性制約の被参照列に依存する全てを削除 

テーブル、ビュー、シーケンスへの権限の付与/剥奪

デフォルトでは、作成者と管理ユーザー以外はアクセス出来無い。

・付与
    DB=> grant  権限,権限,...  on  テーブル名,...  to  ユーザー名,...  [with grant option];

    with grant option(許可されている権限を付与できる)
・剥奪
    DB=> revoke  権限,権限,...  on  テーブル名,...  from  ユーザー名,...;
all全権限
select検索
insert挿入
update更新
delete削除
truncate全行削除
references外部キー作成
triggerトリガー
・確認
    DB=> \z

    ユーザー名 = arwdDxt/付与したユーザー名
ainsert(append)
rselect(read)
wupdate(write)
ddelete
Dtruncate
xreferences
tcreate trigger
*直前の権限を付与できる