• トップページ
  • LAPP
  • PostgreSQL
  • テーブルの作成
  • テーブルの作成

    テーブルの作成は、

    テーブルの作成

    もくじ

    1. テーブル作成、制約、項目タイプ、キー作成、順序作成
    2. テーブルクリア、テーブル定義変更、整合性制約定義、権限

    テーブル作成、制約、項目タイプ、キー作成、順序作成

    日本語問題

    ・日本語 ODBC 経由のアクセスでは、項目名やデータとして日本語を使用する ことに何ら問題がない。これは、ECU_JP パラメタでコンフィグレーション をしたおかげと思う。 しかし、psql や ipgsql では、日本語がうまく扱えない。 テキストファイルからの実行等も行ったがダメである。 ECU コードで指定する必要があるのかと、日本語 EUC コードで、SQL のテ キストを作成し、「\i」で実行させたら、正常に日本語が処理できた。 但し、psql の select 表示は化ける。(^^; (Cygwin 2.05b と less290i.tgz で psql での select 日本語表示可能。)

    データベース作成

    ・PATH の優先順位が MySQL の方が高かったため、create database でエラー が発生した。PostgreSQL への PATH が先に来るようにする。

    ・データベースの作成は、次のようにする。

    create user sak with password 'sak';
    
    create database sak;
    

    テーブル作成

    ・「\i sqlfile」で、テキストファイルに記述した SQL を処理できる。 また、クリップボードコピーでの流し込みでも処理できる。

    create table testm (
      key            char(008)     primary key,
      data1          int8,
      data2          int8,
      data3          int8
    );
    

    ・テーブル作成時に NOT NULL 制約、UNIQUE 制約、DEFAULT 初期値設定を 指定できる。

    create table testm (
      key            char(008)     primary key,
      data1          int8 not null,
      data2          int8 unique,
      data3          int8 default 0 not null
    );
    

    ・constraint でプライマリキーを付けるには、次のようにする。 複合主キーは、これ以外の方法では付けられない。 (と思っていたら、付けられるらしい。バージョンによる?)

    create table testm (
      key            char(008),
      data1          int8,
      data2          int8,
      data3          int8,
      constraint KEY_NAME primary key (
        key,
        data1
      )
    );
    

    ・テーブル削除は、次のようにする。

    drop table testm;
    

    項目タイプ(フィールドタイプ)

    ・PostgreSQL 項目型

        smailint / int2                     2 バイト整数
        integer / int / int4                4 バイト整数
        bigint / int8                       8 バイト整数
        decimal(a, a) / numeric(a, s)       10 進型
        real / float4                       6 桁単精度浮動小数点
        double precision / float8           15 桁倍精度浮動小数点
        serial                              4 バイト順序
        bigserial                           8 バイト順序
        date                                日付
        time                                時間
        timestamp                           日付時間
        char(文字数) / character            固定長文字列  (最大 4096 文字)
        varchar(文字数) / charcter varying  可変長文字列  (最大 4096 文字)
        text                                無制限テキスト
        ラージオブジェクト                  oid 型 (いまいちわからん) (^^;
        boolean / bool                      true / false
    

    キー作成

    ・単項目のキー設定は、テーブル作成時の create 文で行うと良い。 項目定義に primary key を指定するだけである。

      key            char(008)     primary key,
    

    ・複合主キーについては、PostgreSQL では、テーブル作成後に別途 alter 文 で追加することはできない。 create tabel 時に constraint primary key で付加しておくこと。 (と思っていたら、付けられるらしい。バージョンによる?)

    create table testm (
      key            char(008),
      data1          int8,
      data2          int8,
      data3          int8,
      constraint KEY_NAME primary key (
        key,
        data1
      )
    );
    

    ・主キー以外にいくつでも副キー索引を作成できる。 create index で作成するが、ユニークでない重複キーも作れる。 頻繁に使われるクエリのキーは作成しておくと、レスポンスが著しく違う。 但し、あまりキー(索引) を作りすぎてもいけない。 更新時に索引の更新負荷が増大することになる。 従って、問い合わせクエリを意識した最小限で、かつ、高速アクセスとなる 索引を設計する。 また、where 句や order by 句にキーを含めて指定するようにする。 データベースの更新や問い合わせに異様に時間がかかるのは、キーの指定が されていないからである。

    ・create tabble 時の primary key は、システムが自動的に索引を作成する ので、手作業で別途作成する必要はない。 それ以外の副キーは、次のように作成する。 unique 指定がなければ重複可となる。

    create unique index KEY_NAME on testm (
      key,
      data1
    );
    
    create index KEY_NAME on testm (
      key,
      data1
    );
    

    ・索引の削除は、次のようにする。

    drop index KEY_NAME;
    

    順序作成

    ・順序は、serial と定義することで利用できる。

    create table testm (
      key            char(008),
      data1          serial,
      data2          int8,
      data3          int8
    );
    
    insert into testm values('a001');
    insert into testm values('b002');
    
       key    | data1 | data2 | data3
    ----------+-------+-------+-------
     a001     |     1 |       |
     b002     |     2 |       |
    

    ・Oracle と同じ順序のように create sequence がある。 単純な順序は、次のように作成する。

    create sequence seq;
    

    ・次のようにすると、10000 〜 9999900、増分 100 でくるくる回ります。 cache の省略値は不明。

    create sequence seq
      increment    100
      minvalue     10000
      maxvalue     9999900
      start        10000
      cache        200
      cycle
    ;
    

    ・順序の削除は、次のようにする。

    drop sequence seq;
    

    ・実際に順序を利用するには、次のようにする。 nextval がシーケンス番号を生み出す。 尚、順序が rollback されず、飛ぶ可能性があるかは未検証。 currval は、前回と同じ順序を呼び出す。順序の現在値ではなく、 セッションユーザが最後に使用した値になるかは未検証。

    insert into testm values ('s001', nextval('seq'), 20, 30);
    insert into testm values ('s002', nextval('seq'), 20, 30);
    insert into testm values ('s003', currval('seq'), 20, 30);
    
       key    | data1 | data2 | data3
    ----------+-------+-------+-------
     s001     | 10000 |    20 |    30
     s002     | 10100 |    20 |    30
     s003     | 10100 |    20 |    30
    
    

    テーブルクリア、テーブル定義変更、整合性制約定義、権限

    テーブルクリア

    ・テーブル中のデータだけを全て削除するには、delete 文を使用する。

    delete from testm;
    

    ・但し、単純クリアが目的なら、この方法はレスポンス的に問題がある。 トランザクション機能が働くので、全レコードのトランザクションバッファ への書き込みとコミットによる更新が行われる。 何万件にも及ぶデータのクリアを delete 文でやると実用に耐えない。

    ・単純なテーブルクリアが目的の場合は、表切り捨て(truncate) を使用する と良い。この方が一瞬でクリアできる。

    truncate table testm;
    

    ・思いきって、テーブル削除と再作成を利用する手もある。

    drop table testm;
    create table testm (
      key            char(008),
      data1          int8,
      data2          int8,
      data3          int8
    );
    

    テーブル定義変更(テーブル属性変更、列定義変更)

    ・データ項目の桁数が足りなくなったり、新たな項目を追加するのは alter 文で簡単に行える。但し、項目の削除はできない。 項目の削除を行うには、 データ退避 テーブル削除 テーブル再作成 新形式にデータ編集 データセットアップ を行う必要がある。

    ・新たにテーブルに項目を追加(列の追加、フィールド追加、カラム追加) するには、次のようにする。 Oracle のように複数項目を一度に追加することはできない。

    alter table testm add
      newdata1  int8
    ;
    

    ・PostgreSQL では、列名の変更ができる。

    alter table testm rename
      data1 to abc
    ;
    

    ・PostgreSQL では、列の項目タイプ定義変更はできないようである。 項目タイプを変更するためには、テーブルを作り直すしかない。

    整合性制約定義、リレーション、リファレンス、外部キー

    ・データ項目に他のテーブルとのリレーションをとって、制約条件などを設定 することができる。(constraints) 制約追加は、create table 時、または、alter table で行う。

    ・列制約には、

    primary key  主キー制約(unique & not null)
    unique       一意キー制約(ユニーク項目、一意制約、ユニーク値、ユニークキー、ユニーク制約)
    not null     NOT ヌル制約
    check        チェック制約
    references   参照整合性制約(参照制約、外部整合性制約、外部制約キー)
    
    

    がある。

    PostgreSQL では、primary key、unique、not null 制約については、 create table 時にしか定義できない。 alter table で指定可能なのは、check、references 制約だけである。

    alter table testm
      add constraint TESTM_JCSU check(data1 >= 0)
    ;
    
    alter table testm
      add constraint TESTM_FKEY
      foreign key (key) references test2m (key)
    ;
    

    ・PostgreSQL では、制約単体の削除はできないようである。

    名前変更(テーブル名変更)

    ・テーブル名の変更は、次のようにする。

    alter table testm rename to abc;
    

    ユーザ作成

    ・ユーザの作成は、次のようにする。

    create user sak with password 'sak';
    

    ・ユーザの削除は、次のようにする。

    drop user sak;
    

    システム権限付与

    ・PosegreSQL については、ロールが使用可能か不明である。 権限の付与は、次のようにする。

    grant 権限名, ...
      on オブジェクト, ...
      to public;
         |_ public
            group グループ名
            ユーザ名
    

    ・権限の取り消しは、次のようにする。

    revoke 権限名, ...
      on オブジェクト名, ...
      from public;
           |_ public
              group グループ名
              ユーザ名
    

    以上