Wie gehe ich mit NULL-Werten um, wenn ich mehrere Zeilen in PostgreSQL aktualisiere?

Umwandlung von NULL beim Aktualisieren mehrerer Zeilen

Beim Aktualisieren mehrerer Zeilen in einer Tabelle mit einer einzigen Abfrage muss unbedingt sichergestellt werden, dass die Werte korrekt sind Die zugewiesenen Werte stimmen mit den Datentypen der beteiligten Spalten überein. Wenn NULL-Werte beteiligt sind, kann dies zu Fehlern aufgrund von Typkonflikten führen.

Problemübersicht

Bedenken Sie die folgende Abfrage:

UPDATE foo SET x=t.x, y=t.y
FROM (
  VALUES (50, 50, 1),
         (100, 120, 2)
)
AS t(x, y, pkid)
WHERE foo.pkid=t.pkid

Diese Abfrage funktioniert für Nicht-NULL-Werte, aber wenn NULL-Werte eingeführt werden, tritt ein Fehler auf tritt auf:

UPDATE foo SET x=t.x, y=t.y
FROM (
  VALUES (null, 20, 1),
         (null, 50, 2)
)
AS t(x, y, pkid)
WHERE foo.pkid=t.pkid

Der Fehler wird durch die fehlende Typangabe für die NULL-Werte verursacht. PostgreSQL versucht, ihren Typ anhand des Literals zu erraten, was zu einer Nichtübereinstimmung mit der Ganzzahlspalte x führt.

Lösungen

Um dieses Problem zu beheben, können mehrere Lösungen eingesetzt werden :

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

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;

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

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;

2. VALUES-Ausdruck mit spaltenweisem Typ

UPDATE foo f
SET    x = t.x
     , y = t.y
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.
WHERE  f.pkid = t.pkid;

3. VALUES-Ausdruck mit Zeilentyp

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;

4. VALUES-Ausdruck mit zerlegtem Zeilentyp

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

5. VALUES-Ausdruck mit vom Zeilentyp abgerufenen Typen

UPDATE foo f
SET   (  x,   y)
    = (t.x, t.y)  -- short notation, see below
FROM (
   VALUES
      ((NULL::foo).pkid, (NULL::foo).x, (NULL::foo).y)  -- subset of columns
    , (1, 20, NULL)
    , (2, 50, NULL)
   ) t(pkid, x, y)       -- arbitrary column names (I made them match)
WHERE  f.pkid = t.pkid;

Die Wahl der Lösung hängt von Faktoren wie Leistung, Komfort und der Anzahl der beteiligten Spalten ab. Aus Gründen der Einfachheit und Flexibilität werden im Allgemeinen die Lösungen 4 und 5 empfohlen.

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