====== PostgreSQL ======
===== 概要 =====
読み方は、ポストグレス・キューエル。Ingresの次版(Post)としてPostgresとなった。QLはQuery Languageの略。
PostgreSQLは、追記型アーキテクチャを採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。\\
不要領域を再利用可能な状態にするには、VACUUMコマンドを使います。\\
[[http://www.geocities.jp/sugachan1973/doc/funto60.html|PostgreSQLのお勉強]]
将来的には追記型アーキテクチャを捨てて、EnterpriseDBが率先して開発している「zHeap」の実装して他のデータベースのようにUNDOログをもつようにしたい。\\
[[https://gihyo.jp/dev/column/newyear/2019/postgresql?page=2|データベースとして，オープンソースとして，コミュニティとして ―石井達夫氏が語るPostgreSQLの強さと課題]]
===== 接続ドライバー =====
PostgreSQL OLDDBドライバーは、遅い上に[[http://qa.atmarkit.co.jp/q/5163|PostgreSQLのnumeric型フィールド値がうまく取得できない]]など問題があるため、ODBCドライバーを使用すること。\\
ちなみに別のOLDDBドライバーである[[https://sourceforge.net/projects/pmpostgresqlole/|PM PostgreSQL OLEDB]]を使用してみたが、速度も遅くClassic.ASP(VB)のCIntエラーが出るなど使えなかった。
OLDDBドライバーを使用してシステムでは、遅いということで調査したところ1つのSQLで10msの処理が100ms程度かかっていた。ODBCドライバーに変更したところ正常な速度となった。\\
==== ODBCドライバー設定 ====
psqlodbc_09_00_0200.zip を解凍して、指示通りにインストールする。\\
管理ツールの「ODBCデータソースアドミニストレータ」にてシステムDNS\\
PostgreSQL35W PostgreSQL Unicode\\
サーバー名 localhost\\
データベース名 db_test\\
ユーザー名 fuga\\
パスワード hoge\\
==== 接続文字列変更 ====
test\Global.asa の接続をODBC用に変更する。
'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;"
=== NPGSQL ver3にした場合 ===
接続文字列の「Preload Reader=true」が不要になりました。あると例外エラーになる。
===== バージョン =====
[[https://ja.wikipedia.org/wiki/PostgreSQL|PostgreSQL wiki]]\\
現在の9.6の最新版は9.6.17(リリース日:2020-02-13)\\
https://www.postgresql.org/docs/9.6/release-9-6-17.html
^バージョン^リリース^主な変更点^
|9.2|2012/09/10|インデックスオンリースキャン, カスケードレプリケーション, JSON型, 範囲型|
|9.3|2013/09/09|マテリアライズドビュー, 外部テーブルへの書き出し, イベントトリガ, データページ・チェックサム, LATERAL句|
|9.4|2014/12/18|JSONB型, SQLからのサーバー設定の変更（ALTER SYSTEM）, レプリケーションスロット|
|9.5|2016/01/07|UPSERT機能, ALTER TABLE tablename ENABLE ROW LEVEL SECURITYコマンド, ブロックレンジインデックス(BRIN)|
|9.6|2016/09/29|同期レプリケーション機能の強化（「remote_apply」モード）, PostgreSQL間のデータ連携ドライバー(「postgres_fdw」)の強化(リモート下にあるサーバーにおいても実行可能となる|
|10.0|2017/10/05|[[it技術:データベース:postgresql#postgresql10情報|postgresql10情報]] |
|11.0|2018/10/18|[[it技術:データベース:postgresql#postgresql11情報|postgresql11情報]] |
|12.0|2019/10/03|[[it技術:データベース:postgresql#postgresql12情報|postgresql12情報]] |
|13.0|2020/09/24|[[it技術:データベース:postgresql#postgresql13情報|postgresql13情報]] |
|14.0|2021/09/30|[[it技術:データベース:postgresql#postgresql14情報|postgresql14情報]] |
|15.0|2022/10/06|[[it技術:データベース:postgresql#postgresql15情報|postgresql15情報]] |
|16.0|2023/09/14|[[it技術:データベース:postgresql#postgresql16情報|postgresql16情報]] |
==== サポート終了期限(EOL＝End Of Life) ====
https://www.postgresql.org/support/versioning/
^バージョン^初期リリース日^サポート終了期限^
|16|2023年09月14日|2028年11月9日|
|15|2022年10月13日|2027年11月11日|
|14|2021年09月30日|2026年11月12日|
|13|2020年09月24日|2025年11月13日|
|12|2019年10月3日|2024年11月14日|
|11|2018年10月18日|2023年11月9日|
|10|2017年10月5日|2022年11月10日|
|9.6|2016年09月29日|2021年11月11日|
==== PostgreSQL10情報 ====
2017-10-05にリリース
=== 主な新機能 ===
- IDカラム \\ 行のIDを保存するカラム、一意かつ自動でカウントアップされます。
- ネイティブのパーティショニング機能 \\ ひとつのテーブルの実体を分割して扱える \\ ※[[ https://tech-lab.sios.jp/archives/11693|UPDATE時にパーティションキーが更新されるとエラーになる]]
- 複数列統計 \\ [[http://www.oracle.com/webfolder/technetwork/jp/obe/11gr1_db/perform/multistats/multicolstats.html|Oracle11gで採用された複数列統計]]相当なもの
- 並列性の強化 \\ パラレルクエリの改善
- JSONとJSONBの全文検索 \\ JSON型やJSONB型のカラムで全文検索をサポート
- 論理レプリケーション \\ 特定のテーブルの情報だけをレプリケーションする
=== 名称変更 ===
PostgreSQL10ではいくつかのディレクトリ名や関数名などが変更されています。メンテナンス系のシェルスクリプトや監視ツールで下記ディレクトリ名や関数名などをハードコードされている場合は、PostgreSQL10以降の名称に修正する必要があります。
* ログファイル出力先のディレクトリ名がpg_logからlogへ変更
* WALに関連するディレクトリ／関数／コマンドなどでxlogがwal、locationがlsnに変更
* コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更
^PostgreSQL10以降で変更されたディレクトリ／関数名などの例^^
^PostgreSQL9.6以前の名称^PostgreSQL10以降の名称^
|pg_log|log|
|pg_xlog|pg_wal|
|pg_clog|pg_xact|
|pg_current_xlog_location|pg_current_wal_lsn|
|pg_xlogdump|pg_waldump|
|pg_receivexlog|pg_receivewal|
=== 参照 ===
* [[https://techracho.bpsinc.jp/hachi8833/2017_11_22/48200|PostgreSQL 10の使って嬉しい5つの機能（翻訳）]]
* [[https://www.sraoss.co.jp/event_seminar/2017/db_tech_show_case_oss_2017.pdf|次期バージョン PostgreSQL 10 の新機能とその後の方向性]]
* [[http://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_10_New_Features_ja_20170522-1.pdf|HPのBeta1検証報告書]]
* [[https://lets.postgresql.jp/documents/technical/10|PostgreSQL10の新機能]]
* [[https://www.slideshare.net/babystarmonja/postgresql-10-osc-2017-fukuoka|PostgreSQL 10 新機能 @OSC 2017 Fukuoka - slideshare]]
==== PostgreSQL11情報 ====
2018-10-18にリリース
=== 主な新機能 ===
[[https://www.sraoss.co.jp/tech-blog/pgsql/pg11report/|PostgreSQL 11 検証報告]]
* JITコンパイルの追加 ［性能］
* パーティショニングの様々な改善［機能＋性能］\\ ※[[ https://tech-lab.sios.jp/archives/11693|UPDATE時にパーティションキーが更新されると適切な子テーブルに移動される]]
* 並列実行の様々な改善［性能］
* プロシージャ内でのトランザクション制御をサポート ［機能］
* 認証でSCRAMチャンネルバインドをサポート［セキュリティ］
* ALTER TABLE .. ADD COLUMN の性能改善 ［性能］
* ウィンドウ関数の拡張［機能］
==== PostgreSQL12情報 ====
2019-10-03にリリース
=== 主な新機能 ===
[[https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_12_GA_New_Features_ja_20191011-1.pdf|PostgreSQL 12 新機能検証結果 (GA) - pdf]]\\
[[https://www.postgresql.org/about/press/presskit12/ja/|PostgreSQL 12 プレスキット]]\\
[[https://mag.osdn.jp/19/10/04/163000|「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる]]\\
[[https://www.publickey1.jp/blog/19/postgresql_12bjson.html|PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など]]\\
[[https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada|PostgreSQL 12は ここがスゴイ！ ～性能改善やpluggable storage engineなどの新機能を徹底解説～ - SlideShare]]\\
[[https://www.fujitsu.com/jp/products/software/resources/feature-stories/postgres/partitioning-performance/|技術者に聞く：パーティショニングにおける性能改善への取り組み PostgreSQLインサイド]]\\
[[https://aws.amazon.com/jp/blogs/news/postgresql-12-a-deep-dive-into-some-new-functionality/|PostgreSQL 12 – いくつかの新機能のご紹介]]
* JSON Pathに対応
* 生成列（式で計算される列）に対応
* 各種インデックスの機能追加、性能改善
* パーティショニングの機能追加、性能改善
* テーブルアクセスメソッドに対応
* REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能
* WITH句の共通テーブル式(CTE)で同じCTEが2回以上ある場合に[[https://www.sraoss.co.jp/tech-blog/pgsql/pg12-cte/|高速化]]
=== 注意 ===
* WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない
* recovery.confはなくなりpostgresql.confに統合
=== 対処方法 ===
[[https://www.slideshare.net/nttdata-tech/postgresql12-performance-improvement-pluggable-storage-engine-ntt-sawada|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は設定ファイルに必ず一つになるようにすること
==== PostgreSQL13情報 ====
2020-09-24にリリース
=== 主な新機能 ===
[[https://www.postgresql.org/about/press/presskit13/ja/|PostgreSQL 13 プレスキット]]\\
[[https://www.sraoss.co.jp/tech-blog/pgsql/pg13report/|PostgreSQL 13 検証報告]]\\
[[https://news.mynavi.jp/article/20200525-1040900/|PostgreSQL 13で登場する新機能まとめ]]\\
[[https://qiita.com/nuko_yokohama/items/e253dd2619c639558a23|PostgreSQL 13がやってくる！(6) - psqlの改善あれこれ]]\\
[[https://www.slideshare.net/nttdata-tech/postgresql13-pg-stat-statements-nttdata-fujii|PostgreSQL 13でのpg_stat_statementsの改善について - SlideShare]]\\
[[https://www.slideshare.net/nttdata-tech/postgresql13-pg-stat-statements-nttdata-fujii|PostgreSQL 13でのpg_basebackupの改善について - SlideShare]]\\
[[https://www.slideshare.net/nttdata-tech/postgresql13-replication-nttdata-fujii|PostgreSQL 13でのレプリケーション関連の改善について - SlideShare]]
* B-Treeインデックスの性能向上
* 新しい実行プラン
* パーティションテーブル機能の拡張
* パラレルVACUUM
* 進捗レポートビューの拡張
* pgbenchの拡張
* pg_rewindの拡張
* SQL機能の追加
==== PostgreSQL14情報 ====
2021-09-30にリリース
=== 主な新機能 ===
[[https://www.postgresql.org/about/press/presskit14/ja/|PostgreSQL 14 プレスキット]]\\
[[https://www.sraoss.co.jp/tech-blog/pgsql/pg14report/|PostgreSQL 14 検証報告]]\\
[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2021/08/pg14_report_20210818.pdf|PostgreSQL14 検証レポート- pdf]]
* Btreeインデックスの肥大化防止
* 式に対する拡張統計
* LZ4による列のTOAST圧縮
* postgres_fdwの拡張
* ロジカルレプリケーションの改善
* マルチ範囲型
* 再帰CTE問い合わせにおけるSEARCH／CYCLE句のサポート
* アイドルセッションのタイムアウト機能
* 新システムロールpg_read_all_data/pg_write_all_data
* COPY進捗表示、WAL書き出しやロジカルレプリケーションの統計表示
* pg_amcheckコマンド
* ANALYZEコマンドのパフォーマンス改善により大幅に高速化
==== PostgreSQL15情報 ====
2022-10-06にリリース
=== 主な新機能 ===
[[https://www.sraoss.co.jp/tech-blog/pgsql/pg15report/|PostgreSQL 15検証報告]]\\
[[https://www.sraoss.co.jp/tech-blog/wp-content/uploads/2022/09/pg15_report_20220906_rev2.pdf|PostgreSQL15 検証レポート - pdf]]\\
[[https://speakerdeck.com/takahashiikki/postgresql15-new-information|アプリケーションエンジニアから見たPostgreSQL15 の新機能]]
* Merge文のサポート
* 正規表現関数の追加
* NOT IN句の改善
* ソートの性能改善
* ウィンドウ関数の性能改善
* psql \copyの性能改善
* zstandard圧縮サポート
* 先読みによるリカバリ性能改善
* SQL/JSON対応の拡張
* 多重範囲型への集約
* ロジカルレプリケーションの機能拡張
* 呼び出し元権限で実行されるビュー
* モジュールによるWALアーカイブ
* JSONLOG形式
* モニタリングビューの追加
* ロジカルレプリケーションの機能拡張
* パラレルクエリの強化
* バージョン非互換対応(新機能ではないけど)
* PublicスキーマのCreate権限がデフォルトからなくなる
==== PostgreSQL16情報 ====
2023-09-14にリリース
=== 主な新機能 ===
[[https://www.sraoss.co.jp/tech-blog/pgsql/pg16report/|PostgreSQL 16検証報告]]\\
[[https://community.hpe.com/t5/hpe-blog-japan/%E7%AF%A0%E7%94%B0%E3%81%AE%E8%99%8E%E3%81%AE%E5%B7%BB-postgresql-16-beta-1-%E6%96%B0%E6%A9%9F%E8%83%BD%E6%A4%9C%E8%A8%BC%E7%B5%90%E6%9E%9C-%E5%85%AC%E9%96%8B/ba-p/7188972?profile.language=ja|篠田の虎の巻「PostgreSQL 16 Beta 1 新機能検証結果」公開！]]
* 性能向上
* パラレルクエリ対応追加
* ウィンドウ関数の最適化
* 同時データ投入の性能向上
* SIMD CPUアクセラレータ対応
* SQL機能
* SQL/JSON 対応
* 整数リテラル表現の追加
* ICU照合順序の拡張
* ロジカルレプリケーション機能追加
* スタンバイ上でのパブリケーション
* トランザクションの並列適用
* 双方向ロジカルレプリケーション
* クライアント機能
* libpqロードバランス
* psqlで拡張プロトコル対応
* pg_dump圧縮オプション追加
* 運用管理
* 新たなモニタリング項目
* 新たな定義済みロール
* ページ凍結の改善
* VACUUMリングバッファ指定
* Mesonビルド
===== 設定について =====
postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。
-- postgresユーザーで実行
select name, context from pg_settings;
^設定反映のタイミング^^
^context^説明^
|不可(internal)|ユーザー設定変更が出来ない|
|起動(postmaster)|PostgreSQLの再起動で反映される|
|再読み込み(signup)|pg_ctl reload でも反映できる|
|スーパーユーザーのみ(suset)|スーパーユーザーによるSET文でも反映できる|
|いつでも(user)|一般ユーザーによるSET文でも反映できる|
サーバー上の管理者権限で「pg_ctl reload」コマンドでpostgresql.confの設定値を反映
>pg_ctl reload -D "F:\Program Files\PostgreSQL\9.6\data"
サーバにシグナルを送信しました
==== 他ホストから接続するための設定変更 ====
コメントを外す。「localhost」から「*」に変更する。これをしないとlocalhostしか接続できない。\\
[[http://rina.jpn.ph/~rance/linux/postgresql/connect.html|他ホストから接続するための設定]]
listen_addresses = '*' # what IP address(es) to listen on;
port = 5432 # (change requires restart)
※変更後は、PostgreSQLサービス再起動
=== 接続できるクライアントを設定する ===
pg_hda.conf に設定追加する。
全てのクライアントPCからの接続を許可
# IPv4 local connections:
host all all 0.0.0.0/0 md5
クライアントPCからの接続に制限を付ける
# 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フォルダにログ出力させる。\\
[[https://www.ashisuto.co.jp/db_blog/article/20151117_logging_parameter.html|障害発生に備えて設定すべき3つのログ関連パラメーター]]
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
[[https://software.fujitsu.com/jp/manual/manualfiles/m150001/b1ws1172/05z200/b1172-00-02-02-13.html|postgresql.conf エラーログの設定 Windowsの場合]]
log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db =%d,remote = %r app = %a '
^log_line_prefix^^^
^エスケープ^効果^セッションのみ^
|%a|アプリケーション名 例 psql または [unknown]|○|
|%u|ユーザ名|○|
|%d|データベース名|○|
|%r|遠隔ホスト名、またはIPアドレス、およびポート番号|○|
|%h|遠隔ホスト名、またはIPアドレス|○|
|%p|プロセス識別子|×|
|%t|ミリ秒無しのタイムスタンプ|×|
|%m|ミリ秒付きタイムスタンプ|×|
|%i|コマンドタグ。セッションの現在のコマンド種類|○|
|%e|SQLSTATE エラーコード|×|
|%c|セッションID。下記参照|×|
|%l|各セッションまたは各プロセスのログ行の番号。1から始まります。|×|
|%s|プロセスの開始タイムスタンプ|×|
|%v|仮想トランザクションID（backendID/localXID）|×|
|%x|トランザクションID （未割り当ての場合は0）|×|
|%q|何も出力しません。 非セッションプロセスではこのエスケープ以降の出力を停止します。 セッションプロセスでは無視されます。|×|
=== クライアントからの接続・切断を記録 ===
ひとつのデータベースを複数のアプリケーションで参照する場合、以下の設定を行っておくと接続元のIPが記録されるため、どこからの接続が多いかがログから判断できます。→システム監査用途
デフォルトでは off となっています。\\
有効にする場合は on に設定してください。
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文そのものに文法エラーなどがあり実行されなかった場合も出力されない。
log_statement = ‘all’
[[https://www.casleyconsulting.co.jp/blog/engineer/4470/|PostgreSQLで実行されたクエリからバグを探してみた(Windows編)]]
==== 接続子の優先設定変更 ====
postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。
#search_path = '"$user", public' # schema names
search_pathに設定されている値の左から順に選択されるので「test」スキーマを自動的に選択するためには以下のように設定する。
search_path = 'test,"$user", public' # schema names
==== psqlの使い方 ====
psqlの機能やの使い方を紹介しているスライド資料\\
[[https://event.ospn.jp/slides/OSC2022_OnlineKyoto/OSC-kyoto-psql.pdf|psqlの使い方 - pdf]]
==== psqlのパスワード入力省略 ====
パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。
%APPDATA%\postgresql\pgpass.conf
localhost:5432:*:postgres:(パスワード)
もう1つのやり方として、セキュリティ上は非推奨であるがバッチ内に環境変数(PGPASSWORD)を設定した上で実行する。
SET PGPASSWORD=(パスワード)
psql -U (ユーザー) -f xxxxx.sql
==== psqlのスクリプトエラー無視 ====
postgresqlの特性上、1度エラーが出ると以降のSQLは全てエラー(commit/rollbackするまで)になります。\\
エラーは無視して、OKなSQLだけ実行したい場合、ファイルの先頭に下記を記述する。
※COPY句のCSV登録時のエラーには関係しない
\set ON_ERROR_ROLLBACK on
* [[https://web.archive.org/web/20110610035711/http://postgresql.g.hatena.ne.jp/iakio/20081120/1227202633|エラーの後にロールバックしなければいけない理由]]
* [[https://blog.goo.ne.jp/pianyi/e/4d633732dc2b1953d9563815f61f9eb3|Postgresql で探していたものが見つかった！]]
* [[https://kotaeta.com/55381151|エラーがあってもPostgreSQLスクリプトを続行させるオプションを設定する方法はありますか？]]
===== ビュー定義 =====
==== 自動変換 ====
ビュー作成時に整形したりコメントを付けたとしても、ビューに登録された段階で自動変換されて型変換が付いたり整形が崩れたりコメントが消去されてしまう。
==== 型変換エラー ====
ビューで「cannot change data type of view column」で間違っていないのに登録できない場合、ビューを削除して再作成すると登録できるようになる。
===== ユーザ定義関数 =====
==== 動的クエリ ====
「EXECUTE sql;」として、動的クエリを実行させる。また、「RETURN QUERY」としてテーブルを返す。\\
[[http://shin-hanuman.blogspot.jp/2011/06/postgresql.html|PostgreSQL めも - ストアドプロシージャ]]
SQLの文字列の値セットにformat関数で指定している。\\
[[https://www.postgresql.jp/document/9.3/html/functions-string.html|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';
==== デバッグ ====
デバッガ画面でトレースは出来るが、不安定である(ステップアウトで応答が返らない)。\\
* [[http://pgsqldeepdive.blogspot.jp/2013/05/plpgsqldebugger.html|PL/pgSQLデバッガを使ってみよう]]
* [[http://qiita.com/hasu0612/items/a650f720c580a6d400b1|[PostgreSQL] pgAdminでデバッグする方法]]
従来のメッセージ出力によるデバッグ方法もある。
* [[http://db.just4fun.biz/?PL/pgSQL/%E3%82%BF%E3%83%BC%E3%83%9F%E3%83%8A%E3%83%AB%E3%81%AB%E3%83%A1%E3%83%83%E3%82%BB%E3%83%BC%E3%82%B8%E3%82%92%E8%A1%A8%E7%A4%BA%E3%81%99%E3%82%8B|PL/pgSQL/ターミナルにメッセージを表示する]]
===== エクスポート =====
==== 全体 ====
pg_dumpall
==== スキーマ単位 ====
pg_dump -h ホスト名 -p ポート番号 DB名
=== リストア ===
pg_restoreは、pg_dumpによってアーカイブされた平文形式以外のアーカイブファイルを使って、PostgreSQLデータベースをリストアするためのユーティリティ
==== テーブル単位 ====
tオプションを指定する。\\
pg_dump -t (テーブル名)
[[https://blog.suusuke.info/2011/08/19/pg_dump%E3%81%A7%E8%A4%87%E6%95%B0%E3%81%AE%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%83%80%E3%83%B3%E3%83%97%E3%82%92%E5%8F%96%E3%82%8B%E6%96%B9%E6%B3%95/|pg_dumpで複数のテーブルのダンプを取る方法]]
逆に除外する場合は、英大文字のTオプションを指定する。\\
pg_dump -T (テーブル名)
==== スキーマ単位 ====
sオプションを指定する。\\
pg_dump -s (スキーマ名)
==== 条件指定 ====
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にするとインポートできる。
* [[https://qiita.com/okame_qiita/items/5b323eeea924d41a04f0|PostgreSQLの「COPY」で特定のデータをダンプし別のDBに投入するメモ]]
* [[https://qiita.com/cyborg__ninja/items/99efcb5b62a4cef2f156|PostgreSQLのCSV出力(Export)方法]]
=== writing: Permission denied ===
PostgreSQLのユーザーで実行されるため、出力先がPostgreSQLのユーザーで書き込めるようにする。\\
フォルダにNETWORK SERVICEユーザーを追加して「フル コントロール」にする。
{{:it技術:データベース:psql使用フォルダ権限.png|}}
===== インポート =====
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 '';
==== 注意点 ====
=== null のところが空欄でセット ===
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));
=== COPYで読み込みエラー ===
「読み取り用にオープンできませんでした: Permission denied」が出た場合、参照フォルダのプロパティのセキュリティタブにて"Network Service"アカウント、または、"Everyone"アカウントを追加する。
==== 参照 ====
* [[https://www.postgresql.jp/document/8.3/html/sql-copy.html|PostgreSQL 8.3.7文書 COPY]]
* [[http://d.hatena.ne.jp/deeeki/20080613/pgsqlcopycsv|PostgreSQL CSVファイルからのインポート]]
* [[http://obel.hatenablog.jp/entry/20170113/1484280528|PostgreSQL でダブルクォートを含んだフィールドが正しくインポートできない場合]]
* [[http://deta.hateblo.jp/entry/2015/09/10/094157|PostgreSQLのcopyではまった]]
==== 高速化 ====
インデックスを張らない状態でロードする\\
初期ロードの際には、インデックスを張ってからロードするよりも、張らずにロードして後から CREATE INDEX したほうが速い場合が多いようです。 このとき、主キー (PRIMARY KEY)、一意性制約 (UNIQUE)、排他制約 (EXCLUDE) も暗黙的にインデックスが追加されることに注意してください. 外部キーの追加もロード後のほうが良いでしょう。
* [[https://www.xmisao.com/2014/06/06/postgresql-copy-from-csv.html|COPY文でCSVファイルとテーブル間でデータを高速にコピーする(PostgreSQL)]]
* [[https://www.xmisao.com/2014/07/21/remove-index-when-copying-data-to-postgresql.html|PostgreSQLでCOPYする時はインデックスを削除すべし、数十倍高速化する]]
* [[https://lets.postgresql.jp/documents/technical/bulkload/|大量のデータを高速に投入するには]]
* [[https://www.sraoss.co.jp/technology/postgresql/3rdparty/pg_bulkload.php|pg_bulkload (データの高速読み込みツール)]]
==== バッチ化 ====
-f オプションでファイル名を指定する。\\
[[it技術:データベース:postgresql#psqlのパスワード入力省略|psqlのパスワード入力省略]]
[[https://www.postgresql.jp/document/9.2/html/app-psql.html|psqlオプション]]
psql -U hostname -d databasename -f xxxxx.sql
DELETE FROM tablename;
COPY tablename FROM pathname WITH encoding 'UTF8' CSV;
===== Tips =====
==== 全テーブル件数の取得 ====
統計情報から取得しているので、おおよその件数が取得できる。\\
統計情報を手動で行うコマンド「VACUUM ANALYZE」を実行してからなら、正確な件数が取得できる。
select relname, n_live_tup from pg_stat_user_tables where schemaname='test'
[[http://d.hatena.ne.jp/sorayukinoyume/20101202/1291304017|PostgreSQLでテーブルの行数を速く取得する方法]]
==== 文字列結合 ====
SELECT句で Null文字列を含むカラムを連結すると、全体が Null になります。\\
これを回避するためには、カラムごとに Null の場合の置換を指定することで対処可能です。\\
[[http://d.hatena.ne.jp/zankey/20070914/postgresql|[PostgreSQL] PostgreSQL の SELECT句で Null文字列を連結する方法]]
SELECT ok_column || '-' || coalesce((null_column, '') FROM foo;
==== シーケンス作成 ====
serial を使ったカラムの作成すると、自動でシーケンスが作成される。\\
serial ではないカラムで別途作成したシーケンスを適用するには、DEFAULTにシーケンスをセットする。
id integer DEFAULT nextval('xxxxx_id_seq') NOT NULL,
[[https://blog.siwa32.com/postgresql_serial_and_sequence/|[PostgreSQL]serial と 手動でのシーケンス作成の違い]]
==== データベース作成 ====
デフォルトの設定が「C」となっている場合に違う照合順序「Japanese_Japan.932」に指定するには、「TEMPLATE = template0」を追記する。
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の場合、インデックスが使われる為には、先頭のキーが必ず必要になる。\\
[[https://sgyatto.hatenablog.com/entry/2018/11/24/225938|複合インデックスは、構成列のどの列を指定すれば選択候補に挙がるのか。]]
あるテーブルのカラム(C1, C2, C3) に対して複合インデックスを貼っている状況を考えます。\\
C1, C2, C3 の順で順序付けされています。
^C1^C2^C3^複合インデックス利用可不可^
|○|○|○|可|
|○|○|×|可|
|○|×|×|可|
|○|×|○|可 ※C1のみ、C3は無視|
|×|○|○|可 ※table scanが選択される可能性高い|
|×|○|×|可 ※table scanが選択される可能性高い|
|×|×|○|可 ※table scanが選択される可能性高い|
|×|×|×|不可|
=== bloomインデックス ===
bloomインデックスは、その名前のとおり、ブルームフィルタを用いて、条件を満たさないタプルを高速に除外する（＝条件を満たす「かもしれない」タプルを残す）ことが可能なインデックスメソッドです。
主キーが複数の場合のインデックスにはデフォルトのb-treeインデックスが使用されるが、弱点として先頭のキーが外れるとインデックスが使われなくなる。その場合に複数列インデックスには、bloomインデックスを使用することで、先頭のキーが条件が外れていてもインデックスが使われる。\\
[[https://qiita.com/nuko_yokohama/items/4a7fe2a731a479189034|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文などを行う際は注意が必要です。)
* [[https://www.casleyconsulting.co.jp/blog/engineer/259/|PostgreSQLの実行計画について調べてみた]]
* [[https://www.slideshare.net/MikiShimogai/postgre-sql-explain|PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～ - slideshare]]
^主な演算子一覧^^^
^分類^演算子^処理^
|テーブルスキャン|Seq scan|インデックスを使用せず、全件を検索|
| |Index scan|インデックスを使用してスキャン|
| |Bitmap scan|ビットマップを使用してスキャン|
| |Index only scan|問い合わせがインデックスに含まれるカラムのみで完結する場合のスキャン|
| |Tid scan|検索条件がタプルID(ctid)のスキャン|
|その他のスキャン|Function scan|関数がデータをgatherした結果をスキャン|
|テーブルの結合|Nested Loop|ネステッド・ループ結合を行う|
| |Merge Join|ソート・マージ結合を行う|
| |Hash Join|ハッシュ結合を行う|
==== 照合順序 ====
照合順序については下記サイトを参照
* [[http://se.ykysd.com/2015/09/28/orderby/|PostgreSQLで並び順がおかしい時の修正方法]]
* [[http://kenpg2.seesaa.net/article/408972224.html|CentOS と PostgreSQL のロケール]]
* [[http://blog.jnito.com/entry/2014/12/03/185146|漢数字が数字順にソートされない理由を調べてみた]]
* [[https://db.just4fun.biz/?PostgreSQL/%E3%83%AD%E3%82%B0%E3%81%AE%E8%A8%80%E8%AA%9E%E3%82%92%E8%8B%B1%E8%AA%9E%E3%81%AB%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95#v53e7429|postgresql.confを修正し出力言語を英語にする]]
運用としては、全て「C」で統一していく。\\
lc_collateとlc_ctypeは、デフォルトで「C」、lc_messages, lc_monetary, lc_numeric, lc_timeに関してはpostgresql.confファイル内で指定することができる。変更したらPostgresサービスを再起動して反映させる。\\
※弊害としては、pg_logに出力されるログのメッセージは日本語ではなく英語になる。
^lc_collate|C|
^lc_ctype|C|
^lc_messages|C|
^lc_monetary|C|
^lc_numeric|C|
^lc_time|C|
SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%'
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 ;
{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,Ａ,Ｂ,Ｃ,Ｄ,Ｅ,Ｆ,Ｇ,Ｈ,Ｉ,Ｊ,Ｋ,Ｌ,Ｍ,Ｎ,Ｏ,Ｐ,Ｑ,Ｒ,Ｓ,Ｔ,Ｕ,Ｖ,Ｗ,Ｘ,Ｙ,Ｚ}
文字コード順
は（清音）
ば（濁音）
ぱ（半濁音）
ハ（清音）
バ（濁音）
パ（半濁音）
{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 … SSL off」などが出た場合
* Windowsファイアウォールで、Postgresのポート5432を許可するようにする。
* dataフォルダ(PostgreSQLフォルダ配下に無い場合、データ専用のドライブ側にある)の pg_hda.conf ファイルを修正する。
[[it技術:データベース:postgresql#他ホストから接続するための設定変更|他ホストから接続するための設定変更]]
==== バッチからCOPYの絶対パスを渡す ====
COPYのFROM/TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。\\
バインド変数として絶対パスを渡す。
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'"
SET CLIENT_ENCODING TO 'UTF8';
COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath;
==== パフォーマンス向上 ====
* [[https://techracho.bpsinc.jp/hachi8833/2020_04_16/79219|PostgreSQL: 「OR」を避けてパフォーマンスを向上させよう]]
* [[https://blog.honjala.net/entry/2016/11/19/021057|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
[[https://www.postgresql.jp/document/pg920doc/html/errcodes-appendix.html|PostgreSQLエラーコード]]
===== 参照 =====
* [[http://d.hatena.ne.jp/taramonera/20110121/1295574794|PostgreSQL select で文字列を連結するときの注意点]]
* [[http://dice-k-0.blogspot.jp/2011/11/postgresql.html|PostgreSQLで文字列の結合(||)をすると勝手にトリムされる(固定長文字列を生成する際には注意)]]
* [[https://blog.kawa-xxx.jp/entry/20140609/1402318085|PostgreSQLで to_char()すると前に半角スペースが入る]]
* [[http://heppoen.seesaa.net/article/148020207.html|[Oracle][PostgreSQL]Date型の違い]]
* [[http://d.hatena.ne.jp/zankey/20070914/postgresql|PostgreSQL の SELECT句で Null文字列を連結する方法]]
* [[http://www.ne.jp/asahi/hishidama/home/tech/postgres/sql.html|PostgreSQLのSQL]]
* [[http://log.miraoto.com/2012/03/358/|PostgreSQLでカラムを追加する際にdefault値を設定する方法]]
* [[http://d.hatena.ne.jp/sternheller/20090717/1247853538|PostgreSQLで自動的にインデックスが生成される条件]]
* [[http://qa.atmarkit.co.jp/q/2395|PostgreSQLとMySQLはどちらかに明確な優位性がありますか、というの質問・回答]]
* [[https://thinkit.co.jp/free/marugoto/2/1/18/1.html|PostgreSQLチューニング実践テクニック(DISTINCTよりGROUP BYの方が速い)]]
* [[https://employment.en-japan.com/engineerhub/entry/2017/09/05/110000|PostgreSQLとMySQL、使うならどっち？ データベース専門家が8つの視点で徹底比較！]]
* [[https://www.slideshare.net/noriyoshishinoda/postgresql-internals-1-for-postgresql-96-japanese|PostgreSQL Internals (1) for PostgreSQL 9.6 (Japanese) - SlideShare]]