PostgreSQLとは何ですか?

PostgreSQLは、SQLクエリ言語を使用するデータベース管理システムです。 これは非常に安定した機能豊富なデータベースシステムであり、他のアプリケーションからのデータをVPSに保存するために使用できます。

この記事では、postgreSQLインターフェース内でテーブルを作成および管理する方法について説明します。 テーブルを適切に構成し、それらを使用して情報を保存する方法を学習します。

UbuntuにPostgreSQLをインストールしてログインする方法

このガイドでは、PostgreSQLをUbuntu 12.04にインストールしますが、他のほとんどのディストリビューションのデフォルトリポジトリで利用できるはずです。

次のコマンドを入力してインストールします。

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

インストール後、作成するデータベースを管理するための新しいユーザーを作成します。

sudo adduser postgres_user

デフォルトのPostgreSQLユーザー(「postgres」と呼ばれる)にログインしてデータベースを作成し、それを新しいユーザーに割り当てます。

sudo su - postgres
psql

PostgreSQLコマンドプロンプトが表示されます。

作成したシステムユーザーと一致する新しいユーザーを作成します。 次に、そのユーザーが管理するデータベースを作成します。

CREATE USER postgres_user WITH PASSWORD 'password';
CREATE DATABASE my_postgres_db OWNER postgres_user;

次のコマンドを使用して、インターフェイスを終了します。

\q

デフォルトの「postgres」ユーザーアカウントを終了し、次のコマンドで作成したユーザーにログインします。

exit
sudo su - postgres_user

次のコマンドを使用して、作成したデータベースにサインインします。

psql my_postgres_db

これで、テーブル管理について学習する準備が整いました。

PostgreSQLでのテーブル作成構文

私たちのデータベースにはまだテーブルがありません。 これを確認するには、PostgreSQLに次のコマンドで使用可能なテーブルのリストを提供するように依頼します。

\d
No relations found.

次の構文に従うことで、新しいテーブルを作成できます。

CREATE TABLE new_table_name (
	table_column_title TYPE_OF_DATA column_constraints,
	next_column_title TYPE_OF_DATA column_constraints,
	table_constraint
	table_constraint
) INHERITS existing_table_to_inherit_from;

閉じ括弧の後からセミコロンまでの部分は、前の定義にリストされている列に加えて、既存のテーブルからすべての列を継承するオプションの構造です。

括弧内の部分は、列定義とテーブル制約の2つの部分に分かれています。

PostgreSQLの列とテーブルの定義

列定義は次の構文パターンに従います。

column_name data_type (optional_data_length_restriction) column_constraints

列名はわかりやすいものにする必要があります。

PostgreSQLのデータ型

データ型は次のいずれかになります。

  • boolean :「boolean」または「bool」を使用してtrueまたはfalseの値を宣言します。
  • 文字値
    • char :単一の文字を保持します
    • char(#):#文字数を保持します。 余分な部屋を埋めるためにスペースが挿入されます。
    • varchar(#):最大#文字数を保持します。 より少なく含めることができます。
  • 整数値
    • smallint :-32768から32767までの整数。
    • int :-214783648から214783647までの整数。
    • serial :自動入力された整数。
  • 浮動小数点値
    • float(#):少なくとも#ポイントの精度を持つ浮動小数点数。
    • real :8バイトの浮動小数点数
    • 数値(#、after_dec):#桁数の実数、および10進数の後のafter_dec桁
  • 日付と時刻の値
    • date :日付値を格納します
    • time :時間値を格納します
    • タイムスタンプ:日付と時刻の値を格納します
    • timestamptz :タイムゾーンデータを含むタイムスタンプを格納します
    • よい間隔:2つのタイムスタンプ値の差を格納します
  • 幾何学的データ
    • point :ポイントを定義する座標のペアを格納します
    • line :線を描く点のセットを格納します
    • lseg :線分を定義するデータを格納します
    • box :長方形を定義するデータを格納します
    • ポリゴン:閉鎖空間を定義するデータを格納します
  • デバイス仕様
    • inet :IPアドレスを保存します
    • macaddr :デバイスのMACアドレスを保存します

PostreSQLの列とテーブルの制約

列定義には、列で見つかったデータのタイプのルールを提供する制約を含めることもできます。 以下は、データ型に続くスペース区切りの値として使用できます。

  • NOT NULL :列にnull値を含めることはできません
  • UNIQUE :列の値はどのレコードでも同じであってはなりません。 Nullは常に一意の値と見なされます
  • PRIMARY KEY :上記の2つの制約の組み合わせ。 テーブルごとに1回のみ使用できます
  • CHECK :列の値の条件が真であることを確認します
  • REFERENCES :値は別のテーブルの列に存在する必要があります

列が定義された後、テーブル全体の制約を宣言できます。 テーブル全体の制約は、UNIQUE、PRIMARY KEY、CHECK、またはREFERENCESのいずれかになります。

PostgreSQLでテーブルを作成する方法

練習用のテストテーブルを作成しましょう。 さまざまな遊具を定義する「pg_equipment」というテーブルを作成します。 次のテーブル定義を入力します。

CREATE TABLE pg_equipment (
	equip_id serial PRIMARY KEY,
	type varchar (50) NOT NULL,
	color varchar (25) NOT NULL,
	location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
	install_date date
	);
NOTICE:  CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment"
CREATE TABLE

プロンプトに「\d」と入力すると、新しいテーブルが表示されます。

\d
                       List of relations
 Schema |           Name            |   Type   |     Owner     
--------+---------------------------+----------+---------------
 public | pg_equipment              | table    | postgres_user
 public | pg_equipment_equip_id_seq | sequence | postgres_user
(2 rows)

テーブルと、「equip_id」シリアルデータ型宣言によって作成されたシーケンスが一覧表示されます。

PostgreSQLでテーブルデータを変更する方法

次の一般的な構文を使用して、テーブルの定義を変更できます。

ALTER TABLE table_name Action_TO_Take;

たとえば、次のコマンドを入力して、「pg_equipment」テーブルに列を追加できます。

ALTER TABLE pg_equipment ADD COLUMN functioning bool;
ALTER TABLE

次のように入力すると、追加の列が表示されます。

\d pg_equipment
    Column    |         Type          |                            Modifiers                            
--------------+-----------------------+-----------------------------------------------------------------
 equip_id     | integer               | not null default nextval('pg_equipment_equip_id_seq'::regclass)
 type         | character varying(50) | not null
 color        | character varying(25) | not null
 location     | character varying(25) | 
 install_date | date                  | 
 functioning  | boolean               |
 . . .

「特に明記されていない限り、機器は機能していると見なす必要がある」ことを指定するデフォルト値を追加するには、次のコマンドを実行します。

ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';

値もnullでないことを確認したい場合は、次のようにします。

ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;

列の名前を変更するには、次の構文を使用します。

ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;

作成した列を削除するには、次のコマンドを入力します。

ALTER TABLE pg_equipment DROP COLUMN working_order;

次のコマンドを使用して、テーブル全体の名前を変更できます。

ALTER TABLE pg_equipment RENAME TO playground_equip;

PostgreSQLでのテーブルの削除

次のように入力して、作成したテーブルを削除できます。

DROP TABLE playground_equip;
DROP TABLE

存在しないテーブルにそのコマンドを与えると、次のエラーが発生します。

ERROR: table "playground_equip" does not exist

このエラーを回避するために、postgreSQLにテーブルが存在する場合はそれを削除し、どちらの方法でも正常に戻るように指示できます。 これを行うには、次のコマンドを発行します。

DROP TABLE IF EXISTS playground_equip;
NOTICE:  table "playground_equip" does not exist, skipping
DROP TABLE

今回は、テーブルが見つからなかったことを示していますが、エラーをスローする代わりに続行します。

結論

これで、PostgreSQLで単純なテーブルを作成および管理するための十分な知識が得られます。 これらのスキルは、別のアプリケーションからのデータを管理している場合、またはコマンドラインからPostgreSQLを制御する方法を学習している場合に役立ちます。

ジャスティン・エリングウッド