psqlとは？PostgreSQLをコマンドラインで操作する基本コマンドと実践的な使い方

psql --version

psql (PostgreSQL) 16.4

# Homebrewでインストールした場合はそのまま実行可能
psql -U postgres

# PATHが通っていない場合はフルパスで指定
"C:\Program Files\PostgreSQL\16\bin\psql.exe" -U postgres

# または「SQL Shell (psql)」をスタートメニューから起動

# パッケージマネージャーでインストールした場合
sudo -u postgres psql

psql -h ホスト名 -p ポート番号 -U ユーザー名 -d データベース名

# ローカルのデフォルトデータベースに接続
psql -U postgres

# 特定のデータベースに接続
psql -U postgres -d myapp_development

# リモートサーバーに接続
psql -h db.example.com -p 5432 -U admin -d production

# 接続URI形式（Supabaseなどのクラウドサービスで一般的）
psql "postgresql://user:password@host:5432/dbname?sslmode=require"

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "postgres"

psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?

# macOS（Homebrew）
brew services list | grep postgresql

# Linux（systemd）
sudo systemctl status postgresql

# サーバーを起動する場合
# macOS
brew services start postgresql@16
# Linux
sudo systemctl start postgresql

psql: error: FATAL: database "myuser" does not exist

# まずpostgresデータベースに接続
psql -U postgres -d postgres

-- データベース一覧の表示
postgres=# \l
                                  List of databases
      Name      |  Owner   | Encoding |   Collate   |    Ctype    
----------------+----------+----------+-------------+-------------
 myapp_dev      | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8
 myapp_test     | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8
 postgres       | postgres | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8

-- データベースの切り替え
postgres=# \c myapp_dev
You are now connected to database "myapp_dev" as user "postgres".
myapp_dev=#

-- テーブル一覧の表示
myapp_dev=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | users    | table | postgres
 public | orders   | table | postgres
 public | products | table | postgres

-- テーブル構造の表示
myapp_dev=# \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) |           | not null | 
 created_at | timestamp              |           |          | CURRENT_TIMESTAMP

-- 拡張表示をONにする（カラムが多いテーブルで便利）
myapp_dev=# \x
Expanded display is on.

myapp_dev=# SELECT * FROM users WHERE id = 1;
-[ RECORD 1 ]-------------------
id         | 1
name       | 佐藤太郎
email      | sato@example.com
created_at | 2024-01-15 09:30:00

-- クエリの実行時間を計測
myapp_dev=# \timing
Timing is on.

myapp_dev=# SELECT COUNT(*) FROM orders;
 count 
-------
 15234
(1 row)

Time: 12.345 ms

-- データベースの作成
CREATE DATABASE myapp_development;

-- 作成したデータベースに切り替え
\c myapp_development

-- テーブルの作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- テーブルが作成されたか確認
\dt

-- INSERT：データの挿入
INSERT INTO users (name, email, age) VALUES ('佐藤太郎', 'sato@example.com', 28);
INSERT INTO users (name, email, age) VALUES ('鈴木花子', 'suzuki@example.com', 32);
INSERT INTO users (name, email, age) VALUES ('田中一郎', 'tanaka@example.com', 25);

-- SELECT：データの取得
SELECT * FROM users;

-- 条件付き取得
SELECT name, email FROM users WHERE age >= 28;

-- UPDATE：データの更新
UPDATE users SET age = 29 WHERE name = '佐藤太郎';

-- DELETE：データの削除
DELETE FROM users WHERE id = 3;

-- 削除後の確認
SELECT * FROM users;

# psqlの外から実行する方法
psql -U postgres -d myapp_development -f setup.sql

# psqlの中から実行する方法
myapp_development=# \i /path/to/setup.sql

# .pgpassファイルの作成（Mac / Linux）
touch ~/.pgpass
chmod 600 ~/.pgpass

# ホスト名:ポート:データベース名:ユーザー名:パスワード
localhost:5432:myapp_development:postgres:mypassword
db.example.com:5432:production:admin:secretpassword

# ワイルドカードも使用可能
localhost:5432:*:postgres:mypassword

-- ~/.psqlrc の設定例

-- プロンプトにデータベース名とユーザー名を表示
\set PROMPT1 '%n@%/%R%# '

-- NULLを視覚的に分かりやすく表示
\pset null '(NULL)'

-- クエリの実行時間を常に表示
\timing

-- 自動補完を大文字小文字を無視して実行
\set COMP_KEYWORD_CASE upper

-- ヒストリ（履歴）の重複を除外
\set HISTCONTROL ignoredups

-- テーブルのデータをCSVファイルにエクスポート
\copy users TO '/tmp/users_export.csv' WITH CSV HEADER

-- CSVファイルからテーブルにインポート
\copy users (name, email, age) FROM '/tmp/users_import.csv' WITH CSV HEADER

-- 特定の条件のデータだけエクスポート
\copy (SELECT name, email FROM users WHERE age >= 30) TO '/tmp/senior_users.csv' WITH CSV HEADER