PostgreSQL でデータベースとテーブルを作成および削除する方法

はじめに

PostgreSQL およびその他のリレーショナルデータベース管理システムは、データを構造化して整理するためにデータベースとテーブルを使用します。これらの 2 つの用語の定義を簡単に確認できます。

• データベース: 異なる構造とデータのセットを互いに分離します。
• テーブル: データ構造を定義し、データベース内の実際のデータ値を格納します。

PostgreSQL には、データベースとテーブルの中間オブジェクトとしてスキーマもあります。

• スキーマ: テーブル、インデックス、ビュー、およびその他の項目を含むデータベース内の名前空間。

このガイドでは、PostgreSQL のスキーマの概念を直接扱うことはありませんが、それが存在することを知っておくことは良いことです。

代わりに、PostgreSQL データベースとテーブルを作成および破棄する方法に焦点を当てます。例では主に SQL を使用しますが、最後の方で、コマンドラインを使用してこれらのタスクのいくつかを行う方法を示します。これらの代替手段は、PostgreSQL ホストへの管理者アクセス権を持っている場合に利用できる、標準の PostgreSQL インストールに含まれているツールを使用します。

このガイドで説明されているステートメントの一部、特に PostgreSQL の CREATE TABLE ステートメントには、この記事の範囲外の多くの追加オプションがあります。詳細については、PostgreSQL の公式ドキュメントをご覧ください。

前提条件

このガイドに従うには、psql コマンドラインクライアントを使用して、管理者権限を持つユーザーで PostgreSQL インスタンスにログインする必要があります。PostgreSQL インスタンスは、ローカル、リモート、またはプロバイダーによってプロビジョニングされたものにインストールできます。

具体的には、PostgreSQL ユーザーには CREATE DB 権限または Superuser である必要があります。これは、psql の \du メタコマンドで確認できます。

\du

                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

インストール時に自動的に作成される postgres スーパーユーザーには必要な権限がありますが、Create DB 権限を持つ任意のユーザーを使用できます。

新しいデータベースを作成する

psql またはその他の SQL クライアントを使用して PostgreSQL インスタンスに接続したら、SQL を使用してデータベースを作成できます。

データベースを作成するための基本的な構文は次のとおりです。

CREATE DATABASE db_name;

これにより、db_name という名前のデータベースが現在のサーバーに作成され、現在のユーザーが新しいデータベースの所有者として設定され、デフォルトのデータベース設定が使用されます。次の psql メタコマンドを使用して、デフォルトの template1 テンプレートのプロパティを表示できます。

\l template1

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(1 row)

追加のパラメータを追加して、データベースの作成方法を変更できます。一般的なオプションを以下に示します。

• ENCODING: データベースの文字エンコーディングを設定します。
• LC_COLLATE: データベースの照合順序、つまりソート順序を設定します。これは、項目が順序付けられるときにどのように整理されるかを決定するローカライズオプションです。
• LC_CTYPE: 新しいデータベースの文字分類を設定します。これは、どの文字がUpperCase、LowerCase、および数字と見なされるかに影響を与えるローカライズオプションです。

これらは、データベースがサポートする予定の形式で、プロジェクトのローカライズ設定でデータを確実に保存できるようにするのに役立ちます。

たとえば、データベースが Unicode サポートで作成され、サーバー独自のロケールをオーバーライドしてアメリカ英語のローカライズを使用するようにするには（これらはすべて、上記の template1 の値と一致するため、実際には変更は発生しません）、次のように入力できます。

CREATE DATABASE db_name
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

このガイドの例に従うには、インスタンスのデフォルトのロケール設定と UTF8 文字エンコーディングを使用して、school という名前のデータベースを作成します。

CREATE DATABASE school ENCODING 'UTF8';

これにより、指定した仕様を使用して新しいデータベースが作成されます。

既存のデータベースを一覧表示する

サーバーまたはクラスターで現在利用可能なデータベースを確認するには、次の SQL ステートメントを使用できます。

SELECT datname FROM pg_database;

これにより、環境内で現在定義されている各データベースが一覧表示されます。

  datname
-----------
 _dodb
 template1
 template0
 defaultdb
 school
(5 rows)

前述のように、psql クライアントを使用して接続している場合は、\l メタコマンドを使用してこの情報を取得することもできます。

\l

これにより、利用可能なデータベース名が、所有者、エンコーディング、ロケール設定、および権限とともに表示されます。

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

作成した school データベースが、システム上の他のデータベースの中に表示されます。これは、サーバーまたはクラスター内のデータベースの概要を把握するのに適した方法です。

データベース内にテーブルを作成する

1 つ以上のデータベースを作成したら、データを格納するためのテーブルの定義を開始できます。テーブルは、名前と定義されたスキーマで構成されており、各レコードに含める必要のあるフィールドとデータ型を決定します。

CREATE TABLE table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

\c school

CREATE TABLE supplies (
  id INT PRIMARY KEY,
  name VARCHAR,
  description VARCHAR,
  manufacturer VARCHAR,
  color VARCHAR,
  inventory int CHECK (inventory > 0)
);

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
(1 row)

\d supplies

                      Table "public.supplies"
    Column    |       Type        | Collation | Nullable | Default
--------------+-------------------+-----------+----------+---------
 id           | integer           |           | not null |
 name         | character varying |           |          |
 description  | character varying |           |          |
 manufacturer | character varying |           |          |
 color        | character varying |           |          |
 inventory    | integer           |           |          |
Indexes:
    "supplies_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "supplies_inventory_check" CHECK (inventory > 0)

CREATE TABLE teachers (
  id INT PRIMARY KEY,
  first_name VARCHAR,
  last_name VARCHAR,
  subject VARCHAR,
  grade_level int
);

PostgreSQL でテーブルを表示する方法

PostgreSQL では、探している情報に応じて、いくつかの異なる方法でテーブルを一覧表示できます。

データベース内で利用可能なテーブルを確認する場合は、psql クライアントに含まれている \dt メタコマンドを使用して、上記で説明したようにすべてのテーブルを一覧表示できます。

\dt

          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | supplies | table | doadmin
 public | teachers | table | doadmin
(2 rows)

テーブルのスキーマが仕様と一致していることを確認することもできます。

\d teachers

                     Table "public.teachers"
   Column    |       Type        | Collation | Nullable | Default
-------------+-------------------+-----------+----------+---------
 id          | integer           |           | not null |
 first_name  | character varying |           |          |
 last_name   | character varying |           |          |
 subject     | character varying |           |          |
 grade_level | integer           |           |          |
Indexes:
    "teachers_pkey" PRIMARY KEY, btree (id)

teachers テーブルは、定義と一致しているようです。

テーブルを変更する

PostgreSQL で既存のテーブルのスキーマを変更する必要がある場合は、ALTER TABLE コマンドを使用できます。ALTER TABLE コマンドは CREATE TABLE コマンドと非常によく似ていますが、既存のテーブルで動作します。

ALTER TABLE <table_name> <change_command> <change_parameters>

ALTER TABLE some_table ADD COLUMN missing_column text;

ALTER TABLE some_table DROP COLUMN useless_column;

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;

テーブルを削除する

テーブルを削除する場合は、DROP TABLE SQL ステートメントを使用できます。これにより、テーブルとテーブル内に格納されているすべてのデータが削除されます。

基本的な構文は次のようになります。

DROP TABLE table_name;

これにより、テーブルが存在する場合は削除され、テーブル名が存在しない場合はエラーがスローされます。

テーブルが存在する場合は削除し、存在しない場合は何も実行しない場合は、ステートメント内に IF EXISTS 修飾子を含めることができます。

DROP TABLE IF EXISTS table_name;

他のテーブルまたはオブジェクトに依存関係があるテーブルは、それらの依存関係が存在する間はデフォルトで削除できません。エラーを回避するには、オプションで CASCADE パラメータを含めることができます。これにより、テーブルとともにすべての依存関係が自動的に削除されます。

DROP TABLE table_name CASCADE;

削除するテーブルを参照する外部キー制約を持つテーブルがある場合、その制約は自動的に削除されます。

先ほど作成した supplies テーブルを削除するには、次のように入力します。

DROP TABLE supplies;

teachers データベースは、データベースを削除するステートメントがテーブルのようなすべての子オブジェクトも削除することを実証するために保持します。

データベースを削除する

DROP DATABASE ステートメントは、指定されたデータベースを削除するように PostgreSQL に指示します。基本的な構文は次のようになります。

DROP DATABASE database_name;

database_name プレースホルダーを、削除するデータベースの名前に置き換えます。これにより、データベースが見つかった場合は削除されます。データベースが見つからない場合は、エラーが発生します。

DROP DATABASE some_database;

ERROR:  database "some_database" does not exist

データベースが存在する場合に削除し、存在しない場合は何もしないようにするには、オプションの IF EXISTS オプションを含めてください。

DROP DATABASE IF EXISTS some_database;

NOTICE:  database "some_database" does not exist, skipping
DROP DATABASE

これはデータベースを削除するか、見つからない場合は何もしません。

このガイドで使用した school データベースを削除するには、システム上の既存のデータベースを一覧表示します。

\l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 _dodb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 defaultdb | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 school    | doadmin  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

削除したくないデータベースのいずれかに新しい接続を開きます。

\c defaultdb

新しい接続が開いたら、次のコマンドで school データベースを削除します。

DROP DATABASE school;

これにより、school データベースが、その中に定義されている teachers テーブルとともに削除されます。

SQL を使用してここまで進めてきた場合は、ここで終了するか、結論 にスキップできます。コマンドラインからデータベースを作成および削除する方法について学習したい場合は、次のセクションに進んでください。

管理コマンドラインツールを使用してデータベースを作成および削除する

PostgreSQL がインストールされているサーバーまたはクラスターへのシェルアクセスがある場合、データベースの作成と削除に役立つ追加のコマンドラインツールにアクセスできる場合があります。createdb および dropdb コマンドは、PostgreSQL のインストール時にバンドルされています。

createdb db_name

createdb --encoding=UTF8 --locale=en_US db_name

createdb --encoding=UTF8 school

dropdb database_name

dropdb --if-exists database_name

dropdb school

結論

この記事では、PostgreSQL 内でデータベースとテーブルを作成および削除する方法の基本について説明しました。これらは、データベースシステムをセットアップし、データの構造を定義するために必要な最も基本的なコマンドの一部です。

前述のように、この PostgreSQL チュートリアルで説明した SQL ステートメント、特に CREATE TABLE ステートメントには、PostgreSQL の動作を変更するために使用できる追加のパラメーターが多数あります。これらの詳細については、PostgreSQL 公式ドキュメント を参照してください。

FAQ

CREATE TABLE IF NOT EXISTS table_name (
    column_name TYPE [column_constraint],
    [table_constraint,]
);

pg_restore [connection-option...][option...][filename]

createdb db_name

DROP DATABASE database_name;

ALTER TABLE resident ALTER COLUMN id SET DATA TYPE int USING id::int;