kakiro-webカキローウェブ

システム開発情報とコンピューター書籍の紹介サイト

PostgreSQL テーブルの作成

PostgreSQLでテーブルの作成に関するSQLについて、以下に示します。

※ここで説明に使用しているPostgreSQLは、当サイトのCentOS7 64bitのyumリポジトリにPostgreSQLを追加し、最新バージョンをインストールのページを元にインストールした、バージョン9.4になります。

※データベース接続ユーザーの作成については、当サイトのPostgreSQL データベース接続ユーザーの作成のページをご覧ください。

※データベースの作成については、当サイトのPostgreSQL データベースの作成のページをご覧ください。

ここでは、上記のページを元に作成したデータベース接続ユーザー「user01」で、データベース「db01」に接続し、テーブルの作成を行っています。

テーブルの作成

テーブルを作成するには、以下のようなSQLを実行します。

db01=> CREATE TABLE tbl01 (id CHAR(5) NOT NULL, num01 INTEGER, txt01 VARCHAR(50), date01 TIMESTAMP WITHOUT TIME ZONE, CONSTRAINT pk_tbl01 PRIMARY KEY (id));
  • 「CREATE TABLE」に続けて、作成するテーブルの名前を指定します。

    ここでは「tbl01」という名前のテーブルを作成するようにしています。

    テーブル名は「スキーマ名.テーブル名」のように指定して、テーブルを作成するスキーマを指定することができます。

    スキーマ名を省略した場合は、現在のスキーマに作成されます。

    スキーマの作成を行っていない場合は、デフォルトで存在するpublicスキーマにテーブルが作成されます。

    データベース接続ユーザーと同じ名前のスキーマが存在する場合は、データベースに接続時のスキーマは、ユーザー名のスキーマになります。

    データベース接続ユーザーと同じ名前のスキーマが存在しない場合は、データベースに接続時のスキーマは、publicスキーマになります。

  • テーブル名の指定の後に、「()」内に列の定義を指定します。

  • 「id」という名前の列を、固定長文字列型「CHAR」のサイズ「5」で、NOT NULL制約を付けて作成するようにしています。

    「CHAR」のサイズは、バイト数単位ではなく、文字数単位になります。

  • 「num01」という名前の列を、整数型「INTEGER」で、作成するようにしています。

    「INTEGER」で利用可能な値の範囲は、「-2147483648」から「2147483647」になります。

  • 「txt01」という名前の列を、可変長文字列型「VARCHAR」のサイズ「50」で、作成するようにしています。

    「VARCHAR」のサイズは、バイト数単位ではなく、文字数単位になります。

  • 「date01」という名前の列を、日付、時刻型「TIMESTAMP」で、作成するようにしています。

    「TIMESTAMP」で利用可能な値の範囲は、紀元前4713年から西暦294276年で、最小単位はマイクロ秒(0.000001秒)になります。

    「WITHOUT TIME ZONE」を指定するとタイムゾーンなし、「WITH TIME ZONE」を指定するとタイムゾーンありになります。

  • 「CONSTRAINT」に続けて、テーブルに設定する制約の名前を指定します。

    ここでは「pk_tbl01」という名前の主キー制約を作成するようにしています。

    「PRIMARY KEY」に続けて、「()」内に主キーに使用する列の名前を指定します。

    ここでは列「id」を主キーに指定しています。

尚、テーブルの所有者は、CREATE TABLE文の実行時にデータベースに接続しているユーザーになります。

現在のスキーマの確認

上記のテーブルの作成でも記載しましたが、テーブルはスキーマ内に作成されます。データベースの中にスキーマがあり、スキーマの中にテーブルがある構成となります。

現在のスキーマを確認するには、以下のようなSQLを実行します。

db01=> SELECT CURRENT_SCHEMA();
 current_schema
----------------
 public

自動採番列を持つテーブルの作成

自動採番列を持つテーブルを作成するには、以下のようなSQLを実行します。

db01=> CREATE TABLE tbl02 (num01 SERIAL NOT NULL, txt01 VARCHAR(50), date01 TIMESTAMP WITHOUT TIME ZONE, CONSTRAINT pk_tbl02 PRIMARY KEY (num01));
  • 「num01」という名前の列を、自動採番が行われる連番型「SERIAL」で、作成するようにしています。

    「SERIAL」で利用可能な値の範囲は、「1」から「2147483647」になります。

    この場合、「tbl02_num01_seq」という名前のシーケンスが自動的に作成され、このシーケンスのNEXTVALが列「num01」のデフォルト値として使用されるようになります。

自動採番列を持つテーブルへのINSERT文は、以下のように自動採番列を省略して行います。

db01=> INSERT INTO tbl02 (txt01, date01) VALUES ('aaa', '2016-01-02 03:04:05');

前回のINSERTで、シーケンス「tbl02_num01_seq」のNEXTVALにより採番された値を取得するには、以下のようなSQLを実行します。

db01=> SELECT CURRVAL('tbl02_num01_seq');
 currval
---------
       1

テーブル一覧の確認

テーブルの一覧を確認するには、以下のようなSQLを実行します。

db01=> SELECT * FROM pg_tables WHERE schemaname = 'public';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
 public     | tbl01     | user01     |            | t          | f        | f
 public     | tbl02     | user01     |            | t          | f        | f
  • 「pg_tables」は、システムカタログからテーブルの情報を取得するビューになります。

  • WHERE句の「schemaname」で、テーブル一覧を確認するスキーマの名前を指定します。

    ここではスキーマ「public」に作成されているテーブル一覧を確認するようにしています。

    上記のテーブル「tbl01」、「tbl02」を作成した際の結果になっています。

以下のコマンドを実行して、テーブル、インデックス、シーケンスの一覧を確認することもできます。

db01=> \dtis
                      リレーションの一覧
 スキーマ |      名前       |      型      | 所有者 | テーブル
----------+-----------------+--------------+--------+----------
 public   | pk_tbl01        | インデックス | user01 | tbl01
 public   | pk_tbl02        | インデックス | user01 | tbl02
 public   | tbl01           | テーブル     | user01 |
 public   | tbl02           | テーブル     | user01 |
 public   | tbl02_num01_seq | シーケンス   | user01 |
  • 「\d」コマンドに、「t」を付けるとテーブル、「i」を付けるとインデックス、「s」を付けるとシーケンスの一覧が表示されるようになります。

既存のテーブルのテーブル定義の確認

既存のテーブルの列の情報を確認するには、以下のようなSQLを実行します。

db01=> SELECT * FROM information_schema.columns WHERE table_name = 'tbl01';
 table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable |          data_type          | character_maximum_length | character_octet_length | numeric_precision | numeric_precision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collation_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name  | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable
---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------------------------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+-------------+------------+-----------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------
 db01          | public       | tbl01      | id          |                1 |                | NO          | character                   |                        5 |                     20 |                   |                         |               |                    |               |                    |                       |                      |                    |                   |                  |                |                |               |             | db01        | pg_catalog | bpchar    |               |              |            |                     | 1              | NO                  | NO          |                     |                |                    |                  |                  |                | NEVER        |                       | YES
 db01          | public       | tbl01      | num01       |                2 |                | YES         | integer                     |                          |                        |                32 |                       2 |             0 |                    |               |                    |                       |                      |                    |                   |                  |                |                |               |             | db01        | pg_catalog | int4      |               |              |            |                     | 2              | NO                  | NO          |                     |                |                    |                  |                  |                | NEVER        |                       | YES
 db01          | public       | tbl01      | txt01       |                3 |                | YES         | character varying           |                       50 |                    200 |                   |                         |               |                    |               |                    |                       |                      |                    |                   |                  |                |                |               |             | db01        | pg_catalog | varchar   |               |              |            |                     | 3              | NO                  | NO          |                     |                |                    |                  |                  |                | NEVER        |                       | YES
 db01          | public       | tbl01      | date01      |                4 |                | YES         | timestamp without time zone |                          |                        |                   |                         |               |                  6 |               |                    |                       |                      |                    |                   |                  |                |                |               |             | db01        | pg_catalog | timestamp |               |              |            |                     | 4              | NO                  | NO          |                     |                |                    |                  |                  |                | NEVER        |                       | YES
  • スキーマ「information_schema」にある「columns」は、テーブルの列の情報を取得するビューになります。

  • WHERE句の「table_name」で、列の情報を確認するテーブルの名前を指定します。

    ここでは上記で作成したテーブル「tbl01」の列の情報を確認するようにしています。

既存のテーブルに設定されている制約の情報を確認するには、以下のようなSQLを実行します。

db01=> SELECT * FROM information_schema.table_constraints WHERE table_name = 'tbl01';
 constraint_catalog | constraint_schema |    constraint_name    | table_catalog | table_schema | table_name | constraint_type | is_deferrable | initially_deferred
--------------------+-------------------+-----------------------+---------------+--------------+------------+-----------------+---------------+--------------------
 db01               | public            | pk_tbl01              | db01          | public       | tbl01      | PRIMARY KEY     | NO            | NO
 db01               | public            | 2200_16547_1_not_null | db01          | public       | tbl01      | CHECK           | NO            | NO
  • スキーマ「information_schema」にある「table_constraints」は、テーブルに設定されている制約の情報を取得するビューになります。

  • WHERE句の「table_name」で、テーブルに設定されている制約の情報を確認するテーブルの名前を指定します。

    ここでは上記で作成したテーブル「tbl01」に設定されている制約の情報を確認するようにしています。

既存のテーブルのキーが設定されている列の情報を確認するには、以下のようなSQLを実行します。

db01=> SELECT * FROM information_schema.key_column_usage WHERE table_name = 'tbl01';
 constraint_catalog | constraint_schema | constraint_name | table_catalog | table_schema | table_name | column_name | ordinal_position | position_in_unique_constraint
--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------
 db01               | public            | pk_tbl01        | db01          | public       | tbl01      | id          |                1 |
  • スキーマ「information_schema」にある「key_column_usage」は、キーが設定されている列の情報を取得するビューになります。

  • WHERE句の「table_name」で、キーが設定されている列の情報を確認するテーブルの名前を指定します。

    ここでは上記で作成したテーブル「tbl01」のキーが設定されている列の情報を確認するようにしています。

テーブルの定義は、以下のコマンドで確認することもできます。

db01=> \d tbl01
             テーブル "public.tbl01"
   列   |             型              |  修飾語
--------+-----------------------------+----------
 id     | character(5)                | not null
 num01  | integer                     |
 txt01  | character varying(50)       |
 date01 | timestamp without time zone |
インデックス:
    "pk_tbl01" PRIMARY KEY, btree (id)
  • 「\d」コマンドに、テーブルの定義を確認するテーブルの名前を指定します。

    ここでは上記で作成したテーブル「tbl01」の定義を確認するようにしています。

テーブルの削除

テーブルを削除するには、以下のようなSQLを実行します。

db01=> DROP TABLE tbl01;
  • 「DROP TABLE」に続けて、削除するテーブルの名前を指定します。

    ここでは「tbl01」という名前のテーブルを削除するようにしています。

PostgreSQL テーブルの作成については、以上です。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。