PostgreSQL入門#1: データベースとテーブル作成

PostgreSQLとは

PostgreSQLは、オープンソースのリレーショナルデータベース管理システム（RDBMS）です。一般的に「Postgres」とも呼ばれます。特徴を整理すると次の通りです。

特徴

  • オープンソース
    無料で利用でき、ソースコードも公開されています。商用利用も自由に可能です。
  • 高い標準準拠性
    SQL標準に準拠しており、ANSI SQL:2008などの規格に対応しています。
  • 拡張性
    独自のデータ型、関数、演算子などを追加できます。プラグインを利用して機能を拡張することも容易です。
  • 安定性・信頼性
    ACID特性（Atomicity, Consistency, Isolation, Durability）を満たし、トランザクション処理の信頼性が高いです。
  • 豊富な機能
    JSONサポート、全文検索、地理情報システム（PostGIS）、レプリケーションなど幅広い機能を備えています。
  • クロスプラットフォーム対応
    Linux、Windows、macOSなど様々なOSで動作します。

利用シーン

  • Webアプリケーションのバックエンドデータベース
  • 大規模なデータ分析基盤
  • 地理情報システム（GIS）
  • 企業システムの基幹データベース

まとめ

PostgreSQLは、堅牢性と拡張性に優れたRDBMSであり、小規模なアプリから大規模なエンタープライズシステムまで幅広く利用されています。オープンソースでありながら商用データベースに匹敵する機能を持つため、多くの開発者や企業から支持されています。

データベースの新規作成

psql から SQL を実行して作成

PostgreSQLの対話ツール psql に接続して、SQL文を使います。

CREATE DATABASE mydb;

ここで mydb が新しく作るデータベース名です。
デフォルトでは現在ログインしているユーザーがそのデータベースの所有者になります。

createdb コマンドを使う方法

LinuxやmacOSのターミナルから直接実行できます。

createdb mydb

このコマンドは内部的に CREATE DATABASE SQLを呼び出しています。
-O オプションで所有者を指定することも可能です。

createdb -O username mydb

データベース作成時のオプション

CREATE DATABASE文では、エンコードやロケールなどを指定できます。

CREATE DATABASE mydb
  WITH OWNER = myuser
       ENCODING = 'UTF8'
       LC_COLLATE = 'ja_JP.UTF-8'
       LC_CTYPE = 'ja_JP.UTF-8'
       TEMPLATE = template0;
  • OWNER: データベースの所有者
  • ENCODING: 文字コード（UTF8が一般的）
  • LC_COLLATE / LC_CTYPE: ソート順や文字分類に使うロケール
  • TEMPLATE: データベース作成時にコピー元とするテンプレートDB（通常は template1）

4. 作成後の接続

作成したデータベースに接続するには:

psql mydb

または psql内で

\c mydb

を実行します。

テーブルの作成方法

テーブル作成の基本構文

CREATE TABLE テーブル名 (
    カラム名 データ型 制約,
    カラム名 データ型 制約,
    ...
);

例: ユーザー情報を管理するテーブル

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
  • SERIAL: 自動採番（整数のシーケンス）
  • PRIMARY KEY: 主キー制約
  • UNIQUE: 重複不可
  • NOT NULL: NULL禁止
  • DEFAULT: デフォルト値

主なデータ型の種類

数値型

  • SMALLINT (2バイト、-32768 ～ 32767)
  • INTEGER / INT (4バイト、約±21億)
  • BIGINT (8バイト、非常に大きな整数)
  • DECIMAL / NUMERIC (任意精度の小数、金融計算向き)
  • REAL (4バイト浮動小数点)
  • DOUBLE PRECISION (8バイト浮動小数点)
  • SERIAL / BIGSERIAL (自動採番付き整数)

文字列型

  • CHAR(n) 固定長文字列
  • VARCHAR(n) 可変長文字列（最大長を指定）
  • TEXT 制限なしの文字列

日時型

  • DATE 日付（YYYY-MM-DD）
  • TIME 時刻（HH:MM:SS）
  • TIMESTAMP 日付と時刻
  • TIMESTAMPTZ タイムゾーン付きタイムスタンプ
  • INTERVAL 期間（例: INTERVAL '2 days'）

論理型

  • BOOLEAN （TRUE, FALSE, NULL）

その他

  • UUID 一意識別子
  • JSON / JSONB JSONデータ型（JSONBはバイナリ形式で検索性能が高い）
  • ARRAY 配列（例: INTEGER[]）
  • BYTEA バイナリデータ（画像やファイル格納用）
  • ENUM 列挙型（独自定義の固定値セット）

制約の例

  • PRIMARY KEY: 主キー
  • FOREIGN KEY: 外部キー
  • CHECK: 値の条件制約
  • DEFAULT: デフォルト値
  • UNIQUE: 一意制約
  • NOT NULL: NULL禁止

サンプル: 商品テーブル

CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price NUMERIC(10,2) CHECK (price >= 0),
    stock INTEGER DEFAULT 0,
    category TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

この例では、価格はマイナスにならないよう CHECK 制約を付けています。

テーブルの削除と変更

テーブルの削除

基本構文

DROP TABLE テーブル名;

存在する場合のみ削除

存在しないテーブルを指定するとエラーになります。エラーを避けるには:

DROP TABLE IF EXISTS テーブル名;

依存オブジェクトも含めて削除

外部キー制約やビューなど他のオブジェクトが依存している場合、通常は削除できません。依存関係ごと削除する場合は:

DROP TABLE テーブル名 CASCADE;

テーブルの変更 (ALTER TABLE)

カラムの追加

ALTER TABLE テーブル名
ADD COLUMN カラム名 データ型 [制約];

例:

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

カラムの削除

ALTER TABLE テーブル名
DROP COLUMN カラム名;

存在確認付き:

ALTER TABLE テーブル名
DROP COLUMN IF EXISTS カラム名;

カラム名の変更

ALTER TABLE テーブル名
RENAME COLUMN 古いカラム名 TO 新しいカラム名;

カラムのデータ型を変更

ALTER TABLE テーブル名
ALTER COLUMN カラム名 TYPE 新しいデータ型;

デフォルト値の追加・変更

ALTER TABLE テーブル名
ALTER COLUMN カラム名 SET DEFAULT デフォルト値;

デフォルト値の削除:

ALTER TABLE テーブル名
ALTER COLUMN カラム名 DROP DEFAULT;

NULL制約の追加・削除

-- NOT NULL制約を追加
ALTER TABLE テーブル名
ALTER COLUMN カラム名 SET NOT NULL;

-- NOT NULL制約を削除
ALTER TABLE テーブル名
ALTER COLUMN カラム名 DROP NOT NULL;

テーブル名の変更

ALTER TABLE 古いテーブル名 RENAME TO 新しいテーブル名;

サンプル

ユーザーテーブルに「住所」カラムを追加し、その後削除する例:

ALTER TABLE users ADD COLUMN address TEXT;
ALTER TABLE users DROP COLUMN address;

テーブルごと削除する場合:

DROP TABLE users;

つまり、

  • DROP TABLE → テーブル自体を削除
  • ALTER TABLE → カラムや制約の追加・削除・変更

を使い分ける形です。

テーブルにデータを追加する方法

PostgreSQLでテーブルにデータを追加するには、基本的に INSERT文 を使います。用途によっていくつかの書き方があります。

単一行を追加

INSERT INTO テーブル名 (カラム1, カラム2, ...)
VALUES (値1, 値2, ...);

例: users テーブルに新しいユーザーを追加

INSERT INTO users (name, email, age)
VALUES ('Taro Yamada', 'taro@example.com', 30);

全カラムにデータを追加

カラム名を省略すると、テーブル定義のカラム順に値を指定する必要があります。

INSERT INTO users
VALUES (DEFAULT, 'Hanako Sato', 'hanako@example.com', 25, DEFAULT);

DEFAULT を指定すると、デフォルト値やシーケンスが自動的に使われます。

複数行を一度に追加

INSERT INTO users (name, email, age)
VALUES 
  ('Ichiro Suzuki', 'ichiro@example.com', 40),
  ('Ken Tanaka', 'ken@example.com', 22),
  ('Mika Arai', 'mika@example.com', 28);

SELECT文を使って追加

他のテーブルやクエリ結果からデータを取り込む場合に使います。

INSERT INTO users_archive (name, email, age)
SELECT name, email, age
FROM users
WHERE age > 30;

衝突（重複キー）時の制御

主キーやユニーク制約に違反しそうな場合に使う方法です。

既存行がある場合は何もしない

INSERT INTO users (id, name, email)
VALUES (1, 'Taro Yamada', 'taro@example.com')
ON CONFLICT DO NOTHING;

既存行がある場合は更新する（UPSERT）

INSERT INTO users (id, name, email)
VALUES (1, 'Taro Yamada', 'taro_new@example.com')
ON CONFLICT (id) 
DO UPDATE SET email = EXCLUDED.email;

EXCLUDED.email は、INSERTで指定された値を参照しています。

まとめ

  • INSERT INTO ... VALUES → 単一または複数行を追加
  • INSERT INTO ... SELECT → 別テーブルから取り込み
  • ON CONFLICT → 重複時の処理を制御

これで基本的なデータ追加パターンを押さえられます。

-PostgreSQL, SQL