One INSERT with multiple SELECT

Question

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?

Seductress

Please always declare your version of Postgres. Providing your current code is very helpful, but there should also be a plain English description of what you are trying to achieve. And table definitions should be there, too. – Talmudist

Answer 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

Talmudist

It's not a problem in my design: phrase = sentence in english. All the words are sorted into groups many categories and i need to generate sentences based only on the "groups of categories" (to generate funny sentences). but sometimes there's only one word left "alone" so I need a sentence = phrase that has only the "groups of categories" this word belongs to, so I can still make something with this word. – Seductress

This syntax is very new to me, and I feel like it's very powerful, but it's overkill when you want to do such simple daily operations. Thank you very much indeed for your help! – Seductress

Looking at the various possible SQL statements, it seems to be the simplest possible way to me - other advantages aside. – Talmudist

@ErwinBrandstetter I've started actually adding the word "erwin" to my searches for pgsql questions, so I get a good answer. – Bedfellow

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

Apatetic

Answer 3

I have just tried the following statement in PG and it worked.

INSERT INTO main_phrase (phrase_id, groupe_categories_id)
 SELECT
     (SELECT id FROM main_phrase WHERE description='Mot commun féminin pluriel animaux'),
     (SELECT id FROM main_groupecategories WHERE description='Mot commun féminin pluriel animaux');

Loup