はじめに
PostgreSQLの基本的なコマンドをまとめる。内容はPostgreSQLへの接続から、CopyコマンドでのCSVのインポートまでを対象とする。環境は下記の通り。
> wmic os get caption Caption Microsoft Windows 10 Pro > psql -V psql (PostgreSQL) 13.0
PostgreSQLへの接続
PostgreSQLへの接続を行うには下記のコマンドで実行する。psqlコマンドを使えるようにするためには、C:\Program Files\PostgreSQL\13\binをシステム環境変数を設定する必要あり。環境によってパスは変更される可能性があるので注意。PostgreSQLをインストールした直後はスーパーユーザーpostgresロールしか作成されていない。ロールは一般的にはユーザーにあたるもの。postgresデータベースも自動で作成されている。
psql -h [host] -p [port] -U [roll] -d [db name]
ではローカルホストのPostgreSQLに接続してみる。パスワードはインストール時に設定したもの。PostgreSQLとコネクションを切断する場合は¥q。
> psql -h 127.0.0.1 -p 5432 -U postgres -d postgres ユーザ postgres のパスワード: psql (13.0) "help"でヘルプを表示します。 postgres=#
データベースの作成
データベースを作成する場合はCREATE DATABASE [db name]で行う。
postgres=# create database mydb; CREATE DATABASE
\lでデータベースの一覧を確認する。mydbが確認できる。
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+--------------------+--------------------+-----------------------
mydb | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
postgres | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
template0 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行)
ownerでデータベースの所有者を指定し、templateでデータベースを作成する際のテンプレートを指定。テンプレートに指定したデータベース設定を引き継ぐことが可能。新しいデータベースを作成するので、template0を指定。照合順序(lc_collate)のCは、文字のソートをコード順に行う指定で、lc_typeは、データベースで使用する文字の大文字、小文字、数字の指定で、特別な処理は行わないCを指定。
postgres=# create database mydb2 owner = postgres template = template0 encoding = 'UTF8' lc_collate = 'C' lc_ctype = 'C';
CREATE DATABASE
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+--------------------+--------------------+-----------------------
mydb | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
mydb2 | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
template0 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 行)
作成したデータベースに接続するには\c [dbname]コマンドを利用する。
postgres=# ¥c mydb データベース"mydb"にユーザ"postgres"として接続しました。 mydb=#
接続するデータベースが予めわかっているのであれば、接続時に-dで指定する。接続しているデータベースはプロンプトで表示される。またはselect current_database();で確認できる。
> psql -h 127.0.0.1 -p 5432 -U postgres -d mydb ユーザ postgres のパスワード: psql (13.0) "help"でヘルプを表示します。 mydb=# mydb=# select current_database(); current_database ------------------ mydb (1 行)
データベースを削除する場合はdrop database [db name];。
mydb=# drop database mydb2;
DROP DATABASE
mydb=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+--------------------+--------------------+-----------------------
mydb | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
postgres | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
template0 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行)
スキーマ
スキーマがあるのでMySQLとは少し違う印象をもってしまう。PostgreSQLをインストールするとtemplate0とtemplate1 、postgresの3つのデータベースが作成される。また、データベースの集合をデータベースクラスタと呼ぶ。現状、これに加えてmydbがあるので4つのデータベースがある。
PostgreSQLでもデータはテーブルに格納される。テーブルはもちろん複数作成することが可能で、テーブルをまとめているのがデータベース…のはずが、PostgreSQLには「スキーマ」と呼ばれるものがある。スキーマは、データベースに作成されるテーブルや関数などのオブジェクトをグループ化するもので、スキーマが異なれば、同じテーブル名でテーブルを作成することができる。データベースを作成すると、publicスキーマが作成される。このスキーマは、デフォルトですべてのロールにアクセス権限とcreate権限が付与されており、publicスキーマの中にどのロールでもテーブルを作成することができる公共施設のような扱いのスキーマ。もちろんpublic以外にも自由にスキーマ作成することはできる。データベースクラスタのイメージは下記の通り。
DATABASE CLUSTER ├─mydb │ ├─myschema │ │ ├─tableA │ │ ├─tableB │ │ └─tableC │ └─public │ └─public_table ├─postgres │ └─public ├─template └─template0
\dnコマンドでデータベース内のスキーマーを確認できる。
mydb=# ¥dn スキーマ一覧 名前 | 所有者 --------+---------- public | postgres (1 行)
create schema [schema name]でスキーマーを作成することができる。削除する際はdrop schema [schema name]コマンドを実行する。
mydb=# create schema myschema;
CREATE SCHEMA
mydb=# ¥dn
スキーマ一覧
名前 | 所有者
----------+----------
myschema | postgres
public | postgres
ロールの作成
新しいロールを作成するにはcreate rollコマンドを実行する。
mydb=# create role user01 with login password 'pass'; CREATE ROLE
\duでロールを確認する。
mydb=# ¥du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+--------------------------------------------------------------------------+--------------
postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
user01 | | {}
createdb、createroleを追加すると、そのロールに権限を付与できる。
mydb=# create role user02 with createdb createrole login password 'pass';
CREATE ROLE
mydb=# ¥du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+--------------------------------------------------------------------------+--------------
postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
user01 | | {}
user02 | ロール作成可, DB作成可 | {}
alter roleで後からロールに権限を付与できる。
mydb=# alter role user01 with createdb createrole;
ALTER ROLE
mydb=# ¥du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+--------------------------------------------------------------------------+--------------
postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
user01 | ロール作成可, DB作成可 | {}
user02 | ロール作成可, DB作成可 | {}
mydb=# alter role user01 with nocreatedb;
ALTER ROLE
mydb=# ¥du
ロール一覧
ロール名 | 属性 | 所属グループ
----------+--------------------------------------------------------------------------+--------------
postgres | スーパユーザ, ロール作成可, DB作成可, レプリケーション可, RLS のバイパス | {}
user01 | ロール作成可 | {}
user02 | ロール作成可, DB作成可 | {}
user01は不要なのでdrop role [name]で削除しておく。
grantコマンドでロールに権限を追加できる。まずは、user02にmydb内にスキーマを作成する権限を付与する。user02=C/postgresが追加されていることがわかる。
mydb=# grant create on database mydb to user02;
GRANT
mydb=# ¥l mydb
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
------+----------+------------------+--------------------+--------------------+-----------------------
mydb | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | user02=C/postgres
次に、user02にmydb内のmyschema内にテーブルを作成する権限を付与する。user02=C/postgresが追加されていることがわかる。
mydb=# grant create on schema myschema to user02;
GRANT
mydb=# ¥dn+
スキーマ一覧
名前 | 所有者 | アクセス権限 | 説明
----------+----------+----------------------+------------------------
myschema | postgres | postgres=UC/postgres+|
| | user02=C/postgres |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 行)
user02はmyschemaにアクセスする権限とセレクト権限がないので、先に付与しておく。アクセス権限を付与したので、これでuser02がmydb内のmyschema内にテーブルを作成する準備ができた。
mydb=# grant usage on schema myschema to user02; GRANT mydb=# grant select on myschema.test to user02; GRANT
user02でログインしなおしてテーブルを作成する。
> psql -h 127.0.0.1 -p 5432 -U user02 -d mydb ユーザ user02 のパスワード: psql (13.0) "help"でヘルプを表示します。 mydb=> create table myschema.test (id integer, name varchar(10)); CREATE TABLE mydb=> select * from myschema.test; id | name ----+------ (0 行)
他の権限は下記の通り。
| 権限 | 内容 |
|---|---|
| select | テーブルやビューへのselect権限 |
| insert | テーブルに新しいデータを追加する権限 |
| update | テーブルのデータを更新する権限 |
| delete | テーブルからデータを削除する権限 |
| truncate | テーブルのデータを空にする権限 |
| references | テーブルやカラムに対して外部キー制約を作成する権限 |
| trigger | テーブルでトリガを作成する権限 |
| create | スキーマやテーブルを作成する権限 |
| connect | 指定のデータベースに接続する権限 |
| temporary | データベース内に一時テーブルを作成する権限 |
| excute | 関数またはプロシージャ、演算子の使用を許可する権限 |
| usage | 対象がスキーマの場合、オブジェクトへのアクセスを許可する権限 |
| all privileges | 利用可能な権限をまとめて許可する権限 |
テーブル作成
まずは前回までの掃除をしておく。postgresデータベースにpostgresでログインして、データベースやスキーマー、ロールを削除する。
>psql -h 127.0.0.1 -p 5432 -U postgres -d postgres ユーザ postgres のパスワード: psql (13.0) "help"でヘルプを表示します。 postgres=# postgres=# drop database mydb; DROP DATABASE postgres=# drop role user02; DROP ROLE
ここではECデータの注文データを格納するデータベースecやテーブルpublic.orderを作成する。
postgres=# create database ec;
CREATE DATABASE
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+--------------------+--------------------+-----------------------
ec | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
postgres | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 |
template0 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | Japanese_Japan.932 | Japanese_Japan.932 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行)
ecに切り替えて、
postgres=# \c ec
データベース"ec"にユーザ"postgres"として接続しました。
ec=#
create table public.order (
Product varchar(20),
Cuid varchar(20),
Sales×Quantity real,
Category varchar(20),
Order_date date,
Order_id varchar(20),
Sub_category varchar(20),
Quantity integer,
Sales real
);
ec=# ¥dt
リレーション一覧
スキーマ | 名前 | タイプ | 所有者
----------+-------+----------+----------
public | order | テーブル | postgres
(1 行)
ec=# \d order
テーブル"public.order"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
-----------------+-----------------------+----------+---------------+------------
product | character varying(20) | | |
cuid | character varying(20) | | |
sales×quantity | real | | |
category | character varying(20) | | |
order_date | date | | |
order_id | character varying(20) | | |
sub_category | character varying(20) | | |
quantity | integer | | |
sales | real | | |
例えばインデックスは下記のように付与できる。
ec=# create index on public.order (Cuid); CREATE INDEX
あとはcopyコマンドでテーブルにデータをインサートする。
## こんなオプションの指定もできる ## \copy public.iris from 'C:\\Users\\<user>\\Desktop\\iris\\file.csv' (encoding 'utf8', format csv, header true); ec=# \copy public.order from 'C:\\Users\\<user>\\Desktop\\sample_order.csv' csv header encoding 'utf8'; COPY 9994 ec=# select count(1) from public.order; count ------- 9994 (1 行) ec=# select * from public.order limit 10; product | cuid | sales×quantity | category | order_date | order_id | sub_category | quantity | sales -------------+-----------+-----------------+-----------------+------------+----------------+--------------+----------+---------- Product0387 | X00000167 | 523.92 | Furniture | 2019-11-08 | CA-2019-152156 | Bookcases | 2 | 261.96 Product0832 | X00000167 | 2195.82 | Furniture | 2019-11-08 | CA-2019-152156 | Chairs | 3 | 731.94 Product1439 | X00000202 | 29.24 | Office Supplies | 2019-06-12 | CA-2019-138688 | Labels | 2 | 14.62 Product0368 | X00000688 | 4787.8877 | Furniture | 2018-10-11 | US-2018-108966 | Tables | 5 | 957.5775 Product0573 | X00000688 | 44.736 | Office Supplies | 2018-10-11 | US-2018-108966 | Storage | 2 | 22.368 Product0569 | X00000114 | 342.02 | Furniture | 2017-06-09 | CA-2017-115812 | Furnishings | 7 | 48.86 Product1136 | X00000114 | 29.12 | Office Supplies | 2017-06-09 | CA-2017-115812 | Art | 4 | 7.28 Product1098 | X00000114 | 5442.912 | Technology | 2017-06-09 | CA-2017-115812 | Phones | 6 | 907.152 Product0534 | X00000114 | 55.512 | Office Supplies | 2017-06-09 | CA-2017-115812 | Binders | 3 | 18.504 Product0295 | X00000114 | 574.5 | Office Supplies | 2017-06-09 | CA-2017-115812 | Appliances | 5 | 114.9 (10 行)
おまけ
| コマンド | 内容 |
|---|---|
| PostgreSQLに接続 | psql -h [host] -p [port] -U [roll] -d [db name] |
| PostgreSQLを切断 | \q |
| データベース一覧 | \l |
| データベース接続 | \c [dbname] |
| スキーマー確認 | \dn |
| ロールの確認 | \du |
| 権限確認 | \dp |
| テーブルの確認 | \dt |
| テーブル内容の確認 | \d [table name] |
| 拡張表示 | \x |
