Masa's blog(08-14[長年日記])

2022年08月14日 PostgreSQL-14.4 Windows11Pro npgsql.3.1.10 and Tips

_ pg_env.batの修正

PATHに「"」が含まれていると正しく動作しなかったので、「"」を削除する。

REM @SET PATH="C:\Users\USER\Desktop\PostgreSQL\14\bin";%PATH%
@SET PATH=C:\Users\USER\Desktop\PostgreSQL\14\bin;%PATH%

_ エンコーディングの設定

@SET PGCLIENTENCODING=UTF8

_ パスワードの設定

事前に設定しておくと認証時にパスワードを入力する必要がなくなる。

@SET PGPASSWORD=パスワード

_ DB作成(例)

> createdb.exe -E UTF8 testdb

_ テーブル作成(例)(psql.exe)

psql.exe -U postgres testdb
testdb=# create table tb_sample1 (
testdb=#	id integer primary key,
testdb=#	name varchar(50),
testdb=#	job varchar(50)
testdb=# };

_ psql.exe利用例

出力先をoutput.csvに設定、文字揃えをオフにし、区切り文字をタブに設定、rowのみ出力、tb_sample1テーブルの全てを出力して、出力先を元に戻す。

testdb=# \o output.csv
testdb=# \a
testdb=# \f '\t'
testdb=# \t on
testdb=# select * from tb_sample1;
testdb=# \o

-Aで文字揃えをオフに、-Fで区切り文字を「カンマ」に指定。

> psql -d testdb -U postgres -c "select * from tb_sample1;" -A -F ',' > output.csv

-Aで文字揃えをオフに、-Fで区切り文字を「タブ」に指定(Powershell限定)。

> psql -d testdb -U postgres -c "select * from tb_sample1;" -A -F "`t" > output.csv

タブ区切
引用符(QUOTE)で囲む
NULL文字は空文字化
カラム名を表示

COPY tb_sample1 TO 'c:\temp\output.csv' WITH CSV DELIMITER E'\t' FORCE QUOTE * NULL AS '' HEADER;

_ npgsql.3.1.10.nupkg

pg_hba.confの認証methodを「trust」または「password」に設定する。

本来は、もっと新しいnpgsqlを使うべきだと思われるが、dllの依存関係をどうしても解決できなかった。結局、このバージョン(3.1.10)と認証method(trust or password)でしか接続できなかった...

_ C#によるPostgreSQLアクセス例

//
// Compile: c:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe /r:npgsql.dll npgsqltest.cs
//
using System;
using System.IO;
using System.Data;
using Npgsql;

public class Program{
	static void Main(string[] args){
		var connString = @"Server=127.0.0.1;Port=5432;UserId=postgres;Password=パスワード;Database=testdb";
		var conn = new NpgsqlConnection(connString);
		conn.Open();

		NpgsqlTransaction tran = conn.BeginTransaction();
		var cmd = new NpgsqlCommand(@"delete from tb_sample1", conn);
		cmd.ExecuteNonQuery();
		tran.Commit();

		tran = conn.BeginTransaction();
		cmd = new NpgsqlCommand(@"insert into tb_sample1 (id, name, job) values (:id, :name, :job)", conn);
		cmd.Parameters.AddWithValue("id", 1);
		cmd.Parameters.AddWithValue("name", "山田　太郎");
		cmd.Parameters.AddWithValue("job", "会社員");
		cmd.ExecuteNonQuery();
		tran.Commit();

		tran = conn.BeginTransaction();
		cmd.Parameters.Remove("id");
		cmd.Parameters.Remove("name");
		cmd.Parameters.Remove("job");
		cmd.Parameters.AddWithValue("id", 2);
		cmd.Parameters.AddWithValue("name", "辻󠄀（一点しんにょうつじ）　ＩＶＳ");
		cmd.Parameters.AddWithValue("job", "会社員");
		cmd.ExecuteNonQuery();
		tran.Commit();

		tran = conn.BeginTransaction();
		cmd.Parameters.Remove("id");
		cmd.Parameters.Remove("name");
		cmd.Parameters.Remove("job");
		cmd.Parameters.AddWithValue("id", 3);
		cmd.Parameters.AddWithValue("name", "𠮷（土よし）　サロゲート");
		cmd.Parameters.AddWithValue("job", "会社員");
		cmd.ExecuteNonQuery();
		tran.Commit();

		tran = conn.BeginTransaction();
		cmd.Parameters.Remove("id");
		cmd.Parameters.Remove("name");
		cmd.Parameters.Remove("job");
		cmd.Parameters.AddWithValue("id", 4);
		cmd.Parameters.AddWithValue("name", "（\uE000）　外字");
		cmd.Parameters.AddWithValue("job", "会社員");
		cmd.ExecuteNonQuery();
		tran.Commit();
//
// わざと重複キーでインサートして、どんなエラーメッセージが出るか観察してみる...
//
		tran = conn.BeginTransaction();
		cmd.Parameters.Remove("id");
		cmd.Parameters.Remove("name");
		cmd.Parameters.Remove("job");
		cmd.Parameters.AddWithValue("id", 4);
		cmd.Parameters.AddWithValue("name", "（\uE000）　外字");
		cmd.Parameters.AddWithValue("job", "会社員");

		try{
	 		cmd.ExecuteNonQuery();
	 		tran.Commit();
		}catch (Exception e){
			Concole.WriteLine(e.Message);
			tran.Rollback();
		}

		cmd = new NpgsqlCommand(@"select * from tb_sample1", conn);
		var dataReader = cmd.ExecuteReader();
		while (dataReader.Read()) {
			Console.WriteLine("{0},{1},{2}", dataReader["id"], dataReader["name"], dataReader["job"]);
		}
		dataReader.Close();

		tran = conn.BeginTransaction();
		cmd = new NpgsqlCommand(@"update tb_sample1 set job = :job where id = :id", conn);
		cmd.Parameters.AddWithValue("id", 1);
		cmd.Parameters.AddWithValue("job", "無職");
		cmd.ExecuteNonQuery();

		cmd.Parameters.Remove("id");
		cmd.Parameters.Remove("job");
		cmd.Parameters.AddWithValue("id", 2);
		cmd.Parameters.AddWithValue("job", "無職");
		cmd.ExecuteNonQuery();

		cmd.Parameters.Remove("id");
		cmd.Parameters.Remove("job");
		cmd.Parameters.AddWithValue("id", 3);
		cmd.Parameters.AddWithValue("job", "無職");
		cmd.ExecuteNonQuery();

		cmd.Parameters.Remove("id");
		cmd.Parameters.Remove("job");
		cmd.Parameters.AddWithValue("id", 4);
		cmd.Parameters.AddWithValue("job", "無職");
		cmd.ExecuteNonQuery();

		cmd = new NpgsqlCommand(@"select * from tb_sample1", conn);
		dataReader = cmd.ExecuteReader();
		while (dataReader.Read()) {
			Console.WriteLine("{0},{1},{2}", dataReader["id"], dataReader["name"], dataReader["job"]);
		}
		dataReader.Close();
		tran.Rollback();

		cmd = new NpgsqlCommand(@"select * from tb_sample1", conn);
		dataReader = cmd.ExecuteReader();
		while (dataReader.Read()) {
			Console.WriteLine("{0},{1},{2}", dataReader["id"], dataReader["name"], dataReader["job"]);
		}
		dataReader.Close();

		conn.Close();
	}
}

_ エラーメッセージ

二重キー: "0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "23505: 重複したキー値は一意性制約"tb_sample1_pkey"違反となります"
デッドロック: "0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "40P01: デッドロックを検出しました"

_ PowershellによるPostgreSQLアクセス例

[void][reflection.assembly]::LoadFrom("C:\Users\USER\Desktop\PostgreSQL\Npgsql.dll")
$ConnectionString = "Host=127.0.0.1;Username=postgres;Password=パスワード;Database=testdb"
$conn = New-Object NpgSql.NpgsqlConnection($ConnectionString)
$conn.Open()

$tran = $conn.BeginTransaction()
$command = New-Object NpgSql.NpgSqlCommand("delete from tb_sample1", $conn)
$effected_row_count = $command.ExecuteNonQuery();
$tran.Commit()

$tran = $conn.BeginTransaction()
$command = New-Object NpgSql.NpgSqlCommand("insert into tb_sample1 (id, name, job) values (:id, :name, :job)", $conn)
[void]$command.Parameters.AddWithValue("id", 1)
[void]$command.Parameters.AddWithValue("name", "山田　太郎")
[void]$command.Parameters.AddWithValue("job", "会社員")
$effected_row_count = $command.ExecuteNonQuery();
$tran.Commit()

$tran = $conn.BeginTransaction()
[void]$command.Parameters.Remove("id")
[void]$command.Parameters.Remove("name")
[void]$command.Parameters.Remove("job")
[void]$command.Parameters.AddWithValue("id", 2)
[void]$command.Parameters.AddWithValue("name", "辻󠄀（一点しんにょうつじ）　ＩＶＳ")
[void]$command.Parameters.AddWithValue("job", "会社員")
$effected_row_count = $command.ExecuteNonQuery();
$tran.Commit()

$tran = $conn.BeginTransaction()
[void]$command.Parameters.Remove("id")
[void]$command.Parameters.Remove("name")
[void]$command.Parameters.Remove("job")
[void]$command.Parameters.AddWithValue("id", 3)
[void]$command.Parameters.AddWithValue("name", "𠮷（土よし）　サロゲート")
[void]$command.Parameters.AddWithValue("job", "会社員")
$effected_row_count = $command.ExecuteNonQuery();
$tran.Commit()

$tran = $conn.BeginTransaction()
[void]$command.Parameters.Remove("id")
[void]$command.Parameters.Remove("name")
[void]$command.Parameters.Remove("job")
[void]$command.Parameters.AddWithValue("id", 4)
[void]$command.Parameters.AddWithValue("name", "（\uE000）　外字")
[void]$command.Parameters.AddWithValue("job", "会社員")
$effected_row_count = $command.ExecuteNonQuery();
$tran.Commit()

#
# わざと重複キーでインサートして、どんなエラーメッセージが出るか観察してみる...
#
$tran = $conn.BeginTransaction()
[void]$command.Parameters.Remove("id")
[void]$command.Parameters.Remove("name")
[void]$command.Parameters.Remove("job")
[void]$command.Parameters.AddWithValue("id", 4)
[void]$command.Parameters.AddWithValue("name", "（\uE000）　外字")
[void]$command.Parameters.AddWithValue("job", "会社員")
try{
	$effected_row_count = $command.ExecuteNonQuery();
	$tran.Commit()
}catch{
	write-output $_.Exception.Message
#	"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "23505: 重複したキー値は一意性制約"tb_sample1_pkey"違反となります"
	$tran.Rollback()
}

$command = New-Object NpgSql.NpgSqlCommand("select * from tb_sample1 order by id", $conn)
$datareader = $command.ExecuteReader()
while ($datareader.Read()){
	write-output($datareader["id"].tostring() + "," + $datareader["name"] + "," + $datareader["job"])
}
$datareader.Close()

$tran = $conn.BeginTransaction()
$command = New-Object NpgSql.NpgSqlCommand("update tb_sample1 set job = :job where id = :id", $conn)
[void]$command.Parameters.AddWithValue("id", 1)
[void]$command.Parameters.AddWithValue("job", "無職")
$effected_row_count = $command.ExecuteNonQuery();

[void]$command.Parameters.Remove("id")
[void]$command.Parameters.Remove("job")
[void]$command.Parameters.AddWithValue("id", 2)
[void]$command.Parameters.AddWithValue("job", "無職")
$effected_row_count = $command.ExecuteNonQuery();

[void]$command.Parameters.Remove("id")
[void]$command.Parameters.Remove("job")
[void]$command.Parameters.AddWithValue("id", 3)
[void]$command.Parameters.AddWithValue("job", "無職")
$effected_row_count = $command.ExecuteNonQuery();

[void]$command.Parameters.Remove("id")
[void]$command.Parameters.Remove("job")
[void]$command.Parameters.AddWithValue("id", 4)
[void]$command.Parameters.AddWithValue("job", "無職")
$effected_row_count = $command.ExecuteNonQuery();

$command = New-Object NpgSql.NpgSqlCommand("select * from tb_sample1 order by id", $conn)
$datareader = $command.ExecuteReader()
while ($datareader.Read()){
	write-output($datareader["id"].tostring() + "," + $datareader["name"] + "," + $datareader["job"])
}
$datareader.Close()
$tran.Rollback()

$command = New-Object NpgSql.NpgSqlCommand("select * from tb_sample1 order by id", $conn)
$datareader = $command.ExecuteReader()
while ($datareader.Read()){
	write-output($datareader["id"].tostring() + "," + $datareader["name"] + "," + $datareader["job"])
}
$datareader.Close()

$conn.Close()

_ PowershellでUTF8を使う設定

# 標準出力（パイプ）への出力時のエンコーディングをバックアップ
$oe = $OutputEncoding
# 標準出力（画面）への出力時のエンコーディングをバックアップ
$scoe = [System.Console]::OutputEncoding

# 標準出力（パイプ）、標準出力（画面）への出力時のエンコーディングをUTF-8BOM無しに設定
$OutputEncoding = [System.Console]::OutputEncoding = New-Object System.Text.UTF8Encoding $false
# 標準出力（パイプ）、標準出力（画面）への出力時のエンコーディングをUTF-16、LE、BOM有りに設定
#$OutputEncoding = [System.Console]::OutputEncoding = New-Object System.Text.UnicodeEncoding $false,$true
# 標準出力（パイプ）、標準出力（画面）への出力時のエンコーディングをShift-JISに設定
#$OutputEncoding = [System.Console]::OutputEncoding = [Text.Encoding]::GetEncoding("Shift-JIS")

# ... UTF-8BOMなしデータを処理 ...
Hogge.exe | fuga.exe | foo.exe | bar.exe ...

# 標準出力（パイプ）への出力時のエンコーディングを元に戻す
$OutputEncoding = $oe
# 標準出力（画面）への出力時のエンコーディングを元に戻す
[System.Console]::OutputEncoding = $scoe

_ dotnet and npgsql.6.0.6

結局、pg_hba.confの認証methodを「md5」または「scram-sha-256」にする為にはdotnet6環境でのビルドが必要だった。

ソースディレクトリの準備

mkdir npgsqltest
cd npgsqltest
dotnet new console

npgsql.6.0.6のパッケージ追加(インターネット接続可能な場合)

dotnet add package npgsql

npgsql.6.0.6のパッケージ追加(インターネット接続不可能な場合)

mkdir packages
copy anywhere\npgsql.6.0.6.nupkg .\packages\
copy anywhere\ystem.runtime.compilerservices.unsafe.nupkg .\packages\
dotnet add package npgsql -s .\packages

ソースの編集

notepad Program.cs

ビルド

dotnet build

テスト実行

dotnet run

公開（デバッグ）

dotnet publish
bin\Debug\net6.0\publish\npgsqltest.exe

公開（本番）

dotnet publish -c Release
bin\Release\net6.0\publish\npgsqltest.exe

公開（本番、全部入り）

dotnet publish -c Release --self-contained true -r win-x64
bin\Release\net6.0\win-x64\publish\npgsqltest.exe

公開（本番、全部入り、単一exe）

dotnet publish -c Release --self-contained -p:PublicSingleFile=true -p:PublishTrimmed=true -r win-x64
bin\Release\net6.0\win-x64\publish\npgsqltest.exe

[ツッコミを入れる]

Masa's blog