このセクションでは、PostgreSQL データベースの基本的な操作方法について紹介します。

PostgreSQL データベースに接続します。

[root@db-1 ~]# sudo -u postgres psql -U postgres
could not change directory to "/root": Permission denied
psql (11.1)
Type "help" for help.

postgres=# 

データベースの一覧確認

データベースの一覧を表示します。

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 pgdb01    | pguser   | UTF8     | C           | C           | 
 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
(4 rows)

postgres=# 

データベースの作成

データベースを作成します。ここでは、pgdb というデータベースを作成しています。OWNER を指定せずに作成した場合には、データベースを作成したログインユーザーが作成したデータベースのオーナーになります。

postgres=# CREATE DATABASE pgdb;
CREATE DATABASE
postgres=# 

以下コマンドで、諸条件を細かく指定してデータベースを作成することもできます。

「OWNER」	データベース所有者を指定。ここでは、pguser ユーザーを指定しています。
「TEMPLATE」	データベースを作成する際に使用するテンプレートを指定。新しいデータベースを作成する場合には template0 を指定し、他のデータベースの内容を引き継いで作成する場合には他のテンプレートを指定します。
「ENCODING」	データベースで使用する文字コードを指定。ここでは、UTF8を指定しています。
「LC_COLLATE」	データベースで使用する照合順序を指定。照合順序は、ORDER BY 句でのソート順などに影響する設定となります。ここでは、文字のソートはコード順になるように ‘C’ を指定しています。 日本語を考慮した「ja_JP.UTF-8」といった指定もできますが、パフォーマンスが悪くなることがあるようです。
「LC_CTYPE」	データベースで使用する文字の大文字、小文字、数字といった分類を指定。ここでは、特別な処理は行わない ‘C’を指定しています。日本語を考慮した「ja_JP.UTF-8」を指定すると、LOWER関数で全角の英字も小文字への変換対象として扱われるようになったりするようです。

postgres=# CREATE DATABASE pgdb01 OWNER = pguser TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C';
CREATE DATABASE
postgres=#

データベースの削除

データベースを削除します。ここでは、 pgdb というデータベースを削除しています。

postgres=# drop database pgdb;
DROP DATABASE
postgres=#

データベースの切替え

データベースの切り替えをします。ここでは、knowledge データベースから postgres データベースへの切り替えを行っています。

knowledge=> \c postgres
You are now connected to database "postgres" as user "knowledge".
postgres=> 

データベースへの権限付与

データベースのテーブルへのアクセス権限を確認します。ここでは、テーブルへのアクセス権限がオーナー以外付与されていない（ Access privileges が空白）ことが確認できます。

pgdb01=# \z
                            Access privileges
 Schema | Name | Type  | Access privileges | Column privileges | Policies 
--------+------+-------+-------------------+-------------------+----------
 public | t1   | table |                   |                   | 
(1 row)

pgdb01=# 

テーブルへの全てのアクセス権限をオーナーではない異なるユーザーに付与します。ここでは、 pguser ユーザーに対してフル権限を付与しています。

postgres=# grant all privileges on all tables in schema public to pguser;
GRANT
postgres=# 

データベースのテーブルへのアクセス権限を確認します。pguser ユーザーにテーブルへのアクセス権限が付与されていることが確認できます。

a
r	SELECT可能(read)
w	UPDATE可能(write)
d	DELETE可能(delete)
D	TRUNCATE
x	REFERENCES
t	TRIGGER
/ ***	この権限を付与したロール

pgdb01=# \z
                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 public | t1   | table | postgres=arwdDxt/postgres+|                   | 
        |      |       | pguser=arwdDxt/postgres   |                   | 
(1 row)

pgdb01=#