여러 행을 업데이트 할 때 NULL 유형을 주조

내가 동시에 여러 행을 업데이트 할 때 문제가 있습니다.

다음은 테이블과 쿼리 I 사용 (더 나은 독서 단순화)입니다 :

표

CREATE TABLE foo
(
    pkid integer,
    x integer,
    y integer
)

질문

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

이 쿼리는 완벽하게 작동하지만 모든 X 또는 Y 값이 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

오류

ERROR:  column "x" is of type integer but expression is of type text
LINE 1: UPDATE foo SET x=t.x FROM

상기 값 중 적어도 하나 (NULL, 20, 1)를 변경하는 수정하기위한 유일한 방법 (NULL : INT (50), 2)하지만, 그렇게 할 수없는 I이 "업데이트를 생성하는 기능을 갖고 있기 때문에 여러 행 "쿼리는 열 유형에 대해 아무것도 알지 못한다.

여기에 가장 좋은 방법은 무엇입니까? 여러 행에 대한 더 나은 업데이트 쿼리가 있습니까? 어떤 함수 또는 구문의 AS t처럼 거기 (: getType로 (foo.x) Y : X는 getType로 (foo.y)는, pkid : getType로 (foo.pkid))는?

해결법

==============================
1.독립형 VALUES 식 PostgreSQL는 데이터 유형이어야 무슨 생각이 없습니다. 간단한 숫자 리터럴 시스템은 일치 유형을 가정하는 행복하다. 당신이 이미 발견으로 - 그러나 (NULL과 같은) 다른 입력으로 명시 적으로 캐스팅해야합니다.

독립형 VALUES 식 PostgreSQL는 데이터 유형이어야 무슨 생각이 없습니다. 간단한 숫자 리터럴 시스템은 일치 유형을 가정하는 행복하다. 당신이 이미 발견으로 - 그러나 (NULL과 같은) 다른 입력으로 명시 적으로 캐스팅해야합니다.

당신은 (빠른하지만, PostgreSQL을 특정) pg_catalog를 조회 할 수 있습니다 또는 INFORMATION_SCHEMA (느린,하지만 표준 SQL)를 찾아 적절한 유형으로 문을 준비 할 수 있습니다.

또는 당신은 (내가 마지막 최선의 저장)이 간단한 "속임수"중 하나를 사용할 수 있습니다 :
```
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
```
첫 번째 서브 쿼리의 서브 - 선택 :
```
(SELECT x, y, pkid  FROM foo LIMIT 0)
```
컬럼에 대한 이름과 유형을 얻을 수 있지만, 실제 행을 추가하는 LIMIT 0 방지를. 이후 행은 지금 잘 정의 행 유형 강제 변환되는 - 그리고 그들은 유형과 일치하는 경우 즉시 확인. 원래의 형태를 통해 미묘한 추가 개선해야한다.

주요 제한 : 별도의 SELECT 라인, 포스트 그레스는 즉시 "최선의 노력"형식으로 입력 리터럴을 캐스팅합니다. 나중에 첫 번째 SELECT의 주어진 유형의 캐스팅을 시도 할 때 가정 유형 및 대상 유형 사이에 등록 된 할당 캐스트가없는 경우, 이미 너무 늦었 몇 가지 유형의 수 있습니다. 예 텍스트 -> 타임 스탬프.

찬성: - 최소 오버 헤드. - 읽을 수있는, 간단하고 빠른 몇 행합니다. - 당신은 테이블의 관련 열 이름을 알 필요가있다.

범죄자: - 입력 해상도는 일부 유형의 실패 할 수 있습니다. - 당신이 테스트에서 발견 UNION ALL SELECT는 행의 긴 목록에 대한 값 표현보다 느립니다. - 자세한 정보는 행 당 구문.
```
...
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.
...
```
숫자 식의 첫 번째 행은 행의 모든 후속 유형을 정의 NULL 값의 연속이다.

찬성: - 빠른 1 이상. - 많은 열을 단지 몇 가지 관련이있는 테이블에 대한 최단 구문. - 당신은 테이블의 관련 열 이름을 알 필요가있다.

범죄자: - 자세한 정보는 단지 몇 행에 대한 구문 - 적은 읽을 수 (IMO).
```
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
```
당신은 분명히 테이블 이름을 알고있다. 당신은 또한 열 수를 알고있는 경우 순서는이 작업 할 수 있습니다.

PostgreSQL의 모든 테이블에 대한 행 유형이 자동으로 등록됩니다. 당신이 당신의 표현의 열 수를 일치하면, 테이블 ( '(1,50)':: foo는)하여 암시 적으로 열 유형을 지정하는 행 형식으로 캐스팅 할 수 있습니다. NULL 값을 입력 쉼표 뒤에 아무것도 넣어 없습니다. 모든 관련이없는 후행 컬럼에 쉼표를 추가합니다. 다음 단계에서는 보여 구문을 사용하여 개별 열을 액세스 할 수 있습니다. 설명서의 필드 선택에 대한 자세한.

또는 당신은 NULL 값과 실제 데이터를 사용하여 균일 한 구문의 행을 추가 할 수 있습니다 :
```
...
  VALUES
      ((NULL::foo))  -- row of NULL values
    , ('(1,20,)')    -- uniform ROW value syntax for all
    , ('(2,50,)')
...
```
추가 된 행은 당신의 UPDATE의 WHERE 절에 의해 제외됩니다. 다른 목적을 위해 당신은 하위 쿼리에서 1 OFFSET에 추가 된 첫 번째 행을 제거 할 수 있습니다.

찬성: - 가장 빠른 (몇 행과 열을 내 시험에서 적어도). - 몇 행 또는 모든 열을 필요로 테이블 최단 구문. - 모든 열을 자동으로 일치하는 이름이 - 당신은 테이블의 열을 밖으로 철자 할 필요가 없습니다.

범죄자: - 기록 / 열 / 복합 형의 필드 선택 안 공지 구. - 당신은 번호와 기본 순서에서 해당 컬럼의 위치를 알 필요가있다.

3. 마찬가지로,하지만 표준 문법에서 분해 행 :
```
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
```
또는 NULL의 선두 행으로 다시 값 :
```
...
   VALUES
      ((NULL::foo).*)  -- row of NULL values
    , (1, 20, NULL)    -- uniform syntax for all
    , (2, 50, NULL)
...
```
장점 3. 같은 단점하지만, 더 일반적으로 알려진 구문. 그리고 당신은 (필요할 경우) 열 이름을 철자 할 필요가있다.

Unril 댓글을 달았처럼, 우리는 컬럼의 서브 세트를 제공하기 위해 2, 4의 장점을 결합 할 수 있습니다 :
```
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. 같은 장점과 단점이 있지만, 우리는 열 부분 집합으로 작업 할 수 및 전체 목록을 알아야 할 필요가 없습니다.

또한 많은 열이있는 경우에 편리는 UPDATE 자체에 대한 짧은 구문을 표시. 관련 :

4.와 5. 내 즐겨 찾기입니다.
==============================
2.쿼리를 생성하는 스크립트가있는 경우 추출하고 각 열의 데이터 형식을 캐시 따라 유형 캐스트를 만들 수 있습니다. 예컨대 :

쿼리를 생성하는 스크립트가있는 경우 추출하고 각 열의 데이터 형식을 캐시 따라 유형 캐스트를 만들 수 있습니다. 예컨대 :
```
SELECT column_name,data_type,udt_name 
FROM information_schema.columns 
WHERE table_name = 'foo';
```
당신이 마지막 단락에 설명 된대로이 udt_name에서 당신은 필요한 캐스트를 얻을 수 있습니다. 또한 당신이 할 수 있습니다 :
```
UPDATE foo
SET x = t.x
FROM (VALUES(null::int4,756),(null::int4,6300))
AS t(x,pkid)
WHERE foo.pkid = t.pkid;
```
==============================
3.스크립트는 foo는에서 임시 테이블을 생성합니다. 그것은 foo는 같은 데이터 유형이있을 것이다. 비어 있으므로 불가능한 조건을 사용하여

스크립트는 foo는에서 임시 테이블을 생성합니다. 그것은 foo는 같은 데이터 유형이있을 것이다. 비어 있으므로 불가능한 조건을 사용하여
```
select x, y, pkid
into temp t
from foo
where pkid = -1
```
그것으로 삽입 할 스크립트를 확인 :
```
insert into t (x, y, pkid) values
(null, 20, 1),
(null, 50, 2)
```
이제부터 업데이트 :
```
update foo 
set x=t.x, y=t.y 
from t
where foo.pkid=t.pkid
```
마지막으로 드롭 :
```
drop table t
```

from https://stackoverflow.com/questions/12426363/casting-null-type-when-updating-multiple-rows by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 선택적 매개 변수 SQL 쿼리를 처리하기 위해? (0)	2020.06.12
[SQL] 어떻게 SQL에서 필드 값 당 행의 수를 제한 할 수 있습니까? (0)	2020.06.12
[SQL] SQL 서버 : 포함 된 VARCHAR (MAX) 필드에서 유효하지 않은 XML 문자를 교체 (0)	2020.06.12
[SQL] MySQL의보기에서 행 순위 (0)	2020.06.12
[SQL] 오라클 : 기능 인덱스 선택의 고유성 (0)	2020.06.11

복붙노트

[SQL] 여러 행을 업데이트 할 때 NULL 유형을 주조

여러 행을 업데이트 할 때 NULL 유형을 주조

해결법

1.독립형 VALUES 식 PostgreSQL는 데이터 유형이어야 무슨 생각이 없습니다. 간단한 숫자 리터럴 시스템은 일치 유형을 가정하는 행복하다. 당신이 이미 발견으로 - 그러나 (NULL과 같은) 다른 입력으로 명시 적으로 캐스팅해야합니다.

2.쿼리를 생성하는 스크립트가있는 경우 추출하고 각 열의 데이터 형식을 캐시 따라 유형 캐스트를 만들 수 있습니다. 예컨대 :

3.스크립트는 foo는에서 임시 테이블을 생성합니다. 그것은 foo는 같은 데이터 유형이있을 것이다. 비어 있으므로 불가능한 조건을 사용하여

'SQL' 카테고리의 다른 글

티스토리툴바