Insérez des lignes dans plusieurs tables dans une seule requête, en sélectionnant à partir d'une table impliquée

j'ai deux tables de la forme suivante (i.e., chaque foo est lié à exactement une barre).

CREATE TABLE foo (
    id INTEGER PRIMARY KEY,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    ...,
    bar_id INTEGER UNIQUE NOT NULL,
    FOREIGN key (bar_id) REFERENCES bar(id)
);

CREATE TABLE bar (
    id INTEGER PRIMARY KEY,
    z INTEGER NOT NULL,
    ...
);

Il est facile de copier les lignes foo qui remplissent une condition particulière en utilisant une requête imbriquée:

INSERT INTO foo (...) (SELECT ... FROM foo WHERE ...)

Mais je ne peux pas comprendre comment faire une copie de la ligne correspondante dans la bar pour chaque ligne foo et insérez l'id de bar dans la foo ligne. Est-il possible de le faire en une seule requête?

exemple concret d'un produit souhaité résultat:

-- Before query:

foo(id=1,x=3,y=4,bar_id=100)  .....  bar(id=100,z=7)
foo(id=2,x=9,y=6,bar_id=101)  .....  bar(id=101,z=16)
foo(id=3,x=18,y=0,bar_id=102) .....  bar(id=102,z=21)


-- Query copies all pairs of foo/bar rows for which x>3:

-- Originals
foo(id=1,x=3,y=4,bar_id=101)  .....  bar(id=101,z=7)
foo(id=2,x=9,y=6,bar_id=102)  .....  bar(id=102,z=16)
foo(id=3,x=18,y=0,bar_id=103) .....  bar(id=103,z=21)

-- "Copies" of foo(id=2,...) and foo(id=3,...), with matching copies of
-- bar(id=102,...) and bar(id=103,...)
foo(id=4,x=9,y=6,bar_id=104)  .....  bar(id=104,z=16)
foo(id=5,x=18,y=0,bar_id=105) .....  bar(id=105,z=21)
17
demandé sur foldl 2012-05-06 19:30:19

2 réponses

version finale

... après quelques infos de l'OP. Considérer cette démo:

-- DROP TABLE foo; DROP TABLE bar;

CREATE TEMP TABLE bar (
 id serial PRIMARY KEY  -- using a serial column!
,z  integer NOT NULL
);

CREATE TEMP TABLE foo (
 id     serial PRIMARY KEY  -- using a serial column!
,x      integer NOT NULL
,y      integer NOT NULL
,bar_id integer UNIQUE NOT NULL REFERENCES bar(id)
);

Insérer des valeurs - bar en premier.

Il serait très utile si vous avez fourni des données de test dans votre question comme ça!

INSERT INTO bar (id,z) VALUES
 (100, 7)
,(101,16)
,(102,21);

INSERT INTO foo (id, x, y, bar_id) VALUES
 (1, 3,4,100)
,(2, 9,6,101)
,(3,18,0,102);

définissez les séquences aux valeurs actuelles ou nous obtenons des violations des clés dupliquées:

SELECT setval('foo_id_seq', 3);
SELECT setval('bar_id_seq', 102);

Vérifie:

-- SELECT nextval('foo_id_seq')
-- SELECT nextval('bar_id_seq')
-- SELECT * from bar;
-- SELECT * from foo;

Requête:

WITH a AS (
    SELECT f.x, f.y, bar_id, b.z
    FROM   foo f
    JOIN   bar b ON b.id = f.bar_id
    WHERE  x > 3
    ),b AS (
    INSERT INTO bar (z)
    SELECT z
    FROM   a
    RETURNING z, id AS bar_id
    )
INSERT INTO foo (x, y, bar_id)
SELECT a.x, a.y, b.bar_id
FROM   a
JOIN   b USING (z);

cela devrait faire ce que votre dernière mise à jour décrit.

La requête suppose que zUNIQUE. Si z n'est pas unique, il devient plus complexe. Reportez-vous à Question 2 dans cette réponse connexe pour une solution prête à l'emploi à l'aide de la fenêtre de la fonction row_number() dans ce cas.

aussi, envisager de remplacer le 1:1 rapport entre foo et bar avec une seule table unie.


modification des données CTE

deuxième réponse après plus d'informations.

Si vous voulez ajouter des lignes à fooetbar dans une seule requête, vous pouvez utiliser un données de la modification de la CTE depuis PostgreSQL 9.1:

WITH x AS (
    INSERT INTO bar (col1, col2)
    SELECT f.col1, f.col2
    FROM   foo f
    WHERE  f.id BETWEEN 12 AND 23 -- some filter
    RETURNING col1, col2, bar_id  -- assuming bar_id is a serial column
    )
INSERT INTO foo (col1, col2, bar_id)
SELECT col1, col2, bar_id
FROM   x;

je dessine des valeurs de foo, insérez-les dans les bar, faites-les retourner avec un et insérez en foo. Vous pouvez utiliser d'autres données, trop.

Voici un démo de travail pour jouer avec sur sqlfiddle.


notions de base

réponse originale avec information de base avant clarifications.

La forme de base est:

INSERT INTO foo (...)
SELECT ... FROM foo WHERE ...

aucune parenthèse n'est nécessaire. Vous pouvez faire la même chose avec n'importe quelle table

INSERT INTO foo (...)
SELECT ... FROM bar WHERE ...

Et vous pouvez vous joindre à la table que vous insérez dans le SELECT:

INSERT INTO foo (...)
SELECT f.col1, f.col2, .. , b.bar_id
FROM   foo f
JOIN   bar b USING (foo_id);  -- present in foo and bar

c'est juste un SELECT comme les autres - le tableau que vous insérez dans. Les lignes sont d'abord lues, puis insérées.

28
répondu Erwin Brandstetter 2017-05-23 12:02:43

si idbar sont en série et ont une valeur par défaut nextval('bar_id_seq'::regclass) vous pouvez appeler manuellement cette fonction pour obtenir de nouvelles identifications dans cte

with
s_bar as (
  SELECT id, z, nextval('bar_id_seq'::regclass) new_id
  FROM   bar
  WHERE  ...
),
s_foo as (
  SELECT x, y, bar_id
  FROM   foo
  WHERE  ...
),
i_bar as (
  INSERT INTO bar (id, z)
  SELECT new_id, z
  FROM   s_bar
),
i_foo as (
  INSERT INTO foo (x, y, bar_id)
  SELECT f.x, f.y, b.new_id
  FROM   s_foo f
  JOIN   s_bar b on b.id = f.bar_id
)
SELECT 1
0
répondu Matveev Dmitriy 2018-03-23 09:58:25