PostgreSQL

PostgreSQL Tips

insert into values で一度に複数レコードinsertできる

これはOracleに慣れ親しんでいたために便利と感じたものです。PostgreSQLだけでなくMySQLでもこの構文は有効です。よって「Oracleがイケてない」って事ですね、、Oracleではinsert allを使ってこれに代わる事を実現するのですが、PostgreSQLやMySQLで使えるこちらの構文のほうが便利です。Oracleを少しフォローするとinsert allの場合、同時に複数テーブルに対するINSERTも可能になっています。

以下へ移動しました。

何かと便利な配列処理

内容が大きくなったので以下へ移動しました。

convert_to()関数で文字列をutf-8符号化文字で出力(ダンプ表示)する

文字列を16進表記で確認する場合に使います。1つ注意点あります。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'
;

接続先確認

内容が大きくなったので以下へ移動しました。

スポンサーリンク