[SQL] 데이터와 포스트 그레스와 세트 외래 키 삽입

데이터와 포스트 그레스와 세트 외래 키 삽입

나는 스키마 변경 후 포스트 그레스 DB에 기존의 많은 양의 데이터를 마이그레이션해야합니다.

이전 스키마에서 국가 속성은 사용자 테이블에 저장된다. 이제 국가의 속성은 별도의 주소 테이블로 이동되었습니다 :

users:
  country # OLD
  address_id # NEW [1:1 relation]

addresses:
  id
  country

스키마는 실제로 더 복잡하고 주소는 국가 이상이 포함되어 있습니다. 따라서, 모든 사용자는 자신의 주소 (: 1 개의 관계 1)이 있어야합니다.

데이터를 마이그레이션 할 때, 나는 주소를 삽입 한 후 사용자 테이블의 외래 키를 설정하는 데 문제가 있습니다 :

INSERT INTO addresses (country) 
    SELECT country FROM users WHERE address_id IS NULL 
    RETURNING id;

어떻게 사용자 테이블의 외래 키 참조를 삽입 된 행의 ID를 전파 설정합니까?

지금까지 주소 테이블에 임시 USER_ID 컬럼을 만든 다음하여 address_id를 업데이트와 함께 올 수있는 유일한 솔루션 :

UPDATE users SET address_id = a.id FROM addresses AS a 
    WHERE users.id = a.user_id;

그러나,이 (users.id와 addresses.user_id 모두 인덱스를 사용에도 불구하고) 매우 느린 것으로 밝혀졌다.

사용자 테이블은 300K는 관련 주소 누락 된 3 백만에 대한 행이 포함되어 있습니다.

다른 하나 개의 테이블로 유도 된 데이터를 삽입하는 방법 및 (스키마 그 자체를 변경하지 않고) 나머지에 삽입 된 데이터에 외부 키 설정 기준이 있는가?

나는 포스트 그레스 8.3.14을 사용하고 있습니다.

감사

지금은 파이썬 / SQLAlchemy의 스크립트로 데이터를 마이그레이션하여 문제를 해결했다. 그것은 SQL과 같은 것보다 (나를 위해) 훨씬 쉽게 밝혀졌다. 누군가가 포스트 그레스 SQL의 INSERT 문의 복귀 결과를 처리하는 방법을 알고 있다면 그래도, 내가 관심이있을 것입니다.

해결법

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

   1.테이블 사용자는 공개하지 않았다 몇 가지 기본 키가 있어야합니다. 이 답변의 목적을 위해 나는 그것을 users_id 이름을 것입니다.

   테이블 사용자는 공개하지 않았다 몇 가지 기본 키가 있어야합니다. 이 답변의 목적을 위해 나는 그것을 users_id 이름을 것입니다.

   당신은 PostgreSQL의 9.1에 도입 된 데이터 수정 열팽창 계수에 오히려 우아하게이 문제를 해결 할 수 있습니다 :

   우리 나라 고유 있다고 가정 할 수있는 경우, 전체 작업은 오히려 간단하다 :

   WITH i AS (
       INSERT INTO addresses (country) 
       SELECT country
       FROM   users
       WHERE  address_id IS NULL 
       RETURNING id, country
       )
   UPDATE users u
   SET    address_id = i.id
   FROM   i
   WHERE  i.country = u.country;
   

   당신은 당신의 질문에 버전 8.3을 언급. 당신이 그 동안 업그레이드 주위하지 않은 경우 업그레이드를 고려하는 것이 좋습니다. 삶의 끝 8.3를 위해 곧 제공 될 예정입니다.

   그것은 수도와 같은 것으로,이 버전 8.3와 간단한 충분히입니다합니다. 당신은 두 문장이 필요합니다 :

   INSERT INTO addresses (country) 
   SELECT country
   FROM   users
   WHERE  address_id IS NULL;
   
   UPDATE users u
   SET    address_id = a.id
   FROM   addresses a
   WHERE  address_id IS NULL 
   AND    a.country = u.country;
   

   국가가 고유하지 않으면, 그것은 더 많은 도전이됩니다. 당신은 하나 개의 주소를 작성하고 여러 번 연결 할 수있다. 그 규칙 같은 편리한 솔루션 밖으로 1 관계 :하지만 당신은 하나를 언급했다.

   버전 9.1의 경우 :

   WITH s AS (
       SELECT users_id, country
            , row_number() OVER (PARTITION BY country) AS rn
       FROM   users
       WHERE  address_id IS NULL 
       )
       , i AS (
       INSERT INTO addresses (country) 
       SELECT country
       FROM   s
       RETURNING id, country
       )
       , r AS (
       SELECT *
            , row_number() OVER (PARTITION BY country) AS rn
       FROM   i
       )
   UPDATE users u
   SET    address_id = r.id
   FROM   r
   JOIN   s USING (country, rn)    -- select exactly one id for every user
   WHERE  u.users_id = s.users_id
   AND    u.address_id IS NULL;
   

   명확하게 동일한 국가와 세트의 모든 사용자에게 INSERT에서 반환 정확히 하나 개의 ID를 할당 할 수있는 방법이 없기 때문에, 나는 그들 고유 수 있도록 윈도우 기능 ROW_NUMBER ()를 사용합니다.

   하지 버전 8.3와 정직 등. 한 가지 가능한 방법 :

   INSERT INTO addresses (country) 
   SELECT DISTINCT country -- pick just one per set of dupes
   FROM   users
   WHERE  address_id IS NULL;
   
   UPDATE users u
   SET    address_id = a.id
   FROM   addresses a
   WHERE  a.country = u.country
   AND    u.address_id IS NULL
   AND NOT EXISTS (
       SELECT * FROM addresses b
       WHERE  b.country = a.country
       AND    b.users_id < a.users_id
       ); -- effectively picking the smallest users_id per set of dupes
   

   마지막 NULL 값이 users.address_id에서 사라 될 때까지이 과정을 반복합니다.

from https://stackoverflow.com/questions/7391090/insert-data-and-set-foreign-keys-with-postgres by cc-by-sa and MIT license