PostgreSQL で NULL 値を含む複数の行を効率的に更新するにはどうすればよいですか?

NULL 値を使用して複数の行を更新する際の課題

PostgreSQL では、NULL 値を扱うときに複数の行を同時に更新すると課題が発生する可能性があります。スタンドアロンの VALUES 式を使用する場合、PostgreSQL にはデータ型に関する情報が不足しています。その結果、NULL 値を含む非数値リテラルには明示的なキャストが必要です。

更新クエリでの NULL 値のキャスト

この問題を解決するには、次の解決策を検討してください。

1. LIMIT 0 のサブクエリ

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   UNION ALL
   VALUES
      (1, 20, NULL)  -- no type casts here
    , (2, 50, NULL)
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid;

• 長所: オーバーヘッドが最小限でシンプルかつ高速、関連する列名の知識が必要
• 短所: 一部のデータ型では型解決が失敗する可能性があります

2. LIMIT 0 と SELECT を使用したサブクエリ

UPDATE foo SET x=t.x, y=t.y FROM
(
  (SELECT pkid, x, y FROM foo LIMIT 0)
   UNION ALL SELECT 1, 20, NULL
   UNION ALL SELECT 2, 50, NULL
   ) t               -- column names and types are already defined
WHERE  f.pkid = t.pkid;

• 長所: 型解決の失敗を回避
• 短所: 行ごとの構文が冗長で、長いリストの VALUES 式より遅い

3.列ごとのタイプの VALUES 式

...
FROM  (
   VALUES 
     ((SELECT pkid FROM foo LIMIT 0)
    , (SELECT x    FROM foo LIMIT 0)
    , (SELECT y    FROM foo LIMIT 0))  -- get type for each col individually
   , (1, 20, NULL)
   , (2, 50, NULL)
   ) t (pkid, x, y)  -- columns names not defined yet, only types.
...

• 長所: 通常 #2 より高速、関連する列の短い構文、関連する列名のみの知識が必要
• 短所: 冗長な構文数行の場合、読みにくくなります

4.行型の VALUES 式

UPDATE foo f
SET x = (t.r).x         -- parenthesis needed to make syntax unambiguous
  , y = (t.r).y
FROM (
   VALUES
      ('(1,20,)'::foo)  -- columns need to be in default order of table
     ,('(2,50,)')       -- nothing after the last comma for NULL
   ) t (r)              -- column name for row type
WHERE  f.pkid = (t.r).pkid;

• 長所: 少数の行とすべての列に対して最速かつ最短の構文、列名を指定する必要がない
• 短所: あまり知られていない構文、デフォルトの順序で関連する列の数と位置を知っている必要があります

5。分解された行タイプの VALUES 式

...
FROM (
   VALUES
      ((NULL::foo).*)  -- decomposed row of values
    , (1, 20, NULL)    -- uniform syntax for all
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
...

• 長所と短所は #4 と似ていますが、より一般的に知られている構文です

以上がPostgreSQL で NULL 値を含む複数の行を効率的に更新するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。