Wie gehe ich mit NULL-Werten in mehrzeiligen PostgreSQL-Updates um?

Umwandeln von NULL-Werten in mehrzeiligen Aktualisierungen

Das Aktualisieren mehrerer Zeilen mit NULL-Werten in PostgreSQL kann aufgrund des Fehlens von Typdaten für eigenständige VALUES-Ausdrücke zu Fehlern führen. Hier sind einige Lösungen, um dieses Problem zu lösen:

1. Zeile mit LIMIT 0 auswählen, Zeilen mit UNION ALL VALUES anhängen

Diese Methode verwendet eine LIMIT 0-Unterabfrage, um Spaltennamen und -typen aus der Tabelle abzurufen. Dadurch wird der Zeilentyp definiert, der dann zum Umwandeln der aktualisierten Werte verwendet wird.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- parenthesis needed with LIMIT
   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. Zeile mit LIMIT 0 auswählen, Zeilen mit UNION ALL SELECT anhängen

Ähnlich der vorherigen Methode, verwendet jedoch ein SELECT zum Anhängen von Zeilen anstelle eines VALUES-Ausdrucks, wodurch potenzielle Probleme bei der Typauflösung vermieden werden.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM  (
  (SELECT pkid, x, y FROM foo LIMIT 0) -- parenthesis needed with LIMIT
   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;

3. VALUES-Ausdruck mit spaltenspezifischem Typ

Dieser Ansatz verwendet eine Zeile mit NULL-Werten als erste Zeile eines VALUES-Ausdrucks und definiert so effektiv die Spaltentypen. Nachfolgende Zeilen können ohne explizites Casting aktualisiert werden.

...
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.
...

4. VALUES-Ausdruck mit Zeilentyp

Diese Methode verwendet Zeilentypen, um Spaltentypen implizit zu definieren. Die Zeile wird in einen Zeilentyp umgewandelt, der die Tabelle darstellt, und über die Feldauswahl kann auf einzelne Spalten zugegriffen werden.

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-Ausdruck mit zerlegtem Zeilentyp

Ähnlich der vorherigen Methode, verwendet jedoch zerlegte Zeilenwerte in Standardsyntax.

UPDATE foo f
SET    x = t.x
     , y = t.y
FROM (
   VALUES
      (('(1,20,)'::foo).*)  -- decomposed row of values
    , (2, 50, NULL)
   ) t(pkid, x, y)  -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;     -- eliminates 1st row with NULL values

Schlussfolgerung

Beim Auswählen einer Zeile mit LIMIT 0 ist Da es sich um eine schnelle und weit verbreitete Methode handelt, kann sie fehlschlagen, wenn für einige Werte keine Typauflösung möglich ist. Die anderen Methoden bieten alternative Ansätze, deren Auswirkungen auf die Leistung je nach Anzahl der beteiligten Spalten und Zeilen variieren. Letztendlich hängt die Wahl der Methode von den spezifischen Anforderungen und der Kompatibilität mit vorhandenem Code ab.

Das obige ist der detaillierte Inhalt vonWie gehe ich mit NULL-Werten in mehrzeiligen PostgreSQL-Updates um?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!