INSERT into three tables with many-to-many from one table

Question

With Postgres 9.5 using a trivial example. Given I have this data:

Table everything:

| fruit | country |
|--------|---------|
| Banana | USA |
| Banana | Panama |

How would I create these tables?:

Table fruit:

| fruit_id | name |
|----------|--------|
| 1 | Banana |

Table country:

| country_id | name |
|------------|--------|
| 1 | USA |
| 2 | Panama |

Table fruit_country:

| fruit_id | country_id |
|----------|------------|
| 1 | 1 |
| 1 | 2 |

As an initial pass I hoped I could do something like this:

WITH fruit_ids AS (
 INSERT INTO fruit(name) (
 SELECT (fruit) FROM everything
 )
 ON CONFLICT DO NOTHING
 RETURNING fruit_id
),
country_ids AS (
 INSERT INTO country(name) (
 SELECT(country) FROM everything
 )
 RETURNING country_id
)
INSERT INTO country_fruit(fruit_id, country_id) (
 SELECT
 fruit_id,
 (SELECT country_id FROM country_ids)
 FROM fruit_ids
);

But no dice:

ERROR: more than one row returned by a subquery used as an expression

Answer 1

The immediate cause for the error message is your abuse of parentheses. But there is more:

Data-modifying CTEs are the wrong tool for the use case. The UPSERT (ON CONFLICT DO NOTHING) indicates that some of the fruits might already exist in table fruit (or its use would be nonsense). Hence, some of the fruits might not be included in the set returned with RETURNING.

Use simple, separate INSERT commands instead. If you need the whole operation to be atomic (all or nothing), wrap it into a transaction.

Also, (削除) name (削除ここまで) is not a good name for a column. I use fruit for fruits and country for countries instead. Avoids naming conflicts, and happens to simplify the query a bit by allowing the (totally optional) USING clause for the join condition.

Your tables can look like this: (If that was even part of the question?)

CREATE TABLE fruit (
 fruit_id serial PRIMARY KEY
 , fruit text UNIQUE
);
CREATE TABLE country (
 country_id serial PRIMARY KEY
 , country text UNIQUE
);
CREATE TABLE fruit_country (
 fruit_id int REFERENCES fruit
 , country_id int REFERENCES country
 , PRIMARY KEY (fruit_id, country_id)
);

Detailed instructions:

• How to implement a many-to-many relationship in PostgreSQL?

To INSERT data from everything:

BEGIN;
INSERT INTO fruit (fruit)
SELECT DISTINCT fruit -- must be distinct!
FROM everything
ON CONFLICT DO NOTHING;
INSERT INTO country (country)
SELECT DISTINCT country -- must be distinct!
FROM everything
ON CONFLICT DO NOTHING; -- UPSERT here as well
INSERT INTO fruit_country(fruit_id, country_id)
SELECT f.fruit_id, c.country_id
FROM everything
JOIN fruit f USING (fruit) -- matching row guaranteed now
JOIN country c USING (country); -- matching row guaranteed now
COMMIT;