PostgreSQL에서 여러 행을 업데이트할 때 NULL 값을 처리하는 방법은 무엇입니까?

여러 행을 업데이트할 때 NULL 캐스팅

단일 쿼리를 사용하여 테이블의 여러 행을 업데이트할 때 값이 할당된 항목은 관련된 열의 데이터 유형과 일치합니다. NULL 값이 포함된 경우 유형 불일치로 인해 오류가 발생할 수 있습니다.

문제 개요

다음 쿼리를 고려하세요.

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

이 쿼리는 NULL이 아닌 값에 대해 작동하지만 NULL 값이 도입되면 오류가 발생합니다. 발생:

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

이 오류는 NULL 값에 대한 유형 지정이 누락되어 발생합니다. PostgreSQL은 리터럴을 기반으로 유형을 추측하려고 시도하여 정수 열 x와 일치하지 않게 됩니다.

해결책

이 문제를 해결하려면 여러 가지 해결 방법을 사용할 수 있습니다. :

0. LIMIT 0이 있는 행 선택, UNION ALL VALUES가 있는 행 추가

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. LIMIT 0으로 행 선택, UNION ALL SELECT로 행 추가

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 표현식

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 표현식

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 표현식

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 행 유형에서 가져온 유형의 표현식

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;

솔루션 선택은 성능, 편의성, 관련된 열 수와 같은 요소에 따라 달라집니다. 일반적으로 단순성과 유연성을 위해 솔루션 4와 5를 권장합니다.

위 내용은 PostgreSQL에서 여러 행을 업데이트할 때 NULL 값을 처리하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!