[sql] Postgres를 사용하여 한 번에 3 개의 테이블에 데이터 삽입

Program / 글쓴이

단일 쿼리로 3 개의 테이블에 데이터를 삽입하고 싶습니다.
내 테이블은 다음과 같습니다.

CREATE TABLE sample (
   id        bigserial PRIMARY KEY,
   lastname  varchar(20),
   firstname varchar(20)
);

CREATE TABLE sample1(
   user_id    bigserial PRIMARY KEY,
   sample_id  bigint REFERENCES sample,
   adddetails varchar(20)
);

CREATE TABLE sample2(
   id      bigserial PRIMARY KEY,
   user_id bigint REFERENCES sample1,
   value   varchar(10)
);

모든 삽입에 대한 대가로 키를 받게되며이 키를 다음 테이블에 삽입해야합니다.
내 질문은 다음과 같습니다.

insert into sample(firstname,lastname) values('fai55','shaggk') RETURNING id;
insert into sample1(sample_id, adddetails) values($id,'ss') RETURNING user_id;
insert into sample2(user_id, value) values($id,'ss') RETURNING id;

그러나 단일 쿼리를 실행하면 값을 반환하고 다음 쿼리에서 즉시 재사용 할 수 없습니다.

이것을 달성하는 방법?

답변

사용 열팽창 계수를 데이터 – 수정 :

WITH ins1 AS (
   INSERT INTO sample(firstname, lastname)
   VALUES ('fai55', 'shaggk')
-- ON     CONFLICT DO NOTHING         -- optional addition in Postgres 9.5+
   RETURNING id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT sample_id, 'ss' FROM ins1
   RETURNING user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT user_id, 'ss2' FROM ins2;

각각 INSERT은 이전에 의존합니다. SELECT대신 VALUES이전에서 반환 된 행이없는 경우 보조 테이블에 아무것도 삽입되지 않도록합니다 INSERT. (Postgres 9.5 이상부터는를 추가 할 수 있습니다 ON CONFLICT.)
이 방식으로도 조금 더 짧고 빠릅니다.

일반적으로 전체 데이터 행을 한 곳에서 제공하는 것이 더 편리합니다 .

WITH data(firstname, lastname, adddetails, value) AS (
   VALUES                              -- provide data here
      ('fai55', 'shaggk', 'ss', 'ss2') -- see below
    , ('fai56', 'XXaggk', 'xx', 'xx2') -- works for multiple input rows
       --  more?                      
   )
, ins1 AS (
   INSERT INTO sample (firstname, lastname)
   SELECT firstname, lastname          -- DISTINCT? see below
   FROM   data
   -- ON     CONFLICT DO NOTHING       -- UNIQUE constraint? see below
   RETURNING firstname, lastname, id AS sample_id
   )
, ins2 AS (
   INSERT INTO sample1 (sample_id, adddetails)
   SELECT ins1.sample_id, d.adddetails
   FROM   data d
   JOIN   ins1 USING (firstname, lastname)
   RETURNING sample_id, user_id
   )
INSERT INTO sample2 (user_id, value)
SELECT ins2.user_id, d.value
FROM   data d
JOIN   ins1 USING (firstname, lastname)
JOIN   ins2 USING (sample_id);

db <> 여기에 바이올린

데이터 유형이 대상 테이블에서 파생 된 where에 첨부 된 표현식 VALUES과 달리 독립형 표현식 에서 명시 적 유형 캐스트가 필요할 수 있습니다 . 보다:VALUESINSERT

여러 행이 동일하게 올 수있는 경우 (firstname, lastname)첫 번째에 대해 중복을 접어야 할 수 있습니다 INSERT.

...
INSERT INTO sample (firstname, lastname)
SELECT DISTINCT firstname, lastname FROM data
...

CTE 대신 (임시) 테이블을 데이터 소스로 사용할 수 있습니다 data.

(firstname, lastname)이를 테이블 의 UNIQUE 제약 조건 과 ON CONFLICT쿼리 의 절 과 결합하는 것이 좋습니다.

관련 :

답변

이 같은

with first_insert as (
   insert into sample(firstname,lastname)
   values('fai55','shaggk')
   RETURNING id
),
second_insert as (
  insert into sample1( id ,adddetails)
  values
  ( (select id from first_insert), 'ss')
  RETURNING user_id
)
insert into sample2 ( id ,adddetails)
values
( (select user_id from first_insert), 'ss');

삽입에서 생성 된 ID sample2가 필요하지 않으므로 returning마지막 삽입에서 절을 제거했습니다 .

답변

일반적으로 복잡한 쿼리 작성을 피하기 위해 트랜잭션을 사용합니다.

http://www.postgresql.org/docs/current/static/sql-begin.html

http://dev.mysql.com/doc/refman/5.7/en/commit.html

Postgres 태그가 정확하다고 가정하면 CTE를 사용할 수도 있습니다. 예를 들면 :

with sample_ids as (
  insert into sample(firstname, lastname)
  values('fai55','shaggk')
  RETURNING id
), sample1_ids as (
  insert into sample1(id, adddetails)
  select id,'ss'
  from sample_ids
  RETURNING id, user_id
)
insert into sample2(id, user_id, value)
select id, user_id, 'val'
from sample1_ids
RETURNING id, user_id;

답변

샘플 테이블에 삽입 후 트리거를 만들어 다른 두 테이블에 삽입 할 수 있습니다.

이 작업을 할 때 내가 보는 유일한 문제는 adddetails를 삽입하는 방법이 없다는 것입니다. 항상 비어 있거나이 경우에는 ss입니다. 샘플 테이블에 실제로 있지 않은 열을 샘플에 삽입하는 방법이 없으므로 초기 삽입과 함께 보낼 수 없습니다.

또 다른 옵션은 삽입을 실행하는 저장 프로 시저를 만드는 것입니다.

mysql 및 postgressql 태그가 지정된 질문이 있습니다. 여기서 우리가 말하는 데이터베이스는 무엇입니까?

답변