それマグで！

知識はカップより、マグでゆっくり頂きます。　takuya_1stのブログ

習慣に早くから配慮した者は、 おそらく人生の実りも大きい。
トップ > PostgresSQLの基本的な使い方（ユーザ作成・テーブル作成・確認）ー SQL編+psql で結果確認

PostgresSQLの基本的な使い方（ユーザ作成・テーブル作成・確認）ー SQL編+psql で結果確認

目次

psql でPostgreSQLの基本的な操作

PostgreSQLの基本的な管理を見ていきます。

基本的な管理はSQLとして psql のプロンプトで行うものを取り扱います。

initdb や createdb dropuser のようなシェルコマンドは今回扱いません。

psql を使う前に。

インストール前に、日本語を扱う場合のポイント。

日本語環境が設定されて無いときは、日本語環境を作っておく。 時刻も日本JSTにしておく。

Debian / ubuntu の例

dpkg-reconfigure locales
dpkg-reconfigure tzdata

日本語環境の設定してからインストールするのが無難。

インストール ( debian )

sudo apt install postgersql  sudo

psql の対話型シェルの起動

postgres ユーザーが作成されるので、最初の処理は postgresql ユーザーで行う。

root@:~# sudo -u postgres psql

ユーザーの管理。

ユーザーの管理を psql コマンドで行うことができます。postgresql user と role がありますが、歴史的経緯みたいなものだと思っていればいいです。

ユーザーは login role のエイリアスだったはず。

ユーザーの追加削除と確認

ユーザーの一覧は psql で \du を使います。 du は display user とでも覚えときましょう。

実行サンプル

postgres ユーザー１つだけが存在します。

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

ユーザーの追加。

ユーザーの追加は、 SQL で CREATE USER を使えば可能です。

CREATE USER "takuya_1st" PASSWORD 'j0qViQjCzQMFfqH7';

シングルクォーテーション｀ダブルクォーテーションに注意する！！PSQLは厳密に区別してる。

ユーザ追加サンプル

ユーザーを追加して、結果を確認。一つ増えるのがわかります。

postgres=# CREATE USER "takuya_1st" PASSWORD 'j0qViQjCzQMFfqH7';
postgres=# \du
takuya_1st  |                                                            | {}
 postgres               | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

ユーザーを削除して、結果を確認。

ユーザーの削除は、 SQLのDROP USER 文でできます。

DROP USER "takuya_1st" ;

ユーザー削除サンプル

ユーザーを削除して、ユーザー一覧から消えたことがわかります。

postgres=# DROP USER "takuya_1st" ;
postgres=# \du
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

パスワードの変更

パスワードの変更もSQLを使って可能です。

postgres=# ALTER USER postgres  WITH PASSWORD 'rHBrECDNjbWax1IZ';
ALTER ROLE

初期ユーザーの postgres のパスワードを変えてみるなど。

ROLEロール。create user と create role の違い。

postgresql では ユーザのことを role と呼びます。 LOGIN できるロールのことをユーザーと呼ぶ。と覚えてください。 歴史的経緯によるとでも思っておけばいいと思います。UNIXのユーザーようにPostgreSQLのユーザーはグループをもっていて、グループにロールで管理されています。１つのデータベースのインスタンスを多数のクライアントで共有していた名残りでしょう。個人でデータベースを作る、個人でプログラムから使うときなど、はあまり気にしなくていいと思います。

ロールの作成と確認

これは、create user と同じ

CREATE ROLE "outline_Admin" PASSWORD '01UNeD86gwVmRDRG';

データベースを作成する

日本語UTF-8に対応したテンプレートを作る。

シェル環境変数が LANG=ja_JP.UTF-8なら自動で作られるのだけど。作られていないとUTF-8でも行けそうなん。 気持ち悪いなら、日本語対応したテンプレートを作っておくといいかもしれない。

CREATE DATABASE template1 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
CREATE DATABASE template2 LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;
UPDATE pg_database SET datistemplate = true where datname = 'template2';

データベースの一覧

対話プロンプト psql のコマンドで \l でデータベースの一覧を見れます。 List database の L(エル)と覚えておけばいいでしょう。

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

ここで出てくる template は言語ごとのCollationが設定されたテンプレートです。debian/ubuntu の場合 LANG=ja_JP.UTF-8のbashから apt install postgres すると postgres のaptインストールスクリプトで自動生成されます。

データベースの切替え

データベースを切り替えるにはつぎのように psql コマンド \c で可能です。

\c databae_name

これは、mysql の use DATABASENAME に相当する。

実行サンプル

切り替えたときのサンプル。プロンプトが変わるのでわかる。

postgres=# \c outline
You are now connected to database "outline" as user "postgres".
outline=# \q

データベースを作成。

SQLでデータベースを作成することができます。

CREATE DATABASE my_database;

データベースの作成サンプル

outline という名前でデータベースを作って一覧を見てみます。

postgres=# create database outline;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 outline   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

データベースを削除する

SQL 構文でデータベースを削除することができます。

DROP DATABASE my_database;

実行サンプル

outline という名前のデータベースを削除し、一覧を見てみます。

postgres=# drop database outline;
DROP DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

オーナー指定してデータベースを作る

SQLでデータベースを作るときに所有者（オーナー）を指定して作ることができます。

CREATE DATABASE takuya_database  OWNER "takuya";

これは非常によく使います。

実行サンプル

ユーザーを作成し、続けてそのユーザーのデータベースを作成します。

postgres=# CREATE USER "outline_Admin" PASSWORD 'kj4zzkvVXuU7MlSL';
CREATE ROLE
postgres=# create database outline owner "outline_Admin";
CREATE DATABASE
postgres=# \l
                                         List of databases
   Name    |         Owner          | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+------------------------+----------+-------------+-------------+-----------------------
 outline   | outline_Admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |                        |          |             |             | postgres=CTc/postgres
 template1 | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |                        |          |             |             | postgres=CTc/postgres

データベース作成時：文字コードを指定

文字コードを指定してつくると便利です。 テンプレートを使って作る事が多いです。

CREATE DATABASE outline  LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' 
CREATE DATABASE outline  LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;

template が en_US だし困ったなぁってときに、日本語でDBを作成しておくとトラブルが減る*1

実行サンプル

オーナー指定とテンプレート指定、文字コード指定を「全部合わせ」てデータベースを作成している例です。

CREATE DATABASE outline owner "outline_Admin"  LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8' TEMPLATE template0;

postgres=# \l
                                         List of databases
   Name    |         Owner          | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+------------------------+----------+-------------+-------------+-----------------------
 outline   | outline_Admin | UTF8     | ja_JP.UTF-8 | ja_JP.UTF-8 |
 postgres  | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 repost    | repost                 | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |                        |          |             |             | postgres=CTc/postgres
 template1 | postgres               | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |                        |          |             |             | postgres=CTc/postgres
(5 rows)

おさらい psql コマンド

データベースの切り替え

postgres=#  \c outline
### データベースの一覧
postgres=#  \l
### ユーザー一覧
postgres=# \du

おさらい SQL での管理。

# ユーザー作成
 CREATE USER "takuya_1st" PASSWORD 'j0qViQjCzQMFfqH7';

## パスワード変更
ALTER USER postgres  WITH PASSWORD 'rHBrECDNjbWax1IZ';

# データベース作成
CREATE DATABASE takuya_database ;
CREATE DATABASE takuya_database  OWNER "takuya";
CREATE DATABASE outline  owner "takuya" LC_COLLATE 'ja_JP.UTF-8' LC_CTYPE 'ja_JP.UTF-8' ENCODING 'UTF8'

2020-07-19

更新

*1: 個人の感想です。