insert into values で一度に複数レコードinsertできる
これはOracleに慣れ親しんでいたために便利と感じたものです。PostgreSQLだけでなくMySQLでもこの構文は有効です。よって「Oracleがイケてない」って事ですね、、Oracleではinsert allを使ってこれに代わる事を実現するのですが、PostgreSQLやMySQLで使えるこちらの構文のほうが便利です。Oracleを少しフォローするとinsert allの場合、同時に複数テーブルに対するINSERTも可能になっています。
以下へ移動しました。
何かと便利な配列処理
内容が大きくなったので以下へ移動しました。
convert_to()関数で文字列をutf-8符号化文字で出力（ダンプ表示）する
文字列を16進表記で確認する場合に使います。１つ注意点あります。pgAdmin4のクエリーツールでこのファンクションを使う場合、さらにencode()関数を使う事になります。これはconvert_to()関数の戻り型がbytea（バイナリ型）になっている事によるものです。
convert_to()関数利用例
select convert_to('AB12','utf-8')
;
psqlコマンドからの出力例
myposdb=# select convert_to('AB12','utf-8');
convert_to
------------
\x41423132
(1 row)
myposdb=#convert_to()関数の戻り値をencode()関数で16進表記へ
select encode(convert_to('AB12','utf-8'), 'hex')
;
encode()関数を使った出力例
-- psqlでの出力例ですが、上記と異なり今度は「\x」が除かれています
-- 正確には psqlではbyteaタイプを出力する際「\xを付与する表記」に
-- しているのだと思います。
-- 見方を変えればpgAdmin4が本来の動作で、psqlが親切機能としてバイナリ
-- 型のbyteaの場合頭に「\x」を付けて表示してくれているとも言えます。
--
myposdb=# select encode(convert_to('AB12','utf-8'), 'hex')
myposdb-# ;
encode
----------
41423132
(1 行)
myposdb=#pg_stat_activity セッション一覧
現在のトランザクションIDの取得
Oracleではv$transaction.xidがこれに近しいですが、oracleではselectしてもxidは更新されません。
select txid_current();
-- PostgreSQLはデフォルトでauto commit on になているためSQL実行の度にcommitが入ります
-- そのためtxid_current()を実行する都度返却されるトランザクションIDは更新されていきます。
myposdb=# select txid_current();
txid_current
--------------
835
(1 row)
myposdb=# select txid_current();
txid_current
--------------
836
(1 row)
-- トランザクションを開始するとトランザクションIDは維持されます
myposdb=# begin;
BEGIN
myposdb=*# select txid_current();
txid_current
--------------
837
(1 row)
myposdb=*# select txid_current();
txid_current
--------------
837
(1 row)
-- トランザクションを終えるとまた更新されます。
myposdb=*# end;
COMMIT
myposdb=# select txid_current();
txid_current
--------------
838
(1 row)
myposdb=#pg_stat_activity セッション一覧
select pid,state,datname,application_name,usename ,to_char(backend_start,'YYYY-MM-DD HH24:MI:SS') backend_start from pg_stat_activity where client_addr is not null order by backend_start;
myposdb=> select pid,state,datname,application_name,usename
myposdb-> ,to_char(backend_start,'YYYY-MM-DD HH24:MI:SS') backend_start
myposdb-> from pg_stat_activity where client_addr is not null
myposdb-> order by backend_start;
pid | state | datname | application_name | usename | backend_start
------+---------------------+---------+--------------------------+---------+---------------------
7322 | active | myposdb | pgAdmin 4 - DB:myposdb | sooni | 2022-04-17 19:37:03
7388 | active | myposdb | psql | sooni | 2022-04-17 19:42:47
7403 | idle | prdb | postgres_fdw | sooni | 2022-04-17 19:43:50
7440 | idle in transaction | myposdb | pgAdmin 4 - CONN:8799663 | sooni | 2022-04-17 19:46:17
(4 行)
myposdb=>サービス起動、停止、再起動
systemctl restart postgresql-13.service
起動スクリプト
[root@vm022 ~]# ls -lt /usr/lib/systemd/system/postgresql-13.service
-rw-r--r--. 1 root root 1764 2月 9 17:01 /usr/lib/systemd/system/postgresql-13.service
サービス停止
[root@vm022 ~]# systemctl stop postgresql-13.service
サービス開始
[root@vm022 ~]# systemctl start postgresql-13.service
サービス状態確認
[root@vm022 ~]# systemctl list-units --type=service | grep postgres
postgresql-13.service loaded active running PostgreSQL 13 database server
[root@vm022 ~]#ユーザ一覧
内容が大きくなったので以下へ移動しました。
ユーザ一作成とパスワード変更
基本的にはpostgresユーザで実行しますが、「createrole権限」を持つユーザであれば実行できます
-- ユーザ作成 create user online_user password 'online_pass' ;
-- パスワード変更 alter user online_user with password 'new_pass' ;
スキーマ一覧
内容が大きくなったので以下へ移動しました。
スキーマ作成
内容が大きくなったので以下へ移動しました。
スキーマ削除
一旦スキーマ作成した後スキーマ配下にテーブル等を削除している場合はcascadeオプションが必要になります。
drop schema prjschema cascade ;
スキーマ内テーブル一覧
select schemaname, tablename, tableowner from pg_tables t where t.schemaname='sooni' ;
myposdb=> select schemaname, tablename, tableowner from pg_tables t where t.schemaname='sooni'
myposdb-> ;
schemaname | tablename | tableowner
------------+-----------+------------
sooni | ex01 | sooni
sooni | ex10 | sooni
sooni | intable | sooni
(3 rows)
myposdb=>テーブルのカラム一覧
内容が大きくなったので以下へ移動しました。
オブジェクト一覧（OracleではDBA_OBJECTSを参照したい時利用）
内容が大きくなったので以下へ移動しました。
各種データサイズ確認
データベースサイズ確認
SELECT pg_size_pretty(pg_database_size('database_name')) AS db_size;
スキーマサイズ確認
SELECT table_name, pg_size_pretty(pg_total_relation_size(table_name)) AS table_size FROM information_schema.tables WHERE table_schema = 'schema_name';
テーブルサイズ確認
SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS table_size;
トランザクション開始
BEGIN transaction ;
バージョン確認
select version() ;
文字コード確認
select character_set_name from information_schema.character_sets ;
パスワード変更
ALTER USER sooni WITH PASSWORD 'soopass' ;
接続先確認
内容が大きくなったので以下へ移動しました。
