あまり知られていないPostgreSQLの機能たち

 (hakibenita.com)
13 ポイント 投稿者 xguru 2021-11-10 | 4件のコメント | WhatsAppで共有

  • Insert on Conflict（Upsert）時に更新/追加された行数を知る: xmax

  • 特定のカラムに権限を付与する

  • マルチパターンマッチング: SIMILAR TO または ~ 正規表現

  • 現在のシーケンス値を増やさずに調べる: pg_sequence_last_value()

  • 複数行SQLと \\COPY を一緒に使うには: TEMPORARY VIEW または COPY と \\g を利用

  • 自動生成キーへの値設定を防ぐ: テーブル作成時に GENERATED ALWAYS を指定（GENERATED BY DEFAULT の代わりに）

  • Pivotテーブルを作る: pandas.pivot_table、\\crosstabview、または tablefunc 拡張を使用

  • Dollar Quoting

→ $$ と $$ の間のすべての文字は文字列として認識

→ $JSON$ / $function$ のように中でタグを利用可能

→ ::jsonb を付けると高速に jsonb オブジェクトを生成

  • DBオブジェクトにコメントを付ける: COMMENT ON TABLE/COLUMN、Dollar Quoting を使って長い文字列の説明を追加することも可能

→ 作成: COMMENT ON TABLE sale IS 'Sales made in the system';

→ 表示: \\d+ sale

  • DBごとに History を別々に記録する

→ \\set HISTFILE ~/.psql_history- :DBNAME

  • 自動補完を大文字にする: \\set COMP_KEYWORD_CASE upper

  • スリープを入れる: pg_sleep(秒)、pg_sleep_for('4 minutes 14 seconds')

  • サブクエリなしでグループの最初/最後の行を取得する: DISTINCT ON (グループカラム)

  • uuid-ossp 拡張なしでUUIDを生成する: gen_random_uuid() で version 4 UUID を生成

  • 再現可能なランダムデータを生成する: setseed() でシードを同じにする

  • 既存データをすぐに検証せず条件を追加する: ALTER 時に NOT VALID で条件だけ先に追加し、ALTER VALIDATE で既存データの検証は別途実行

  • Oracle の Synonym のような機能を使う: search_path を変更（Zero Downtime Migration 時に有用）

  • 重複する Range を探す: OVERLAPS 演算子

関連記事

4件のコメント

 
galadbran 2021-11-11

GeekNews には postgres の話題ばかり上がっているんじゃないですか！ （冗談です。）

私たちの製品でも主に pg を使っているので、感謝するばかりです。 :)
 
xguru 2021-11-11

最近Postgresが大人気だから、ということなんでしょうか……!?
 
nallwhy 2021-11-10

Postgres が最高だ
 
roxie 2021-11-10

同感です。