3

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?

Community
  • 1
  • 1
Olivier Pons
  • 15,363
  • 26
  • 117
  • 213
  • 1
    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. – Erwin Brandstetter Jan 09 '16 at 03:25

3 Answers3

10

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;
Olivier Pons
  • 15,363
  • 26
  • 117
  • 213
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
5

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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. – Olivier Pons Jan 09 '16 at 07:57
  • 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! – Olivier Pons Jan 09 '16 at 08:16
  • Looking at the various possible SQL statements, it seems to be the *simplest* possible way to me - other advantages aside. – Erwin Brandstetter Jan 09 '16 at 17:50
  • 1
    @ErwinBrandstetter I've started actually adding the word "erwin" to my searches for pgsql questions, so I get a good answer. – grantwparks Jan 25 '20 at 19:11
1

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');
leocborges
  • 4,799
  • 5
  • 32
  • 38