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

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

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;

StackExchange Database Administrators Q#146906, answer score: 4