接続
windows環境、Linux環境共に以下のようにパスワードを問われてきます。セキュリティの問題はありますが、Oracleのsqlplusのようにパスワードをパラメータで指定できない事少し面倒です、、
psql -h vm022 -p 5432 -U sooni -d myposdb
C:\Users\atsuc>psql -h 192.168.3.22 -p 5432 -U sooni -d myposdb
Password for user sooni:
psql (13.5, server 13.6)
Type "help" for help.
myposdb=>こんな時の対策が以下の通り環境によって異なります
Linux環境
環境変数（ PGPASSWORD ）にパスワードを記載するのがお気軽ですが、接続ユーザが変わると合わせて変更しなくてはならない点不便です。
export PGPASSWORD=soopass
これに対し、Linuxユーザのホームディレクトリに.pgpass を作成し中に接続情報を記載する方法が便利です。
[sooni@vm022 ~]$ vi .pgpass
[sooni@vm022 ~]$ chmod 600 .pgpass ←セキュリティ上600にしないといけません
[sooni@vm022 ~]$ cat .pgpass
vm022:5432:myposdb:sooni:soopass
vm022:5432:prdb:sooni:soopass
vm022:5432:prdb:postgres:postgres
[sooni@vm022 ~]$ psql -h vm022 -p 5432 -U postgres -d prdb
psql (13.6)
"help"でヘルプを表示します。
prdb=#
Windows環境
%APPDATA%\postgresqlの配下にpgpass.confを作成し
サーバ名:ポートNo.:データベース名:ユーザ名:パスワード
を記載します。
当方環境では以下の通り
dos窓
cd %APPDATA%\postgresql
PowerShell
-- 環境変数にパスワードを設定する場合
$env:PGPASSWORD = 'soopass'cd $env:APPDATA\postgresql
以下はPowerShellでの確認例
PS G:\test> cd $env:APPDATA\postgresql
PS C:\Users\sooni\AppData\Roaming\postgresql> ls
ディレクトリ: C:\Users\sooni\AppData\Roaming\postgresql
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 2022/04/17 20:59 98 pgpass.conf
PS C:\Users\sooni\AppData\Roaming\postgresql> cat .\pgpass.conf
vm022:5432:myposdb:sooni:soopass
vm022:5432:prdb:sooni:soopass
vm022:5432:prdb:postgres:postgres
PS C:\Users\sooni\AppData\Roaming\postgresql>
PS C:\Users\atsuc\AppData\Roaming\postgresql> psql -h vm022 -p 5432 -U sooni -d myposdb
psql (13.5, server 13.6)
Type "help" for help.
myposdb=# \q
## 以下のような記載でも接続できます
PS C:\Users\atsuc\AppData\Roaming\postgresql> psql postgresql://vm022:5432/myposdb?user=sooni
psql (13.5, server 13.6)
Type "help" for help.
myposdb=#外部ファイルに書いたSQLを実行する
-f オプションで指定したファイルに記載したSQLを実行します。この時意識しないといけないのは、外部SQLファイルの文字コードと、client_encodingが整合しているかという事です。Windows環境であればデフォルトのclient_encodingはSJISなので外部SQLファイルもShift-JISで記載してあるとそのまま利用できますが、utf-8等別の文字コードが使われている場合、次のようにメタコマンドを打ち込む必要あります。
-- psqlで接続する前に環境変数へセットする場合
SET client_encoding = 'UTF8';
-- psqlで接続する前に環境変数へセットする場合(Powershell)
$env:PGCLIENTENCODING = 'UTF8'
-- 既にログインした後の場合
\encoding UTF8
-- 何れも元に戻すには SJISpsql -U sooni -h vm022 -d myposdb -f .\sample01.sql
実行例
PS C:\oha-yo\powershell> psql -U sooni -h vm022 -d myposdb -f .\sample01.sql
now
------------------------------
2022-11-23 01:32:13.12025+09
(1 row)
pg_get_functiondef
----------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION sooni.destination() +
RETURNS TABLE(item_no integer, item_name text, item_value text) +
LANGUAGE plpgsql +
AS $function$ +
begin +
raise info '接続先を確認するサンプルファンクション'; +
return query +
+
select 1 as item_no,'current_database' as item_name,cast(current_database() as text) as item_value +
union select 2 as item_no,'version' as item_name,substring(version() from 'PostgreSQL [0-9|.]*') as item_value+
union select 3 as item_no,'inet_server_addr' as item_name,cast(inet_server_addr() as text) as item_value +
union select 4 as item_no,'inet_server_port' as item_name,cast(inet_server_port() as text) as item_value +
union select 5 as item_no,'current_user' as item_name,cast(current_user as text) as item_value +
+
union select 6 as item_no,'current_schema' as item_name,cast(current_schema() as text) as item_value +
union select 7 as item_no,'inet_client_addr' as item_name,cast(inet_client_addr() as text) as item_value +
order by item_no +
; +
+
END; +
$function$ +
(1 row)
PS C:\oha-yo\powershell>メタコマンド
\timing 経過時間を表示する
myposdb=# \timing
タイミングは on です。
myposdb=# select count(*) from temp_address;
count
--------
150034
(1 行)
時間: 29.651 ミリ秒
myposdb=# \timing
タイミングは off です。
myposdb=#\d テーブル、view、インデックス、シーケンス等各種オブジェクトの情報出力
何かと出番の多い重宝するやつです
myposdb=# \d logging
Table "sooni.logging"
Column | Type | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+----------------------------------------
logid | integer | | not null | nextval('logging_logid_seq'::regclass)
trace_no | integer | | |
step_no | integer | | |
step_message | character varying(80) | | |
creation_date | timestamp without time zone | | |
myposdb=# \d address_gaiku
View "sooni.address_gaiku"
Column | Type | Collation | Nullable | Default
----------------------+-----------------------+-----------+----------+---------
lno | numeric | | |
prefectures_name | character varying(32) | | |
city_name | character varying(64) | | |
oaza_town_chome_name | character varying(32) | | |
block_number | character varying(10) | | |
myposdb=# \d okinawa_city_idx
Index "sooni.okinawa_city_idx"
Column | Type | Key? | Definition
--------+-----------------------+------+------------
city | character varying(40) | yes | city
Partition of: addres_list_idx02
btree, for table "sooni.okinawa"
myposdb=# \d logging_logid_seq
Sequence "sooni.logging_logid_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
integer | 1 | 1 | 2147483647 | 1 | no | 1
Owned by: sooni.logging.logid
myposdb=#
\x select表示結果を縦にする
myposdb=# \x
Expanded display is on.
myposdb=# select * from information_schema.sequences where sequence_name ='logging_logid_seq';
-[ RECORD 1 ]-----------+------------------
sequence_catalog | myposdb
sequence_schema | sooni
sequence_name | logging_logid_seq
data_type | integer
numeric_precision | 32
numeric_precision_radix | 2
numeric_scale | 0
start_value | 1
minimum_value | 1
maximum_value | 2147483647
increment | 1
cycle_option | NO
\pset null nullを任意の表示へ切り替える
\pset null '(null)'\g 再実行
myposdb=# select * from ex1;
lno | first_name | last_name
-----+------------+-----------
1 | 拓朗 | 西山
2 | (null) | 東川
3 | 優美 | (null)
4 | (null) | (null)
(4 rows)
myposdb=# \g
lno | first_name | last_name
-----+------------+-----------
1 | 拓朗 | 西山
2 | (null) | 東川
3 | 優美 | (null)
4 | (null) | (null)
(4 rows)
myposdb=#set コマンド
よく使うsetコマンドを以下列挙していきます
client_min_messages クライアントへ送るログメッセージレベルを設定する
raise debugと記載していても通常は出力されませんが、以下のように設定すると出力されるようになります。
set client_min_messages = 'debug1';
-- 現在の値を確認するには showコマンドを使います
--
esdb=# show client_min_messages ;
client_min_messages
---------------------
debug1
(1 行)
-- デフォルトはnoticeなので戻しておきます
--
esdb=# set client_min_messages = 'notice';
SET
esdb=# show client_min_messages ;
client_min_messages
---------------------
notice
(1 行)
esdb=#元に戻す
set client_min_messages to default;
