postgresql よく使うコマンドや小ネタ　備忘録

# どうでもいい話~psqlでは；がいらない~
```
psql -U postgres -c 'select * from sample' tm_agg_data
psql -U postgres -c 'select * from sample;' tm_agg_data
```
どちらでもsampleテーブルの結果が返ってくる。
PostgreSQLのターミナルにそのまま流しているのであれば上のほうは実行結果が表示されないはず。（下の行に連続して記述できるため）
「；」がなくてもコマンドを認識できるのか。

# setコマンド
・実行時のパラメータを変更するもの。セッション(ユーザーで接続している間)内かトランザクションかは選ぶことができる。「set local」 or 「set session」。
・トランザクションがabortされるとset効力が失う。
・PostgreSQL ver8.0~8.2までset localの効果は以前のセーブポイントを開放することだった。
使用例）
```:set使用前
postgres=# show search_path;
   search_path   
-----------------
 "$user", public
```
```:seach_path変更
postgres=# create schema t_schena;
CREATE SCHEMA
postgres=# set search_path to 't_schema';
SET
```
```:set使用後
postgres=# show search_path;
 search_path 
-------------
 t_schema
```
```:sessionを切って、再確認
postgres=# \q　(session切断)
root@9dc74f27d78a:~# psql -U postgres
postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)
```
もとに戻ってました。

# テーブルの移動・型確認
```
ALTER TABLE 対象のテーブル名 SET SCHEMA 移動先のスキーマ名
ALTER TABLE テーブル名 ALTER COLUMN 列名 [SET DATA] TYPE 新しい型;
\d (テーブル名)
```
mysqlの場合はshow columns from <tabname>;でテーブルの列の型確認できる。

権限系
```
ALTER USER myuser NOSUPERUSER;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE <tablename> TO <username>;
```

設定系
```
create user superuser createdb <rolename>;
ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';
```
# 明示的ロック
## テーブルロック
ロックモードの名前にrowとあっても表全体を指す。結局どのモードと競合するか問題。行レベルロックは別に存在する。

テーブルロックレベル(PostgreSQL 9.4.5文書より)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3661566/78c5608f-b043-d6b3-4695-5b7f69b794de.png)

MAX:Access exclusiveモード
その他のトランザクションを完全にロック。ロック界の最強。
DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULLコマンドによって獲得される。

## 行レベルロック(PostgreSQL 9.4.5文書より)
行レベルロックは、同じ行に対する書き込みとロックだけをブロック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3661566/9f9ec256-5e07-a5f5-17c1-a516d2c883e8.png)

MAX：FOR UPDATE
現在のトランザクションが終わるまでは他のトランザクションがロック、変更、削除ができなくなる。

# roleとuserの違い
```
postgres=# create role hida with login;
postgres=# create user hida;
```
create roleの場合、デフォルトでcannot login になるのをふせぐ。
もしくは、create userをつかう。
ネットに出てる話はこの程度の情報にとどまる。「緑本OSS-DB Silver ver3.0対応」をみると、解説は以下の通り。（P76）

:::note info
postgresqlにもOSと同様にグループの概念がある。ユーザーとグループはPostgreSQL 8.1以前は異なるものだった。現在ではロールと呼ばれる概念に統合されている。PostgreSQLでは、このロールをユーザー、グループ、またはその両方として使われている。
:::
create roleとuserがわざわざ２通り用意されているのは過去の遺物が残っているともいえる。


# information_schema(情報スキーマ),pg_catalog(システムカタログ)
chatgptにきいてみた

:::note info
<b>情報スキーマ</b>→SQL標準に準拠。異なるデータベースシステム間で互換性あり
<b>システムカタログ</b>→PostgreSQL特有。より詳細な情報を提供するため、互換性なし。
どちらもデータベースのメタデータを参照するために使用されるが、情報スキーマはより一般的な情報を、システムカタログはPostgreSQL固有の詳細な情報を提供する。
:::


# 情報関数についての小ネタ
ふと情報関数の中でかっこつきとそうでないのがあったので気になったので調べてみた。
以下の２つのコマンドは何が違うだろう。
```
select version();
select current_timestamp;
```
postgresql 15.4文書によると、
https://www.postgresql.jp/docs/15/functions-info.html

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3661566/f05e079f-a3e6-82b6-5a29-b1f6cfc82735.png)

とあった。となると上の２つのコマンドの例ではpostgresqlの制作者がversion()よりもcurrent_timestampやcurrent_user,session_userの情報を返すことのほうが重要だとみなしたことになる。
これは学びになった。

# pg_hba.confについて
クライアント認証に関する設定を記述するファイル
```
type|database|user|address|mathod
host|all|all|127.0.0.1/32|md5
```
上から順に確認作業を始める。一致したところで認証作業を終え、接続の許可をする。ようするに先勝ち。読み込みに関してはいつも後勝ちで覚えてしまっていたので印象に残った。