Inserisci i dati in 3 tabelle alla volta utilizzando Postgres

Voglio inserire i dati in 3 tabelle con una singola query.
I miei tavoli hanno il seguente aspetto:

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

Riceverò una chiave in cambio di ogni inserimento e ho bisogno di inserire quella chiave nella tabella successiva.
La mia domanda è:

 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; 

Ma se eseguo query singole, mi restituiscono i valori e non posso riutilizzarli immediatamente nella query successiva.

Come ottenere questo?

Utilizza le CTE che modificano i dati :

 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; 

Ogni inserto dipende da quello precedente. SELECT posto di VALUES garantisce che nulla venga inserito nelle tabelle sussidiarie se non viene restituita alcuna riga dall’inserimento precedente. (Correlato: la clausola ON CONFLICT in Postgres 9.5+)
È anche un po ‘più breve e più veloce in quel modo.


In genere, è più conveniente fornire righe di dati complete in un’unica posizione :

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

Potresti aver bisogno di cast di tipo esplicito in un’espressione VALUES separata (al contrario di un’espressione VALUES associata a un INSERT, in cui i tipi di dati sono derivati ​​dalla tabella di destinazione.

Se più righe possono essere identiche (firstname, lastname) , potrebbe essere necessario piegare i duplicati per il primo inserimento:

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

È ansible utilizzare una tabella (temporanea) come origine dati anziché i data CTE.

Relativo, con più dettagli:

  • Come usare RETURNING con ON CONFLICT in PostgreSQL?
  • SELEZIONA o INSERISCI in una funzione soggetta a condizioni di gara?

Qualcosa come questo

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

Poiché l’id generato dall’inserimento in sample2 non è necessario, ho rimosso la clausola di returning dall’ultimo inserimento.

In genere, dovresti utilizzare una transazione per evitare di scrivere query complicate.

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

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

Potresti anche usare un CTE, assumendo che il tuo tag Postgres sia corretto. Per esempio:

 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; 

È ansible creare un trigger after-insert nella tabella Sample da inserire nelle altre due tabelle.

L’unico problema che vedo con questo è che non si ha un modo di inserire i file aggiunti che sarà sempre vuoto o in questo caso ss. Non c’è modo di inserire una colonna in un campione che non sia effettivamente nella tabella di esempio in modo da non poterla inviare insieme all’inserto innital.

Un’altra opzione sarebbe quella di creare una stored procedure per eseguire i tuoi inserti.

Hai la domanda su mysql e postgressql di quale database stiamo parlando qui?