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という名前のデータベースが、デフォルトのデータベース設定を使用して現在のユーザーが新しいデータベースの所有者として設定されて作成されます。デフォルトのtemplate1テンプレートのプロパティは、次のpsqlメタコマンドを使用して表示できます。

\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: 新しいデータベースの文字分類を設定します。これは、どの文字が大文字、小文字、数字と見なされるかに影響するローカライゼーションオプションです。

これらは、データベースがサポートする予定の形式でデータを格納し、プロジェクトのローカライゼーション設定に合わせるのに役立ちます。

たとえば、データベースが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ドキュメントを確認してください。

よくある質問

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;