tablas - insertar varios registros en postgresql

Insertar datos en 3 tablas a la vez usando Postgres

Question

Quiero insertar datos en 3 tablas con una sola consulta.
Mis tablas se ven a continuación:

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)
);

Obtendré una clave a cambio de cada inserción y necesito insertar esa clave en la siguiente tabla.
Mi consulta es:

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;

Pero si ejecuto consultas únicas, simplemente me devuelven los valores y no puedo reutilizarlos en la próxima consulta de inmediato.

¿Cómo lograr esto?

Answer 1

Algo como esto

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'');

Como el ID generado desde el inserto en la sample2 no es necesario, sample2 la cláusula de returning de la última inserción.

Answer 2

Normalmente, usaría una transacción para evitar escribir consultas complicadas.

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

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

También podría usar un CTE, suponiendo que su etiqueta de Postgres es correcta. Por ejemplo:

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;

Answer 3

Puede crear un desencadenador de inserción posterior en la tabla de muestra para insertarlo en las otras dos tablas.

El único problema que veo al hacer esto es que no tendrás una forma de insertar detalles, siempre estará vacío o en este caso ss. No hay forma de insertar una columna en la muestra que no esté realmente en la tabla de muestra, por lo que no puede enviarla junto con la inserción indirecta.

Otra opción sería crear un procedimiento almacenado para ejecutar sus inserciones.

Usted tiene la pregunta taged mysql y postgressql ¿de qué base de datos estamos hablando aquí?

Answer 4

Use CTE modificadores de datos :

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

Cada inserción depende de la anterior. SELECT lugar de VALUES asegura que no se inserte nada en las tablas subsidiarias si no se devuelve ninguna fila de la inserción anterior. (Relacionado: la cláusula ON CONFLICT en Postgres 9.5+)
También es un poco más corto y más rápido de esa manera.

Normalmente, es más conveniente proporcionar filas de datos completas en un solo lugar :

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

Es posible que necesite conversiones de tipo explícitas en una expresión VALUES separada (en oposición a una expresión VALUES asociada a un INSERT, donde los tipos de datos se derivan de la tabla de destino.

Si pueden aparecer varias filas con idéntico (firstname, lastname) , es posible que deba doblar los duplicados para la primera inserción:

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

Puede usar una tabla (temporal) como origen de datos en lugar de los data CTE.

Relacionado, con más detalles:

• ¿Cómo se usa RETURNING con ON CONFLICT en PostgreSQL?
• ¿SELECCIONAR o INSERTAR en una función es propenso a las condiciones de carrera?