テーブルの作成

テーブルを作成するには、以下のような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」という名前のテーブルを削除するようにしています。

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

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

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

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

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

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