PostgreSQL

読み方は、ポストグレス・キューエル。Ingresの次版(Post)としてPostgresとなった。QLはQuery Languageの略。

PostgreSQLは、追記型アーキテクチャを採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。
不要領域を再利用可能な状態にするには、VACUUMコマンドを使います。

PostgreSQLのお勉強

将来的には追記型アーキテクチャを捨てて、EnterpriseDBが率先して開発している「zHeap」の実装して他のデータベースのようにUNDOログをもつようにしたい。→ その後zHeapのメイン開発者が抜けて休止状態
データベースとして，オープンソースとして，コミュニティとして ―石井達夫氏が語るPostgreSQLの強さと課題

PostgreSQL OLDDBドライバーは、遅い上にPostgreSQLのnumeric型フィールド値がうまく取得できないなど問題があるため、ODBCドライバーを使用すること。

ちなみに別のOLDDBドライバーであるPM PostgreSQL OLEDBを使用してみたが、速度も遅くClassic.ASP(VB)のCIntエラーが出るなど使えなかった。

OLDDBドライバーを使用してシステムでは、遅いということで調査したところ1つのSQLで10msの処理が100ms程度かかっていた。ODBCドライバーに変更したところ正常な速度となった。

psqlodbc_09_00_0200.zip を解凍して、指示通りにインストールする。
管理ツールの「ODBCデータソースアドミニストレータ」にてシステムDNS
PostgreSQL35W PostgreSQL Unicode
サーバー名 localhost
データベース名 db_test
ユーザー名 fuga
パスワード hoge

test\Global.asa の接続をODBC用に変更する。

Global.asa

'Session("ConnectionString") = "Provider=PostgreSQL OLE DB Provider;Data Source=localhost;Location=db_test;User ID=fuga;Password=hoge"
↓
Session("ConnectionString") = "DSN=PostgreSQL35W;Server=localhost;Database=db_test;UID=fuga;PWD=hoge;Port=5432;"

接続文字列の「Preload Reader=true」が不要になりました。あると例外エラーになる。

PostgreSQL wiki
現在の9.6の最新版は9.6.17(リリース日:2020-02-13)
https://www.postgresql.org/docs/9.6/release-9-6-17.html

https://www.postgresql.org/support/versioning/

2017-10-05にリリース

1. IDカラム
   行のIDを保存するカラム、一意かつ自動でカウントアップされます。
2. ネイティブのパーティショニング機能
   ひとつのテーブルの実体を分割して扱える
   ※UPDATE時にパーティションキーが更新されるとエラーになる
3. 複数列統計
   Oracle11gで採用された複数列統計相当なもの
4. 並列性の強化
   パラレルクエリの改善
5. JSONとJSONBの全文検索
   JSON型やJSONB型のカラムで全文検索をサポート
6. 論理レプリケーション
   特定のテーブルの情報だけをレプリケーションする

PostgreSQL10ではいくつかのディレクトリ名や関数名などが変更されています。メンテナンス系のシェルスクリプトや監視ツールで下記ディレクトリ名や関数名などをハードコードされている場合は、PostgreSQL10以降の名称に修正する必要があります。

• ログファイル出力先のディレクトリ名がpg_logからlogへ変更
• WALに関連するディレクトリ／関数／コマンドなどでxlogがwal、locationがlsnに変更
• コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更

• PostgreSQL 10の使って嬉しい5つの機能（翻訳）
• 次期バージョン PostgreSQL 10 の新機能とその後の方向性
• HPのBeta1検証報告書
• PostgreSQL10の新機能
• PostgreSQL 10 新機能 @OSC 2017 Fukuoka - slideshare

2018-10-18にリリース

PostgreSQL 11 検証報告

• JITコンパイルの追加 ［性能］
• パーティショニングの様々な改善［機能＋性能］
  ※UPDATE時にパーティションキーが更新されると適切な子テーブルに移動される
• 並列実行の様々な改善［性能］
• プロシージャ内でのトランザクション制御をサポート ［機能］
• 認証でSCRAMチャンネルバインドをサポート［セキュリティ］
• ALTER TABLE .. ADD COLUMN の性能改善 ［性能］
• ウィンドウ関数の拡張［機能］

2019-10-03にリリース

PostgreSQL 12 新機能検証結果 (GA) - pdf
PostgreSQL 12 プレスキット
「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる
PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など
PostgreSQL 12は ここがスゴイ！ ～性能改善やpluggable storage engineなどの新機能を徹底解説～ - SlideShare
技術者に聞く：パーティショニングにおける性能改善への取り組み PostgreSQLインサイド
PostgreSQL 12 – いくつかの新機能のご紹介

• JSON Pathに対応
• 生成列（式で計算される列）に対応
• 各種インデックスの機能追加、性能改善
• パーティショニングの機能追加、性能改善
• テーブルアクセスメソッドに対応
• REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能
• WITH句の共通テーブル式(CTE)で同じCTEが2回以上ある場合に高速化

• WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない
• recovery.confはなくなりpostgresql.confに統合

PostgreSQL 12は ここがスゴイ！ ～性能改善やpluggable storage engineなどの新機能を徹底解説～ - SlideShare

• WITH OIDSを使わなくても良い設計を検討する
• ALTER TABLE … SET WITHOUT OIDSでOIDSを取り除く
• recovery.confに書いていたパラメータをpostgresql.confに書くようにする
• リカバリ時はrecovery.signal、スタンバイ時はstandby.signal を置くようにする
• recovery_target_XXXは設定ファイルに必ず一つになるようにすること

2020-09-24にリリース

PostgreSQL 13 プレスキット
PostgreSQL 13 検証報告
PostgreSQL 13で登場する新機能まとめ
PostgreSQL 13がやってくる！(6) - psqlの改善あれこれ
PostgreSQL 13でのpg_stat_statementsの改善について - SlideShare
PostgreSQL 13でのpg_basebackupの改善について - SlideShare
PostgreSQL 13でのレプリケーション関連の改善について - SlideShare

• B-Treeインデックスの性能向上
• 新しい実行プラン
• パーティションテーブル機能の拡張
• パラレルVACUUM
• 進捗レポートビューの拡張
• pgbenchの拡張
• pg_rewindの拡張
• SQL機能の追加

2021-09-30にリリース

PostgreSQL 14 プレスキット
PostgreSQL 14 検証報告
PostgreSQL14 検証レポート- pdf

• Btreeインデックスの肥大化防止
• 式に対する拡張統計
• LZ4による列のTOAST圧縮
• postgres_fdwの拡張
• ロジカルレプリケーションの改善
• マルチ範囲型
• 再帰CTE問い合わせにおけるSEARCH／CYCLE句のサポート
• アイドルセッションのタイムアウト機能
• 新システムロールpg_read_all_data/pg_write_all_data
• COPY進捗表示、WAL書き出しやロジカルレプリケーションの統計表示
• pg_amcheckコマンド
• ANALYZEコマンドのパフォーマンス改善により大幅に高速化

2022-10-06にリリース

PostgreSQL 15検証報告
PostgreSQL15 検証レポート - pdf
アプリケーションエンジニアから見たPostgreSQL15 の新機能

• Merge文のサポート
• 正規表現関数の追加
• NOT IN句の改善
• ソートの性能改善
• ウィンドウ関数の性能改善
• psql \copyの性能改善
• zstandard圧縮サポート
• 先読みによるリカバリ性能改善
• SQL/JSON対応の拡張
• 多重範囲型への集約
• ロジカルレプリケーションの機能拡張
• 呼び出し元権限で実行されるビュー
• モジュールによるWALアーカイブ
• JSONLOG形式
• モニタリングビューの追加
• ロジカルレプリケーションの機能拡張
• パラレルクエリの強化
• バージョン非互換対応(新機能ではないけど)
  • PublicスキーマのCreate権限がデフォルトからなくなる

2023-09-14にリリース

PostgreSQL 16検証報告
篠田の虎の巻「PostgreSQL 16 Beta 1 新機能検証結果」公開！

• 性能向上
  • パラレルクエリ対応追加
  • ウィンドウ関数の最適化
  • 同時データ投入の性能向上
  • SIMD CPUアクセラレータ対応
• SQL機能
  • SQL/JSON 対応
  • 整数リテラル表現の追加
  • ICU照合順序の拡張
  • ロジカルレプリケーション機能追加
  • スタンバイ上でのパブリケーション
  • トランザクションの並列適用
  • 双方向ロジカルレプリケーション
• クライアント機能
  • libpqロードバランス
  • psqlで拡張プロトコル対応
  • pg_dump圧縮オプション追加
• 運用管理
  • 新たなモニタリング項目
  • 新たな定義済みロール
  • ページ凍結の改善
  • VACUUMリングバッファ指定
  • Mesonビルド

2024-09-26にリリース

PostgreSQL 17検証報告
PostgreSQL 17 検証レポート - pdf

• 性能向上
  • VACUUM性能改善
  • COPY性能向上
  • ストリームI/O対応
  • 各種プランナ改善
• SQL機能
  • SQL/JSON対応の拡充
  • MERGE文の拡張
  • COPY FROM に ON_ERROR オプション追加
• ロジカルレプリケーション機能追加
  • pg_upgrade 対応の改善
  • フェイルオーバに対応
  • pg_createsubscriberコマンド追加
• パーティショニング機能
  • 排他制約に対応
  • IDENTITY列に対応
• 運用管理
  • インクリメンタルバックアップ
  • 新たな定義済みロール
  • 新たなモニタリングビュー
  • pg_dump の –filterオプション

postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。

-- postgresユーザーで実行
SELECT name, context FROM pg_settings;

サーバー上の管理者権限で「pg_ctl reload」コマンドでpostgresql.confの設定値を反映

>pg_ctl reload -D "F:\Program Files\PostgreSQL\9.6\data"
サーバにシグナルを送信しました

コメントを外す。「localhost」から「*」に変更する。これをしないとlocalhostしか接続できない。
他ホストから接続するための設定

postgresql.conf

listen_addresses = '*'		# what IP address(es) to listen on;
port = 5432			# (change requires restart)

※変更後は、PostgreSQLサービス再起動

pg_hda.conf に設定追加する。

全てのクライアントPCからの接続を許可

pg_hda.conf

# IPv4 local connections:
host all all 0.0.0.0/0 md5

クライアントPCからの接続に制限を付ける

pg_hda.conf

# IPv4 local connections:
host    all             all             192.168.64.0/24         md5
host    all             all             192.168.71.0/24         md5

※変更後は、PostgreSQLサービス再起動は不要

※認証方式(https://www.postgresql.jp/document/9.4/html/auth-methods.html)

• trust
  任意のロール名でパスワードなしで接続可能
• md5
  パスワード認証

logging_collectorを「off」から「on」にして、pg_logフォルダにログ出力させる。
障害発生に備えて設定すべき3つのログ関連パラメーター

postgresql.conf

logging_collector = on		# Enable capturing of stderr and csvlog
log_line_prefix='[%t] %u %d %p[%l] '	# special values:

※変更後は、PostgreSQLサービス再起動またはpg_ctl reloadで反映

ログ出力例

[2019-04-25 14:37:08 JST]  7496[1] LOG:  database system was shut down at 2019-04-25 14:37:06 JST
[2019-04-25 14:37:08 JST]  7496[2] LOG:  MultiXact member wraparound protections are now enabled
[2019-04-25 14:37:08 JST]  6592[3] LOG:  database system is ready to accept connections
[2019-04-25 14:37:08 JST]  3240[1] LOG:  autovacuum launcher started

postgresql.conf エラーログの設定 Windowsの場合

log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db =%d,remote = %r app = %a '

ひとつのデータベースを複数のアプリケーションで参照する場合、以下の設定を行っておくと接続元のIPが記録されるため、どこからの接続が多いかがログから判断できます。→システム監査用途

デフォルトでは off となっています。
有効にする場合は on に設定してください。

postgresql.conf

log_connections = on
#log_disconnections = off

ログ出力例

[2020-03-28 15:45:06 JST] [unknown] [unknown] 8512[1] LOG:  connection received: host=10.55.3.3 port=62717
[2020-03-28 15:45:06 JST] postgres postgres 8512[2] LOG:  connection authorized: user=postgres database=postgres

調査の時に行うくらい。実行するSQLが多いので運用時はコメントアウトにしている。
但し、バインド変数の値までは取れない。またSQL文そのものに文法エラーなどがあり実行されなかった場合も出力されない。

postgresql.conf

log_statement = ‘all’

PostgreSQLで実行されたクエリからバグを探してみた(Windows編)

postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。

postgresql.conf 530

#search_path = '"$user", public'	# schema names

search_pathに設定されている値の左から順に選択されるので「test」スキーマを自動的に選択するためには以下のように設定する。

postgresql.conf 530

search_path = 'test,"$user", public'	# schema names

psqlの機能やの使い方を紹介しているスライド資料
psqlの使い方 - pdf

パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。 %APPDATA%\postgresql\pgpass.conf

pgpass.conf

localhost:5432:*:postgres:(パスワード)

もう1つのやり方として、セキュリティ上は非推奨であるがバッチ内に環境変数(PGPASSWORD)を設定した上で実行する。

SET PGPASSWORD=(パスワード)
psql -U (ユーザー) -f xxxxx.sql 

postgresqlの特性上、1度エラーが出ると以降のSQLは全てエラー(commit/rollbackするまで)になります。
エラーは無視して、OKなSQLだけ実行したい場合、ファイルの先頭に下記を記述する。

※COPY句のCSV登録時のエラーには関係しない

\set ON_ERROR_ROLLBACK on

• エラーの後にロールバックしなければいけない理由
• Postgresql で探していたものが見つかった！
• エラーがあってもPostgreSQLスクリプトを続行させるオプションを設定する方法はありますか？

ビュー作成時に整形したりコメントを付けたとしても、ビューに登録された段階で自動変換されて型変換が付いたり整形が崩れたりコメントが消去されてしまう。

ビューで「cannot change data type of view column」で間違っていないのに登録できない場合、ビューを削除して再作成すると登録できるようになる。

「EXECUTE sql;」として、動的クエリを実行させる。また、「RETURN QUERY」としてテーブルを返す。
PostgreSQL めも - ストアドプロシージャ

SQLの文字列の値セットにformat関数で指定している。
9.4.1. format
format指示子の出力を生成するのに使用されるフォーマット変換の型。 以下の型がサポートされています。

• 「%s」は引数の値を単純文字列にフォーマットします。 NULL値は空文字列として扱われます。
• 「%I」は、必要とされれば二重括弧で括られたSQL識別子として引数値を取り扱います。 NULL値はエラーです。
• 「%L」は引数値をSQLリテラルとして引用します。NULL値は引用符無しでNULL文字列として表示されます。

CREATE OR REPLACE FUNCTION wiremodule(_carline TEXT, _part TEXT, _module TEXT) 
RETURNS TABLE(drawingno text, wirename text) AS $$
DECLARE
  i INTEGER;
  SQL TEXT;
  cat TEXT; 
BEGIN
  SQL := format('SELECT drawingno::text, wirename::text FROM wire WHERE carline=%L AND part=%L AND (' , _carline, _part);
 
  cat := '';
  FOR i IN 1..4 LOOP
     SQL := SQL || cat || format('functionmodule%s=%L', i , _module);
     cat := ' OR ';
  END LOOP;
  SQL := SQL || ')';
 
  RETURN QUERY EXECUTE SQL;
 
END;
$$ LANGUAGE 'plpgsql';

デバッガ画面でトレースは出来るが、不安定である(ステップアウトで応答が返らない)。

• PL/pgSQLデバッガを使ってみよう
• [PostgreSQL] pgAdminでデバッグする方法

従来のメッセージ出力によるデバッグ方法もある。

• PL/pgSQL/ターミナルにメッセージを表示する

pg_dumpツールを使用する場合、保存形式がカスタム形式と平文形式があります。
pg_restoreツールは保存形式がカスタム形式のみ対応しています。

[PostgreSQL]pg_dumpとpg_restoreを用いたバックアップとリストア

• ※1…バックアップ時に選択した対象を全てリストアする
• ※2…TOC=Table Of Contents。そのバックアップファイルにどのようなデータが格納されているかを一覧形式で確認したり、一覧から選んだデータだけリストアできる。
• ※3…SQLに変換してから行う。変換時に標準SQLオプションを指定する事ができる為、平文形式より柔軟性が高い。

pg_dumpall

pg_dump -n スキーマ名

スキーマデータ取得.bat

@echo off
 
SET PGPASSWORD=test
pg_dump -U test -n foo > foo.dump

平文形式の場合、psqlコマンドでダンプファイルを指定してリストアする。

psql -U test < foo.dump

tオプションを指定する。
pg_dump -t (テーブル名)

pg_dumpで複数のテーブルのダンプを取る方法

逆に除外する場合は、英大文字のTオプションを指定する。
pg_dump -T (テーブル名)

COPYコマンドを使用する。
TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。
dumpファイルは、テキスト形式でタブ区切りでNULL文字が「\N」で出力される。

COPY  (SELECT * FROM sample_table WHERE id > 10000) TO 'E:/work/sample_table.dump';

TOをFROMにするとインポートできる。

• PostgreSQLの「COPY」で特定のデータをダンプし別のDBに投入するメモ
• PostgreSQLのCSV出力(Export)方法

PostgreSQLのユーザーで実行されるため、出力先がPostgreSQLのユーザーで書き込めるようにする。
フォルダにNETWORK SERVICEユーザーを追加して「フル コントロール」にする。

PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。
その際には、UTF8 のテキストファイルは BOM 無しとする。
※BOM有りだと1行目でvarchar(n)で長すぎます。「先頭列名:データ」でエラーとなる。

• NULLは、CSVモードでは引用符のない空値を指定する。
• 空白をNULL扱いにする場合、「NULL AS ''」を追加する。
• 先頭行にヘッダーがある場合、「HEADER」を追加する。
• CSV側とPostgre側の列順が違う場合、列順を指定する。
• 引用符がデータ内に含まれている場合、データの中に発生しそうにないオプションでバックスペース(x08)を引用符として指定する。quote e'\x08'

// ファイル名は絶対パスで、'\', '/' または '\\' に置換する
SET CLIENT_ENCODING TO 'UTF8';
DELETE FROM tablename;
COPY tablename FROM pathName WITH CSV;

カラム指定なしの例

copy tablename FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';

Oracleは後で列追加すると後ろに付く。PostgreSQLは最初から正しい順序でテーブルを作成した場合。

カラム指定ありの例

copy tablename(col1,col3,col4,col2) FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';

CSVによるデータ移行でCOPY句に「NULL AS ''」オプションを付けていたが、null のところが空欄でセットされてしまっていた。

原因は、単に文字列が空の場合に二重引用符で囲まれていたため。
対応は、空欄は二重引用符を除去する置換処理を追加する。

COPY句でFORCE_NULLを指定すれば、2重引用符のみをNULLで登録できる。 https://www.postgresql.jp/document/11/html/sql-copy.html

WITHの書き方をこれまでと変える必要があるのと、FORCE_NULLの時にカラムを指定する必要がある。

\COPY tablename(col1,col3,col4,col2) FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';
                                                     ↓
\COPY tablename(col1,col3,col4,col2) FROM pathName WITH(ENCODING 'UTF8',FORMAT CSV,HEADER ON,NULL '',FORCE_NULL(col1,col3,col4,col2));

「読み取り用にオープンできませんでした: Permission denied」が出た場合、参照フォルダのプロパティのセキュリティタブにて“Network Service”アカウント、または、“Everyone”アカウントを追加する。

• PostgreSQL 8.3.7文書 COPY
• PostgreSQL CSVファイルからのインポート
• PostgreSQL でダブルクォートを含んだフィールドが正しくインポートできない場合
• PostgreSQLのcopyではまった

インデックスを張らない状態でロードする
初期ロードの際には、インデックスを張ってからロードするよりも、張らずにロードして後から CREATE INDEX したほうが速い場合が多いようです。 このとき、主キー (PRIMARY KEY)、一意性制約 (UNIQUE)、排他制約 (EXCLUDE) も暗黙的にインデックスが追加されることに注意してください. 外部キーの追加もロード後のほうが良いでしょう。

• COPY文でCSVファイルとテーブル間でデータを高速にコピーする(PostgreSQL)
• PostgreSQLでCOPYする時はインデックスを削除すべし、数十倍高速化する
• 大量のデータを高速に投入するには
• pg_bulkload (データの高速読み込みツール)

-f オプションでファイル名を指定する。
psqlのパスワード入力省略

psqlオプション

xxxxx.bat

psql -U hostname -d databasename -f xxxxx.sql

xxxxx.sql

DELETE FROM tablename;
COPY tablename FROM pathname WITH encoding 'UTF8' CSV;

統計情報から取得しているので、おおよその件数が取得できる。
統計情報を手動で行うコマンド「VACUUM ANALYZE」を実行してからなら、正確な件数が取得できる。

SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname='test'

PostgreSQLでテーブルの行数を速く取得する方法

SELECT句で Null文字列を含むカラムを連結すると、全体が Null になります。
これを回避するためには、カラムごとに Null の場合の置換を指定することで対処可能です。
[PostgreSQL] PostgreSQL の SELECT句で Null文字列を連結する方法

SELECT ok_column || '-' || COALESCE((null_column, '') FROM foo;

serial を使ったカラムの作成すると、自動でシーケンスが作成される。
serial ではないカラムで別途作成したシーケンスを適用するには、DEFAULTにシーケンスをセットする。

id INTEGER DEFAULT NEXTVAL('xxxxx_id_seq') NOT NULL,

[PostgreSQL]serial と 手動でのシーケンス作成の違い

デフォルトの設定が「C」となっている場合に違う照合順序「Japanese_Japan.932」に指定するには、「TEMPLATE = template0」を追記する。

CreateDatabase.sql

CREATE DATABASE "FUGA"
  WITH OWNER = postgres
       TEMPLATE = template0
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'Japanese_Japan.932'
       LC_CTYPE = 'Japanese_Japan.932'
       CONNECTION LIMIT = -1;

複合インデックスは、キーの先頭から途中までのカラムが指定されていればインデックスが使われる。

PostgreSQLの場合、インデックスが使われる為には、先頭のキーが必ず必要になる。
複合インデックスは、構成列のどの列を指定すれば選択候補に挙がるのか。

あるテーブルのカラム(C1, C2, C3) に対して複合インデックスを貼っている状況を考えます。
C1, C2, C3 の順で順序付けされています。

bloomインデックスは、その名前のとおり、ブルームフィルタを用いて、条件を満たさないタプルを高速に除外する（＝条件を満たす「かもしれない」タプルを残す）ことが可能なインデックスメソッドです。

主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。
bloomインデックス

bloomインデックスの本領は、複数列に対するインデックス設定をするときに発揮されます。bloom複合列インデックスの場合は、定義した列の順序に関係なくインデックス検索→Birmap Scanになります。B-Treeインデックスが使われるたケースと比べると検索時間は遅くなりますが、SeqScanほど遅くはないという検索時間になります。

-- 拡張追加
CREATE extension bloom
-- 拡張確認
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL
-- bloomインデックスを追加
CREATE INDEX idx_res_match ON res_match USING bloom (factory_cd, line_cd, seq_no, lotserial, cp_cd)

SQLの前に「EXPLAIN」か「EXPLAIN ANALYZE」を付ける。
EXPLAIN は、実行計画を取得できます。
「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。
(※ ANALYZEオプションを付与すると、実際にクエリが実行されてしまうため、INSERT文や、DELETE文などを行う際は注意が必要です。)

• PostgreSQLの実行計画について調べてみた
• PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～ - slideshare

照合順序については下記サイトを参照

• PostgreSQLで並び順がおかしい時の修正方法
• CentOS と PostgreSQL のロケール
• 漢数字が数字順にソートされない理由を調べてみた
• postgresql.confを修正し出力言語を英語にする

運用としては、全て「C」で統一していく。
lc_collateとlc_ctypeは、デフォルトで「C」、lc_messages, lc_monetary, lc_numeric, lc_timeに関してはpostgresql.confファイル内で指定することができる。変更したらPostgresサービスを再起動して反映させる。
※弊害としては、pg_logに出力されるログのメッセージは日本語ではなく英語になる。

現在設定確認SQL

SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%'

照合順序確認SQL

WITH a AS (
	SELECT * FROM generate_series(ascii('A'), ascii('Z')) ascii
	UNION ALL SELECT * FROM generate_series(ascii('Ａ'), ascii('Ｚ'))
	UNION ALL SELECT * FROM generate_series(ascii('a'), ascii('z'))
), b AS (
	SELECT chr(ascii) FROM a
)
SELECT array_agg(chr ORDER BY chr) FROM b ;

照合順序「C」(文字コード順)

{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,Ａ,Ｂ,Ｃ,Ｄ,Ｅ,Ｆ,Ｇ,Ｈ,Ｉ,Ｊ,Ｋ,Ｌ,Ｍ,Ｎ,Ｏ,Ｐ,Ｑ,Ｒ,Ｓ,Ｔ,Ｕ,Ｖ,Ｗ,Ｘ,Ｙ,Ｚ}
 
文字コード順
は（清音）
ば（濁音）
ぱ（半濁音）
ハ（清音）
バ（濁音）
パ（半濁音）

照合順序「Japanese_Japan.932」

{a,A,Ａ,b,B,Ｂ,c,C,Ｃ,d,D,Ｄ,e,E,Ｅ,f,F,Ｆ,g,G,Ｇ,h,H,Ｈ,i,I,Ｉ,j,J,Ｊ,k,K,Ｋ,l,L,Ｌ,m,M,Ｍ,n,N,Ｎ,o,O,Ｏ,p,P,Ｐ,q,Q,Ｑ,r,R,Ｒ,s,S,Ｓ,t,T,Ｔ,u,U,Ｕ,v,V,Ｖ,w,W,Ｗ,x,X,Ｘ,y,Y,Ｙ,z,Z,Ｚ}
 
アイウエオ一つずつ「カタカナ → ひらがな」の順に並びます。
ハ（清音）
は（清音）
バ（濁音）
ば（濁音）
パ（半濁音）
ぱ（半濁音）

エラー「no pg_hba.conf entry for host <IPアドレス> … SSL off」などが出た場合

• Windowsファイアウォールで、Postgresのポート5432を許可するようにする。
• dataフォルダ(PostgreSQLフォルダ配下に無い場合、データ専用のドライブ側にある)の pg_hda.conf ファイルを修正する。

他ホストから接続するための設定変更

COPYのFROM/TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。
バインド変数として絶対パスを渡す。

Test.bat

REM PostgreSQLのインストールパスのbinディレクトリ
set PGPATH=D:\Program Files\PostgreSQL\9.6\bin\
 
psql -f "Test.sql" -v id='1234' -v inputpath="'E:/Work/Test/tmp/sample_dump'"

Test.sql

SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath;

• PostgreSQL: 「OR」を避けてパフォーマンスを向上させよう
• PostgreSQLでpg_bigmを使って中間一致like検索を高速化する

• postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。
• PostgreSQLの場合、テーブルのカラム名に予約語(例 “DO”)があるとそのままでは使えない。二重引用符“do”を付与すれば使える。
• PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む UTF8 のテキストファイルは BOM 無しにする。
• 名前付きパラメータクエリーは、OLEDB版は使えた(プレースホルダ :xxxx)がODBC版では使えない。パラメータクエリーのプレースホルダは“?”である。
• DECODE文は無いため、CASE文に変更する必要がある。
• FROM句 or WHERE句内で使用する場合、PostgreSQLではエイリアスが必要となる。
• PostgreSQLの場合、SELECTで別名にしたカラム名をORDER BY句で指定できないため、一段上にSELECTを追加する必要があった。
• PostgreSQLの場合、1桁のvarchar型にシングルクォーテーションを付けないで指定するとエラーになる。修正例 AA=1 → AA='1'
• PostgreSQLの場合、Numeric型にシングルクォーテーションを付けて指定するとエラーになる。修正例 AA='1' → AA=1
• PostgreSQLの場合、MAX値取得でORDER BY句があるとエラーになる。そもそもORDER BY句が不要。
• FORMAT関数ではパーセント(%)をエスケープで2つ(%%)にする必要がある。
• PostgreSQLの場合、日付型を文字型にした場合に、例「ERROR: 日付が範囲外です: “20150406”」となる。
  修正例 TO_DATE(MAX(DE),'YYYY/MM/DD') AS DE FROM TEST → TO_CHAR(TO_DATE(MAX(DE),'YYYYMMDD'), 'YYYY/MM/DD') AS DE FROM TEST
• FROMとテーブル名の区切りが全角空白だとエラーになるので半角空白にする。

パスワードは英大小文字を区別する。
例 28P01:invalid_password

PostgreSQLエラーコード

• PostgreSQL select で文字列を連結するときの注意点
• PostgreSQLで文字列の結合(||)をすると勝手にトリムされる(固定長文字列を生成する際には注意)
• PostgreSQLで to_char()すると前に半角スペースが入る
• [Oracle][PostgreSQL]Date型の違い
• PostgreSQL の SELECT句で Null文字列を連結する方法
• PostgreSQLのSQL
• PostgreSQLでカラムを追加する際にdefault値を設定する方法
• PostgreSQLで自動的にインデックスが生成される条件
• PostgreSQLとMySQLはどちらかに明確な優位性がありますか、というの質問・回答
• PostgreSQLチューニング実践テクニック(DISTINCTよりGROUP BYの方が速い)
• PostgreSQLとMySQL、使うならどっち？ データベース専門家が8つの視点で徹底比較！
• PostgreSQL Internals (1) for PostgreSQL 9.6 (Japanese) - SlideShare