PostgreSQL

PostgreSQL psqlコマンド

接続

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
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等別の文字コードが使われている場合、次のようにメタコマンドを打ち込む必要あります。
\encoding UTF8

psql -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=#
スポンサーリンク