PostgreSQL（テーブル作成/削除）

テーブル作成/削除

    DB=> create  table  テーブル名 (
                    列名  データ型  [制約, ...],
                    列名  データ型  [制約, ...],
                            ・
                            ・
                );

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

    [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
                );

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

    create  table  テーブル名 (
        列名  データ型  not  null  default  デフォルト値,
    );

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

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

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

    連結主キー
    create  table  テーブル名 (
        列名  データ型,
        列名  データ型,
        primary  key  (列名, 列名)
    );

列の値が条件を満たすように指定。 検査式が真または 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 : 参照整合性制約の被参照列に依存する全てを削除 

    DB=> alter  table  テーブル名  alter  列名  set  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  ユーザー名,...;

    DB=> \z

    ユーザー名 = arwdDxt/付与したユーザー名