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
  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 country_id FROM country_ids)
  FROM fruit_ids

But no dice:

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

1 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?)

   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:

To INSERT data from everything:


INSERT INTO fruit (fruit)
SELECT DISTINCT fruit              -- must be distinct!
FROM   everything

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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.