【初心者必見】PostgreSQLの代表的なコマンド一覧【何度も使う】

データベース操作関連コマンド

1. データベースの作成:

   CREATE DATABASE データベース名;
   

2. データベースへの接続:

   \c データベース名;
   

3. データベースの一覧表示:

   \l
   

テーブル操作関連コマンド

1. テーブルの作成:

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

2. レコードの挿入:

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

3. レコードの取得:

   SELECT * FROM テーブル名 WHERE 条件;
   

4. テーブルの一覧表示:

   \dt
   

クエリ関連コマンド

1. テーブル結合:

   SELECT * FROM テーブル1 JOIN テーブル2 ON 条件;
   

2. 条件付きクエリ:

   SELECT * FROM テーブル名 WHERE 条件;
   

3. ソート:

   SELECT * FROM テーブル名 ORDER BY カラム名 ASC/DESC;
   

4. 集計:

   SELECT カラム, 関数(カラム) FROM テーブル名 GROUP BY カラム;
   

データの更新と削除

1. データの更新:

   UPDATE テーブル名 SET カラム = 新しい値 WHERE 条件;
   

2. データの削除:

   DELETE FROM テーブル名 WHERE 条件;
   

インデックス関連コマンド

1. インデックスの作成:

   CREATE INDEX インデックス名 ON テーブル名 (カラム名);
   

ビュー関連コマンド

1. ビューの作成:

   CREATE VIEW ビュー名 AS クエリ;
   

2. ビューの削除:

   DROP VIEW ビュー名;
   

バックアップとリストア関連コマンド

1. バックアップの作成:

   pg_dump -h ホスト名 -U ユーザー名 -d データベース名 -f バックアップファイル名
   

2. バックアップからのリストア:

   pg_restore -h ホスト名 -U ユーザー名 -d データベース名 バックアップファイル名
   

ロールと権限関連コマンド

1. ロールの作成:

   CREATE ROLE ロール名 WITH LOGIN PASSWORD 'パスワード';
   

2. ロールへの権限付与:

   GRANT 権限名 ON オブジェクト名 TO ロール名;
   

知っておくと便利１

1. DESCRIBEコマンド（\d+）: テーブルやビューの詳細な情報を表示します。カラムや制約、インデックスなどの情報が含まれます。

   \d+ テーブル名
   

2. EXPLAINコマンド: クエリの実行計画を表示して、クエリの最適化やパフォーマンスの改善ポイントを特定します。

   EXPLAIN SELECT * FROM テーブル名 WHERE 条件;
   

3. VACUUMコマンド: データベースのメンテナンスを行い、削除された行によるスペースを回収します。

   VACUUM テーブル名;
   

4. ANALYZEコマンド: テーブルの統計情報を更新してクエリプランナーの最適化を向上させます。

   ANALYZE テーブル名;
   

5. ALTER COLUMNコマンド: テーブルのカラムの属性を変更します。データ型の変更やNULL制約の追加・削除などが可能です。

   ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET データ型;
   

6. CREATE EXTENSIONコマンド: PostgreSQLの拡張機能を有効化します。例えば、hstoreやuuid-osspなどがあります。

   CREATE EXTENSION 拡張機能名;
   

7. COPYコマンド: ファイルからテーブルにデータを効率的にロードします。

   COPY テーブル名 FROM 'ファイルパス' DELIMITER ',' CSV HEADER;
   

8. SHOWコマンド: PostgreSQLの設定やシステム情報を表示します。例えば、設定値の取得やバージョン情報の確認ができます。

   SHOW 設定名;
   

9. REINDEXコマンド: インデックスを再構築します。データベースのパフォーマンスを向上させるために行われることがあります。

   REINDEX INDEX インデックス名;
   

10. RESETコマンド: ユーザーが設定したセッションレベルの変数をリセットします。

    RESET 変数名;
    

便利すぎ２

1. COUNT関数: 指定したカラムの非NULL値の数をカウントします。

   SELECT COUNT(カラム名) FROM テーブル名 WHERE 条件;
   

2. SUM関数: 指定したカラムの数値を合計します。

   SELECT SUM(カラム名) FROM テーブル名 WHERE 条件;
   

3. AVG関数: 指定したカラムの数値の平均値を計算します。

   SELECT AVG(カラム名) FROM テーブル名 WHERE 条件;
   

4. MAX関数: 指定したカラムの最大値を抽出します。

   SELECT MAX(カラム名) FROM テーブル名 WHERE 条件;
   

5. MIN関数: 指定したカラムの最小値を抽出します。

   SELECT MIN(カラム名) FROM テーブル名 WHERE 条件;
   

6. COUNT(*): テーブル内の全レコードの数をカウントします。

   SELECT COUNT(*) FROM テーブル名;
   

7. GROUP BYとCOUNT: グループごとのレコード数をカウントします。

   SELECT カラム名, COUNT(*) FROM テーブル名 GROUP BY カラム名;
   

8. HAVING句: GROUP BYの結果に対して条件を指定します。

   SELECT カラム名, COUNT(*) FROM テーブル名 GROUP BY カラム名 HAVING COUNT(*) > 5;
   

便利すぎ３

1. BETWEEN演算子: 指定した範囲内の値をカウントします。

   SELECT COUNT(*) FROM テーブル名 WHERE カラム名 BETWEEN 値1 AND 値2;
   

2. LIKE演算子: パターンにマッチする値をカウントします。%は任意の文字列を、_は任意の一文字を表します。

   SELECT COUNT(*) FROM テーブル名 WHERE カラム名 LIKE 'パターン';
   

3. DISTINCTキーワード: 重複を除いた値の数をカウントします。

   SELECT COUNT(DISTINCT カラム名) FROM テーブル名;
   

4. CASE文: 条件に応じて値をカウントします。

   SELECT COUNT(CASE WHEN 条件1 THEN カラム名 ELSE NULL END) FROM テーブル名;
   

5. NULL値のカウント: NULL値の数をカウントします。

   SELECT COUNT(*) FROM テーブル名 WHERE カラム名 IS NULL;
   

6. EXISTSサブクエリ: サブクエリの結果に応じて条件を満たす値をカウントします。

   SELECT COUNT(*) FROM テーブル名 WHERE EXISTS (SELECT 1 FROM 別のテーブル WHERE 条件);
   

7. ARRAY[]: 配列型のカラム内の要素数をカウントします。

   SELECT COUNT(配列カラム名) FROM テーブル名;