PostgreSQL（ポストグレスキューエル）は、高機能なオープンソースのリレーショナルデータベースです。
しかし、コマンドラインでの操作が中心となるため、初心者には少しむずかしく感じるかもしれません。
そこで本記事では、PostgreSQLでよくつかわれる基本コマンドを「ログイン・データベース操作・テーブル操作・ユーザー管理」など目的別に整理し、わかりやすくしょうかいします。
PostgreSQLってなに？
データベースの基本
データベースとは
- 大量のデータを効率的に保存・管理するしくみ
- 複数の人が同時につかえる
- データの整合性を保ってくれる
PostgreSQLの特徴
- 完全に無料で 使える（オープンソース）
- 高性能で信頼性が高い
- 大企業でも使われている
- SQL標準にしたがっている
他のデータベースとのちがい
- MySQL：軽量で高速、Web アプリケーションでよくつかわれる
- PostgreSQL：高機能で拡張性が高い、複雑なデータ処理が得意
- SQLite：ファイルベース、小規模なアプリケーション向け
PostgreSQLをつかう前の準備
インストールの確認 まずは、PostgreSQLがインストールされているか確認しましょう。
psql --version
サービスの起動（Linuxの場合）
sudo systemctl start postgresql
sudo systemctl enable postgresql # 自動起動設定
サービスの起動（macOSの場合）
brew services start postgresql
では、実際にPostgreSQLにつないでみましょう。
PostgreSQLへの接続・ログイン関連
基本的な接続方法
基本の接続コマンド
psql -U postgres # postgresユーザーで接続
psql -U myuser -d mydatabase # 特定のユーザーとデータベースで接続
psql -h localhost -p 5432 -U postgres # ホストとポートを指定
接続オプションの説明
-U：ユーザー名を指定-d：データベース名を指定-h：ホスト名を指定（デフォルト：localhost）-p：ポート番号を指定（デフォルト：5432）
接続の実例
初回接続（postgresユーザー）
$ psql -U postgres
Password for user postgres: [パスワードを入力]
postgres=# # プロンプトが変わったら接続成功
特定のデータベースに接続
$ psql -U myuser -d testdb
testdb=> # データベース名がプロンプトに表示される
psqlシェル内での操作
接続情報の確認
\conninfo -- 現在の接続情報を表示
シェルの終了
\q -- psqlを終了
exit -- または exit
ヘルプの表示
\? -- psqlコマンドのヘルプ
\h -- SQLコマンドのヘルプ
\h SELECT -- 特定のSQLコマンドのヘルプ
接続コマンドは、作業の出発点です。
次は、データベースの作成や一覧表示といった基本操作に進みましょう。
データベース操作コマンド
データベースの作成
基本的な作成方法
CREATE DATABASE testdb; -- 基本的なデータベース作成
CREATE DATABASE myapp
WITH OWNER = myuser
ENCODING = 'UTF8'; -- オプション付きで作成
文字エンコーディングを指定
CREATE DATABASE japanese_db
WITH ENCODING = 'UTF8'
LC_COLLATE = 'ja_JP.UTF-8'
LC_CTYPE = 'ja_JP.UTF-8'; -- 日本語対応
データベースの確認
データベース一覧の表示
\l -- 簡潔な一覧
\list -- 詳細な一覧
\l+ -- さらに詳細な情報
出力例
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | postgres | UTF8 | C | C |
データベースの接続切り替え
別のデータベースに接続
\c testdb -- testdbに接続
\connect myapp -- myappに接続
\c postgres postgres -- postgresデータベースにpostgresユーザーで接続
データベースの削除
データベースの削除
DROP DATABASE testdb; -- データベースを削除
DROP DATABASE IF EXISTS old_db; -- 存在する場合のみ削除
注意点
- 削除は取りもどせません
- 接続中のデータベースは削除できません
- 他のユーザーが接続していると削除できません
データベースのバックアップと復元
バックアップの作成（コマンドライン）
pg_dump -U postgres -d testdb > backup.sql # SQLファイルでバックアップ
pg_dump -U postgres -d testdb -Fc > backup.dump # カスタム形式でバックアップ
復元（コマンドライン）
psql -U postgres -d newdb < backup.sql # SQLファイルから復元
pg_restore -U postgres -d newdb backup.dump # カスタム形式から復元
データベースの作成と接続ができれば、次はテーブルの操作に挑戦してみましょう。
テーブル関連のコマンド
テーブルの作成
基本的なテーブル作成
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- 自動増分の主キー
name VARCHAR(100) NOT NULL, -- 必須の文字列
email VARCHAR(255) UNIQUE, -- ユニークな文字列
age INTEGER, -- 整数
created_at TIMESTAMP DEFAULT NOW() -- タイムスタンプ
);
よくつかうデータ型
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL, -- 制限なしの文字列
price DECIMAL(10,2), -- 10桁で小数点以下2桁
description TEXT,
in_stock BOOLEAN DEFAULT true, -- 真偽値
category_id INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
テーブルの確認
テーブル一覧の表示
\d -- すべてのテーブル一覧
\dt -- テーブルのみ一覧
\dt+ -- 詳細情報付きで一覧
特定のテーブル構造を表示
\d users -- usersテーブルの構造
\d+ users -- 詳細情報付きで表示
出力例
Table "public.users"
Column | Type | Collation | Nullable | Default
------------+------------------------+-----------+----------+---------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(100) | | not null |
email | character varying(255) | | |
age | integer | | |
created_at | timestamp | | | now()
テーブル構造の変更
カラムの追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- 電話番号カラムを追加
ALTER TABLE users ADD COLUMN status VARCHAR(10) DEFAULT 'active'; -- デフォルト値付き
カラムの変更
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(150); -- データ型の変更
ALTER TABLE users ALTER COLUMN age SET NOT NULL; -- NOT NULL制約を追加
ALTER TABLE users ALTER COLUMN email DROP NOT NULL; -- NOT NULL制約を削除
カラムの削除
ALTER TABLE users DROP COLUMN phone; -- カラムを削除
ALTER TABLE users DROP COLUMN IF EXISTS old_column; -- 存在する場合のみ削除
テーブル名の変更
ALTER TABLE old_users RENAME TO users; -- テーブル名を変更
ALTER TABLE users RENAME COLUMN name TO full_name; -- カラム名を変更
インデックスの作成
基本的なインデックス
CREATE INDEX idx_users_email ON users(email); -- emailにインデックス
CREATE INDEX idx_users_name_age ON users(name, age); -- 複合インデックス
CREATE UNIQUE INDEX idx_users_unique_email ON users(email); -- ユニークインデックス
インデックスの確認と削除
\di -- インデックス一覧
DROP INDEX idx_users_email; -- インデックスを削除
テーブルの削除
テーブルの削除
DROP TABLE users; -- テーブルを削除
DROP TABLE IF EXISTS old_table; -- 存在する場合のみ削除
DROP TABLE users CASCADE; -- 依存関係も含めて削除
テーブル構造を自由にあつかえるようになると、実用的なデータ管理が可能になります。
次は、データの操作方法を見ていきましょう。
データ操作（CRUD）コマンド
CRUDとは、Create（作成）、Read（読み取り）、Update（更新）、Delete（削除）の略で、データベースの基本操作です。
データの追加（CREATE – INSERT）
基本的なデータ追加
INSERT INTO users (name, email, age)
VALUES ('田中太郎', 'tanaka@example.com', 25);
-- 複数のデータを同時に追加
INSERT INTO users (name, email, age) VALUES
('佐藤花子', 'sato@example.com', 30),
('鈴木次郎', 'suzuki@example.com', 22),
('高橋美咲', 'takahashi@example.com', 28);
すべてのカラムにデータを追加
INSERT INTO users VALUES
(DEFAULT, '山田太郎', 'yamada@example.com', 35, DEFAULT);
-- DEFAULT は自動設定される値（SERIAL、TIMESTAMPなど）
他のテーブルからデータをコピー
INSERT INTO active_users (name, email)
SELECT name, email FROM users WHERE age >= 20;
データの読み取り（READ – SELECT）
基本的なデータ取得
SELECT * FROM users; -- すべてのデータを取得
SELECT name, email FROM users; -- 特定のカラムのみ取得
SELECT DISTINCT age FROM users; -- 重複を除いて取得
条件を指定してデータ取得
SELECT * FROM users WHERE age >= 25; -- 25歳以上
SELECT * FROM users WHERE name LIKE '田%'; -- 名前が「田」で始まる
SELECT * FROM users WHERE email IS NOT NULL; -- メールアドレスがある
SELECT * FROM users WHERE age BETWEEN 20 AND 30; -- 20〜30歳
並び替えとページング
SELECT * FROM users ORDER BY age ASC; -- 年齢の昇順
SELECT * FROM users ORDER BY created_at DESC; -- 作成日時の降順
SELECT * FROM users ORDER BY age, name; -- 年齢、名前の順
-- ページング
SELECT * FROM users LIMIT 10; -- 最初の10件
SELECT * FROM users LIMIT 10 OFFSET 20; -- 21〜30件目
集計関数
SELECT COUNT(*) FROM users; -- 総数
SELECT AVG(age) FROM users; -- 平均年齢
SELECT MAX(age), MIN(age) FROM users; -- 最高年齢、最低年齢
SELECT COUNT(*) FROM users WHERE age >= 30; -- 30歳以上の人数
グループ化
SELECT age, COUNT(*) FROM users GROUP BY age; -- 年齢別の人数
SELECT age, COUNT(*) FROM users
GROUP BY age HAVING COUNT(*) >= 2; -- 2人以上いる年齢
データの更新（UPDATE）
基本的なデータ更新
UPDATE users SET age = 26 WHERE id = 1; -- 特定のレコードを更新
UPDATE users SET email = 'new@example.com'
WHERE name = '田中太郎'; -- 名前で検索して更新
複数のカラムを同時更新
UPDATE users SET
name = '田中太郎（更新）',
age = 26,
email = 'tanaka_new@example.com'
WHERE id = 1;
条件付きの一括更新
UPDATE users SET age = age + 1; -- 全員の年齢を1歳プラス
UPDATE users SET status = 'inactive'
WHERE created_at < '2023-01-01'; -- 古いレコードを無効化
データの削除（DELETE）
基本的なデータ削除
DELETE FROM users WHERE id = 1; -- 特定のレコードを削除
DELETE FROM users WHERE age < 18; -- 18歳未満を削除
DELETE FROM users WHERE email IS NULL; -- メールアドレスがないレコードを削除
テーブルのデータをすべて削除
DELETE FROM users; -- すべてのデータを削除（構造は残る）
TRUNCATE TABLE users; -- 高速ですべてのデータを削除
TRUNCATE TABLE users RESTART IDENTITY; -- シーケンスもリセット
安全なデータ操作のコツ
トランザクションをつかう
BEGIN; -- トランザクション開始
UPDATE users SET age = 30 WHERE id = 1;
-- 確認してから...
COMMIT; -- 確定
-- または
ROLLBACK; -- 取りけし
操作前にかならず確認
-- 削除前に該当レコードを確認
SELECT * FROM users WHERE age < 18;
-- 問題なければ削除実行
DELETE FROM users WHERE age < 18;
これらのCRUD操作をマスターすれば、実際のアプリケーションでも活用できます。
次に、ユーザーやアクセス権の管理を見ていきましょう。
ユーザー・アクセス権限コマンド
ユーザーの作成と管理
基本的なユーザー作成
CREATE USER devuser WITH PASSWORD 'mypassword'; -- 基本的なユーザー
CREATE USER admin_user WITH PASSWORD 'securepass' CREATEDB; -- DB作成権限付き
CREATE USER readonly_user WITH PASSWORD 'readpass'; -- 読み取り専用想定
ユーザーの属性設定
CREATE USER webapp_user
WITH PASSWORD 'webapp123'
LOGIN -- ログイン可能
NOSUPERUSER -- スーパーユーザーではない
NOCREATEDB -- DB作成権限なし
NOCREATEROLE -- ロール作成権限なし
NOINHERIT -- 権限継承なし
VALID UNTIL '2025-12-31'; -- 有効期限
ユーザー情報の確認
ユーザー一覧の表示
\du -- ユーザー一覧
\du+ -- 詳細情報付き
特定のユーザー情報
SELECT * FROM pg_user; -- システムビューで確認
SELECT usename, usesuper, usecreatedb FROM pg_user WHERE usename = 'devuser';
パスワードの変更
自分のパスワード変更
\password -- 対話形式でパスワード変更
他のユーザーのパスワード変更（管理者のみ）
ALTER USER devuser WITH PASSWORD 'newpassword';
データベースレベルの権限管理
データベースへのアクセス権限
-- データベースへの接続権限を付与
GRANT CONNECT ON DATABASE testdb TO devuser;
-- データベース内のすべての権限を付与
GRANT ALL PRIVILEGES ON DATABASE testdb TO devuser;
-- 特定の権限のみ付与
GRANT CREATE ON DATABASE testdb TO devuser;
権限の剥奪
REVOKE CONNECT ON DATABASE testdb FROM devuser; -- 接続権限を剥奪
REVOKE ALL PRIVILEGES ON DATABASE testdb FROM devuser; -- すべての権限を剥奪
テーブルレベルの権限管理
テーブルの権限付与
-- 特定のテーブルの読み取り権限
GRANT SELECT ON users TO readonly_user;
-- 特定のテーブルのすべての権限
GRANT ALL PRIVILEGES ON users TO devuser;
-- 複数テーブルに権限を付与
GRANT SELECT, INSERT, UPDATE ON users, products TO webapp_user;
-- スキーマ内のすべてのテーブルに権限付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
今後作成されるテーブルにも権限を付与
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_user;
ロール（グループ）の作成
ロールの作成
CREATE ROLE developers; -- 開発者グループ
CREATE ROLE readers; -- 読み取り専用グループ
ロールに権限を付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developers;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readers;
ユーザーをロールに追加
GRANT developers TO devuser; -- devuserを開発者グループに追加
GRANT readers TO readonly_user; -- readonly_userを読み取りグループに追加
ユーザーの削除
ユーザーの削除
DROP USER devuser; -- ユーザーを削除
DROP USER IF EXISTS old_user; -- 存在する場合のみ削除
権限をもつユーザーの削除
-- まず権限を剥奪
REVOKE ALL PRIVILEGES ON DATABASE testdb FROM devuser;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM devuser;
-- その後削除
DROP USER devuser;
便利なpsqlコマンド
システム情報の確認
基本的なシステム情報
\conninfo -- 現在の接続情報
\du -- ユーザー一覧
\l -- データベース一覧
\dn -- スキーマ一覧
\df -- 関数一覧
テーブルとカラムの情報
\d -- テーブル、ビュー、シーケンス一覧
\dt -- テーブル一覧のみ
\dv -- ビュー一覧のみ
\ds -- シーケンス一覧のみ
\di -- インデックス一覧
SQL実行の便利機能
SQLファイルの実行
\i /path/to/script.sql -- SQLファイルを実行
\ir script.sql -- 相対パスで実行
出力設定
\timing on -- クエリ実行時間を表示
\timing off -- 実行時間表示を停止
\x -- 拡張表示モード（縦表示）
\x off -- 通常表示モードに戻る
結果をファイルに出力
\o output.txt -- 出力をファイルにリダイレクト
SELECT * FROM users; -- クエリ実行
\o -- ファイル出力を停止
設定とカスタマイズ
プロンプトのカスタマイズ
\set PROMPT1 '%n@%m:%> ' -- プロンプトを変更
\set PROMPT2 '%n@%m:%> ' -- 継続行のプロンプト
エイリアスの設定
\set show_users 'SELECT id, name, email FROM users ORDER BY id;'
:show_users -- エイリアスを実行
トラブルシューティング
よくあるエラーと解決法
接続エラー
# エラー: could not connect to server
# 解決策：PostgreSQLサービスの確認
sudo systemctl status postgresql
sudo systemctl start postgresql
権限エラー
-- エラー: permission denied for table users
-- 解決策：権限の確認と付与
\z users -- テーブルの権限確認
GRANT SELECT ON users TO current_user;
データベースが見つからない
-- エラー: database "mydb" does not exist
-- 解決策：データベースの存在確認
\l -- データベース一覧で確認
CREATE DATABASE mydb; -- 必要に応じて作成
パフォーマンスのチェック
クエリの実行計画
EXPLAIN SELECT * FROM users WHERE age > 25; -- 実行計画を表示
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25; -- 実際の実行統計も表示
長時間実行中のクエリ
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '1 minutes';
データベースのサイズ確認
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database ORDER BY pg_database_size(datname) DESC;
まとめ：PostgreSQLを使いこなそう
PostgreSQLのコマンドは一見複雑に見えますが、慣れれば強力なツールとなり、日々のデータ管理を効率化してくれます。
この記事のポイント
- 接続とログイン：psqlでの接続方法とオプション
- データベース操作：作成、削除、バックアップ
- テーブル操作：構造の作成・変更・削除
- CRUD操作：データの作成・読取・更新・削除
- ユーザー管理：権限設定とセキュリティ
コメント