복붙노트

[SQL] PostgreSQL의 9.3을 사용하여 CTE UPSERT에 DEFAULT 값을 생성

SQL

PostgreSQL의 9.3을 사용하여 CTE UPSERT에 DEFAULT 값을 생성

나는 우리가 포스트 그레스 실제 upsert / 병합을 얻을 때까지 쓰기 열팽창 계수를 사용하여 PostgreSQL을에 upsert 매우 우아한 솔루션을 수 에뮬레이트하는 것을 찾는거야. (참조 : https://stackoverflow.com/a/8702291/558819)

그러나 한 가지 문제가있다 : I 기본 값을 삽입 할 수있는 방법? 사용 NULL은 NULL로 당연하지 도움을 명시 적으로 MySQL의와 예와는 달리, NULL로 삽입됩니다 것입니다. 예 :

WITH new_values (id, playlist, item, group_name, duration, sort, legacy) AS (
    VALUES (651, 21, 30012, 'a', 30, 1, FALSE)
    ,      (NULL::int, 21, 1, 'b', 34, 2, NULL::boolean)
    ,      (668, 21, 30012, 'c', 30, 3, FALSE)
    ,      (7428, 21, 23068, 'd', 0, 4, FALSE)
), upsert AS (
    UPDATE playlist_items m
    SET    (playlist, item, group_name, duration, sort, legacy)
       = (nv.playlist, nv.item, nv.group_name, nv.duration, nv.sort, nv.legacy)
    FROM   new_values nv
    WHERE  nv.id = m.id
    RETURNING m.id
)
INSERT INTO playlist_items (playlist, item, group_name, duration, sort, legacy)
SELECT playlist, item, group_name, duration, sort, legacy
FROM   new_values nv
WHERE  NOT EXISTS (SELECT 1
                   FROM   upsert m
                   WHERE  nv.id = m.id)
RETURNING id

그래서 나는 두 번째 VALUES 행에 대한 기본 값을 적용하려면 기존 컬럼에 대한 예를 들어 싶습니다.

열팽창 계수가가에 삽입 무엇을 모르고있다 때문에. 그런 일을하지 않는 값 목록에 명시 적으로 사용하여 기본값으로, 몇 가지를 시도했습니다 나는 또한 삽입 문에서 유착을 (COL, DEFAULT) 시도했습니다있는 어느 작동하지 않았다. 그래서, 내가 원하는 것을 할 수 있습니까?

해결법

  1. ==============================

    1.포스트 그레스 9.5 UPSERT을 구현했습니다. 아래를 참조하십시오.

    포스트 그레스 9.5 UPSERT을 구현했습니다. 아래를 참조하십시오.

    이 까다로운 문제입니다. 당신은 (문서 당)이 제한에 실행 :

    굵게 강조 광산. 기본 값에 삽입 할 테이블없이 정의되지 않습니다. 그래서 귀하의 질문에 대한 직접적인 해결책이 없지만, 정확한 요구 사항에 따라 가능한 대체 경로의 수는있다.

    @Patrick 댓글을 달았 또는 INFORMATION_SCHEMA.COLUMNS에서처럼 당신은 시스템 카탈로그 pg_attrdef에서 사람들을 가져올 수 있습니다. 전체 지침 여기 :

    그러나 당신은 여전히 ​​기본값을 요리하는 식의 텍스트 표현으로 행의 목록을 가지고있다. 당신과 함께 작업에 값을 얻기 위해 동적으로 문을 구축하고 실행해야합니다. 지루한와 지저분한. 대신, 우리는 우리를 위해 그렇게 내장 된 포스트 그레스 기능하도록 할 수 있습니다 :

    이 생성 된 기본값을 사용 반환 더미 행을 삽입하고 있습니다 :

    INSERT INTO playlist_items DEFAULT VALUES RETURNING *;

    두 더 많은 문제가 해결 될 수있다 :

    복제가로 열 기본값 및 삽입을 포함한 임시 테이블 :

    BEGIN;
CREATE TEMP TABLE tmp_playlist_items (LIKE playlist_items INCLUDING DEFAULTS)
   ON COMMIT DROP;  -- drop at end of transaction

INSERT INTO tmp_playlist_items DEFAULT VALUES RETURNING *;
...

    동일한 결과, 부작용. 기본 표현이 그대로 복사되어 있기 때문에, 복제가있는 경우 같은 시퀀스에서 그립니다. 그러나 다른 측면 원치 않는 행의 효과 또는 트리거는 완전히 피할 수있다.

    아이디어에 대한 이고르 신용 :

    당신 때문에 (문서 당), NOT NULL 컬럼에 대해 더미 값을 제공해야합니다 :

    어느 INSERT 문에서 그 또는 (더 나은) 제약 조건을 제거하기위한 수용 :

    ALTER TABLE tmp_playlist_items
   ALTER COLUMN foo DROP NOT NULL
 , ALTER COLUMN bar DROP NOT NULL;

    수퍼 유저 권한을 가진 신속하고 더러운 방법이 있습니다 :

    UPDATE pg_attribute
SET    attnotnull = FALSE
WHERE  attrelid = 'tmp_playlist_items'::regclass
AND    attnotnull
AND    attnum > 0;

    그것은 어떤 데이터와 다른 목적을 가진 다만 임시 테이블이며, 그것은 트랜잭션의 끝에서 떨어진 것. 그래서 바로 가기 유혹한다. 하지만 기본적인 규칙은 : 직접 시스템 카탈로그 함부로 변경하지 마십시오.

    그래서, 깨끗한 방식으로의 모습을 보자 : DO가 문에서 동적 SQL과 자동화합니다. 당신은 당신이 같은 역할은 임시 테이블을 생성하기 때문에이 보장되는 정규 권한이 필요합니다.

    DO $$BEGIN
EXECUTE (
   SELECT 'ALTER TABLE tmp_playlist_items ALTER '
       || string_agg(quote_ident(attname), ' DROP NOT NULL, ALTER ')
       || ' DROP NOT NULL'
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = 'tmp_playlist_items'::regclass
   AND    attnotnull
   AND    attnum > 0
   );
END$$

    많은 청소기 여전히 매우 빠르게. 동적 명령으로 관리를 실행하고 SQL 주입 조심. 이 문장은 안전합니다. 좀 더 설명과 함께 여러 관련 답변을 게시했다.

    BEGIN;

CREATE TEMP TABLE tmp_playlist_items
   (LIKE playlist_items INCLUDING DEFAULTS) ON COMMIT DROP;

DO $$BEGIN
EXECUTE (
   SELECT 'ALTER TABLE tmp_playlist_items ALTER '
       || string_agg(quote_ident(attname), ' DROP NOT NULL, ALTER ')
       || ' DROP NOT NULL'
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = 'tmp_playlist_items'::regclass
   AND    attnotnull
   AND    attnum > 0
   );
END$$;

LOCK TABLE playlist_items IN EXCLUSIVE MODE;  -- forbid concurrent writes

WITH default_row AS (
   INSERT INTO tmp_playlist_items DEFAULT VALUES RETURNING *
   )
, new_values (id, playlist, item, group_name, duration, sort, legacy) AS (
   VALUES
      (651, 21, 30012, 'a', 30, 1, FALSE)
    , (NULL, 21, 1, 'b', 34, 2, NULL)
    , (668, 21, 30012, 'c', 30, 3, FALSE)
    , (7428, 21, 23068, 'd', 0, 4, FALSE)
   )
, upsert AS (  -- *not* replacing existing values in UPDATE (?)
   UPDATE playlist_items m
   SET   (  playlist,   item,   group_name,   duration,   sort,   legacy)
       = (n.playlist, n.item, n.group_name, n.duration, n.sort, n.legacy)
   --                                   ..., COALESCE(n.legacy, m.legacy)  -- see below
   FROM   new_values n
   WHERE  n.id = m.id
   RETURNING m.id
   )
INSERT INTO playlist_items
        (playlist,   item,   group_name,   duration,   sort, legacy)
SELECT n.playlist, n.item, n.group_name, n.duration, n.sort
                                   , COALESCE(n.legacy, d.legacy)
FROM   new_values n, default_row d   -- single row can be cross-joined
WHERE  NOT EXISTS (SELECT 1 FROM upsert u WHERE u.id = n.id)
RETURNING id;

COMMIT;

    같은 테이블에 쓰기를 시도하는 동시 트랜잭션이있는 경우에만 LOCK이 필요합니다.

    요청한 바와 같이, 이것은 단지 INSERT 케이스 입력 행 내의 열 기존의 NULL 값을 대체한다. 쉽게 다른 열 또는 업데이트하는 경우에 잘 작동 할 수 있도록 확장 할 수 있습니다. 예를 들어, 당신은뿐만 아니라 조건 UPDATE 수 : 입력 값이 NULL이 아닌 경우에만. 나는 위의 UPDATE에 주석 라인을 추가했다.

    제외 : 유형은 첫 번째 행에서 파생되기 때문에 당신은 어떤 행하지만 VALUES 표현에서 처음으로 주조 값이 필요하지 않습니다.

    갈등 INSERT와 구현 UPSERT .. .. DO 아무것도 | 최신 정보. 이것은 주로 작업을 단순화 :

    INSERT INTO playlist_items AS m (id, playlist, item, group_name, duration, sort, legacy)
VALUES (651, 21, 30012, 'a', 30, 1, FALSE)
,      (DEFAULT, 21, 1, 'b', 34, 2, DEFAULT)  -- !
,      (668, 21, 30012, 'c', 30, 3, FALSE)
,      (7428, 21, 23068, 'd', 0, 4, FALSE)
ON CONFLICT (id) DO UPDATE
SET (playlist, item, group_name, duration, sort, legacy)
 = (EXCLUDED.playlist, EXCLUDED.item, EXCLUDED.group_name
  , EXCLUDED.duration, EXCLUDED.sort, EXCLUDED.legacy)
-- (...,  COALESCE(l.legacy, EXCLUDED.legacy))  -- see below
RETURNING m.id;

    우리는 DEFAULT 키워드를 허용하는 직접 삽입 할 값 절을 첨부 할 수 있습니다. (ID)에서 고유 위반의 경우, 포스트 그레스 대신 업데이트합니다. 우리는 UPDATE에서 제외 행을 사용할 수 있습니다. 수동 :

    과:

    당신은 UPDATE에 대한 다양한 옵션이 있습니다 : 당신은 할 수 ...

    그러나 실제로 INSERT에서 제공하는 분별 DEFAULT 값과 값 방법이 없습니다. 만 배제 결과적 행을 볼 수 있습니다. 당신은 구별해야하는 경우, 다시 당신이 우리의 처분에 모두를 이전 솔루션에 떨어진다.

    2. from https://stackoverflow.com/questions/23794405/generate-default-values-in-a-cte-upsert-using-postgresql-9-3 by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 정규 표현식은 쿼리의 모든 테이블 이름을 찾을 수  (0) 2020.06.15
[SQL] 앱 엔진 자바 서블릿은 클라우드 SQL에 연결되지 않습니다  (0) 2020.06.14
[SQL] 그 조각하여 문자열의 조각 순서를 선택 MYSQL  (0) 2020.06.14
[SQL] SQL은 where 절에 열 별칭을 인식하지  (0) 2020.06.14
[SQL] 문자열로 안전 SQL 문 만들기  (0) 2020.06.14
,

티스토리툴바