One INSERT with multiple SELECT

问题:

I've already read this, this and this, but I cant make this SQL work:

INSERT INTO main_phrase (description) VALUES ('Mot commun féminin pluriel animaux');
/* ERROR: */
WITH
  t1 AS (
    SELECT id 
    FROM main_phrase 
    WHERE description='Mot commun féminin pluriel animaux'
  ),
  t2 AS (
    SELECT id 
    FROM main_groupecategories 
    WHERE description='Mot commun féminin pluriel animaux'
  )
INSERT 
  INTO main_phrasegroupecategories (phrase_id, groupe_categories_id)
  VALUES                           (t1.id,     t2.id);

I get:

ERROR: missing entry for the clause FROM for table t1

What am I missing?

回答1:

Assumptions

• You want to link the newly inserted row in main_phrase to the row(s) in main_groupecategories with the same description.
• main_phrase.id is a serial column.

Explanation for Error

You cannot refer to any tables (including CTE) in a free-standing VALUES expression, you would have to use SELECT with a FROM clause. But there is a better solution. See below.

Better Query

Use a data-modifying CTE instead to make the whole operation shorter, safer and faster:

WITH p AS (
   INSERT INTO main_phrase (description)
   VALUES ('Mot commun féminin pluriel animaux')  -- provide description once
   RETURNING id, description  -- and reuse it further down
   )
INSERT INTO main_phrasegroupecategories (phrase_id, groupe_categories_id)
SELECT p.id, g.id
FROM   p
JOIN   main_groupecategories g USING (description);

If you want to use any values of the new rows, have them returned immediately with another RETURNING clause to the second INSERT.

Why would you have the same description redundantly in both tables of your (presumed) many-to-many relationship? Might be a problem in your database design.

Related:

• PostgreSQL multi INSERT...RETURNING with multiple columns
• SELECT * FROM NEW TABLE equivalent in Postgres
• Combining INSERT statements in a data-modifying CTE with a CASE expression

回答2:

Try this:

INSERT INTO main_phrase (phrase_id, groupe_categories_id) 
WITH
  t1 AS (
    SELECT id 
    FROM main_phrase 
    WHERE description='Mot commun féminin pluriel animaux'
  ),
  t2 AS (
    SELECT id 
    FROM main_groupecategories 
    WHERE description='Mot commun féminin pluriel animaux'
  )
  select t1.id, t2.id
  from t1,t2;