Pythonスキルの習得

【Mac対象】PostgreSQL コマンド まとめ （導入から解説）

【Mac対象】PostgreSQLコマンド（導入から解説）
​

Macを使用してソフトウェア開発している方で、

  • 急にPostgreSQLを使う事になってしまった。
  • 今までMySQLを使っていたけど、乗り換えたい。
  • 初めてのDBで、何から学習すればいいか分からない。

と思っている方々を対象にした記事です。

本記事により、PostgreSQLの導入から視覚的に操作するためのツール「pgAdmin」の使い方まで、一通りの基本知識が習得できます。

目次
  1. PostgreSQLの導入方法
  2. PostgreSQLの初期化
  3. PostgreSQLの起動、停止処理
  4. DBの操作方法（コマンド）
  5. ロール（誰が何の権限でどのテーブルにアクセスできるか）関係の操作
  6. 視覚的に操作できる「pgAdmin」の操作方法
  7. 最後に

PostgreSQLの導入方法

brewを使用するのが、最も簡単です。
ターミナルを開いて、下記を入力するだけです。

brew install postgresql
# versionを見たい場合 
postgres --version

次に、視覚的に操作できる「pgAdmin」をインストールします。（任意）
使用方法はのちほど、詳細を記します。

brew cask install pgadmin4

PostgreSQLの初期化

最初に初期化する必要があります。（何故か私の今の環境では、不要でした。）

initdb /usr/local/var/postgres -E utf8

デフォルトでは、現在のMACユーザ名が、postgreSQLのユーザ名に追加されます。

PostgreSQLの起動、停止処理

起動・停止関係の処理を記します。こちらもbrewを使用するのが簡単だと思います。

# 起動 
brew services start postgresql
# 停止
brew services stop postgresql
# 再起動
brew services restart postgresql

# （参考）brew上で起動しているサービス一覧を確認
brew services list

DBの操作方法（コマンド）

DB一覧 確認コマンド

作成済みのDBを確認するコマンドです。

「PostgreSQL」の初期状態では、以下の表示になります。

psql -l

# 以下は実行結果(例)
                               List of databases
    Name    |   Owner   | Encoding | Collate | Ctype |    Access privileges    
------------+-----------+----------+---------+-------+-------------------------
 example-db | ***       | UTF8     | C       | C     | 
 postgres   | ***       | UTF8     | C       | C     | 
 template0  | ***       | UTF8     | C       | C     | =c/***      +
            |           |          |         |       | ***=CTc/***
 template1  | ***       | UTF8     | C       | C     | ***=CTc/***+
            |           |          |         |       | =c/***
 test       | ***       | UTF8     | C       | C     |

DBの作成、削除コマンド

# データベース新規作成
createdb データベース名

# データベース削除
dropdb データベース名

デーブル関係コマンド

SQLの入力受付け状態に入るコマンド

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

# 下記条件の場合、データベースのみの入力でOK
　　（現在のユーザー名で、ホスト:localhost 
　　　　ポート：postgreSQLのデフォルトポートの場合）
psql データベース名

# ⬆実行結果 (データベース名に「test」を指定した場合)
psql (13.1)
Type "help" for help.

test=# 
#  ⬆　SQLが入力できる状態

SQL入力受付け状態から出るコマンド

\q   # 文末にセミコロンはつけない
# ショートカット ctrl−d でもOK

テーブル一覧の確認、テーブルのカラム情報を見るコマンド

# テーブル一覧を確認
\dt;  # 文末にセミコロン無しでもOK
# ⬆実行結果　（例）
          List of relations
 Schema |  Name   | Type  |   Owner   
--------+---------+-------+-----------
 public | mytable | table | ********



# テーブルのカラム情報を確認
\d (テーブル名);   # 例 \d mytable
# ⬆実行結果　（例）
                Table "public.mytable"
 Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------
 id     | integer               |           | not null | 
 name   | character varying(10) |           |          | 
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (id)

テーブルのCRUD（Create, Read, Update, Delete）

普通のSQL文です。参考までに記します。

# テーブル作成　（テーブル名、カラム名は、一例です。）
CREATE TABLE mytable (
  id integer PRIMARY KEY, 
  name varchar(10)
);

# テーブル削除
DROP table mytable;

# 全データの読込み
SELECT * FROM mytable;

# インサート
INSERT INTO mytable VALUES (2, 'Nakagawa');

# 更新
UPDATE mytable SET name = 'Asai' where id = 2;

# 削除
DELETE FROM mytable; # ←の場合、全データが削除されます

ロール（誰が何の権限でどのテーブルにアクセスできるか）関係の操作

以下のコマンドは、SQLの入力受付け状態に入ってから打ち込みます。

ロール一覧 確認コマンド　

\du;

# ⬆　実行結果
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 taro        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 jiro        |                                                            | {}
 saburo      | Superuser                                                  | {}

各ロール 権限確認コマンド

下記の通り、各テーブル毎に権限は付与できます。

\dp;

# ⬆実行結果
                                   Access privileges
 Schema |  Name   | Type  |      Access privileges      | Column privileges | Policies 
--------+---------+-------+-----------------------------+-------------------+----------
 public | mytable | table | taro=arwdDxt/taro+          |                   | 
        |         |       | jiro=arwdDxt/taro           |                   | 

Access privileges の記号
a: INSERT(add)
r: SELECT(read)
w: UPDATE(write)
d: DELETE
D: TRUNCATE
x: REFERENCES

ロールの新規作成コマンド

# 一般ユーザーの場合
CREATE ROLE username LOGIN PASSWORD 'password';
# ⬆ username:ユーザー名、password:パスワード　を入れて下さい。
# ⬆ LOGINを入れないと、ログインできなくなります。

# ログイン権限 無しでもOK（後から設定も可能）
CREATE ROLE testuser PASSWORD 'password';

# パスワード設定 無しでもOK（後から設定も可能）
CREATE ROLE testuser;

# スーパーユーザーの場合
CREATE ROLE username superuser;
# ⬆LOGINを付けていないので、superuserなのに、ログインできません・・・

作成したロールに、パスワード、LOGIN権限を付与するコマンド

#　パスワードの設定
ALTER ROLE username PASSWORD 'password';

# ログイン権限を付与
ALTER ROLE username LOGIN;

ロールにテーブル操作権限を付与するコマンド

# 個別権限を付与する場合
GRANT 権限 ON tablename TO username;
# ⬆　tablename:テーブル名、 username:ユーザー名　を入力
# ⬆　権限は
SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、
CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE  から選択

# 全権限を付与する場合
GRANT all ON tablename TO username;

各権限の詳細情報はこちら

ロールからテーブル操作権限を剥奪するコマンド

REVOKE 権限 ON tablename FROM username;

ロールを削除するコマンド

DROP ROLE username;

視覚的に操作できる「pgAdmin」の操作方法

DB接続設定

起動すると下記画面が出てきます。

[Servers]を右クリックし、[Create]→[Server…]をクリックします。

下記のダイアログが、出てきます。
Nameは、任意です。　

次にConnectionのタブを開きます。

  • Hostname/address: 127.0.0.1（localの場合）
  • Port: 5432 （デフォルト）
  • Maintenace database: postgres
  • Usernmae: ロール名
  • Password:  そのロール名のパスワード

これで繫げると、下記のようなツリーになります。(DB一覧が表示されます。)

テーブルデータの閲覧方法、変更方法


DB→Schemas→public→Tablesの直下にテーブル一覧があります。
（下図は一例です。）

テーブルデータを見たい場合、テーブルを右クリック→veiw/Edit Data→All Rowsを選択します。

下記画面が出てきます。

ここから、値を変更したり、挿入したりできます。

最後に

後はきっと、ORM様があればOKです（笑）

RELATED POST