Insert data and set foreign keys with Postgres

by

The table users must have some primary key that you did not disclose. For the purpose of this answer I will name it users_id.

You can solve this rather elegantly with data-modifying CTEs introduced with PostgreSQL 9.1:

country is unique

The whole operation is rather trivial in this case:

WITH i AS (
    INSERT INTO addresses (country) 
    SELECT country
    FROM   users
    WHERE  address_id IS NULL 
    RETURNING id, country
    )
UPDATE users u
SET    address_id = i.id
FROM   i
WHERE  i.country = u.country;

You mention version 8.3 in your question. Upgrade! Postgres 8.3 has reached end of life.

Be that as it may, this is simple enough with version 8.3. You just need two statements:

INSERT INTO addresses (country) 
SELECT country
FROM   users
WHERE  address_id IS NULL;

UPDATE users u
SET    address_id = a.id
FROM   addresses a
WHERE  address_id IS NULL 
AND    a.country = u.country;

country is not unique

That’s more challenging. You could just create one address and link to it multiple times. But you did mention a 1:1 relationship that rules out such a convenient solution.

WITH s AS (
    SELECT users_id, country
         , row_number() OVER (PARTITION BY country) AS rn
    FROM   users
    WHERE  address_id IS NULL 
    )
    , i AS (
    INSERT INTO addresses (country) 
    SELECT country
    FROM   s
    RETURNING id, country
    )
    , r AS (
    SELECT *
         , row_number() OVER (PARTITION BY country) AS rn
    FROM   i
    )
UPDATE users u
SET    address_id = r.id
FROM   r
JOIN   s USING (country, rn)    -- select exactly one id for every user
WHERE  u.users_id = s.users_id
AND    u.address_id IS NULL;

As there is no way to unambiguously assign exactly one id returned from the INSERT to every user in a set with identical country, I use the window function row_number() to make them unique.

Not as straight forward with Postgres 8.3. One possible way:

INSERT INTO addresses (country) 
SELECT DISTINCT country -- pick just one per set of dupes
FROM   users
WHERE  address_id IS NULL;

UPDATE users u
SET    address_id = a.id
FROM   addresses a
WHERE  a.country = u.country
AND    u.address_id IS NULL
AND NOT EXISTS (
    SELECT * FROM addresses b
    WHERE  b.country = a.country
    AND    b.users_id < a.users_id
    ); -- effectively picking the smallest users_id per set of dupes

Repeat this until the last NULL value is gone from users.address_id.

More Related Contents:

  1. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  2. Does PostgreSQL support “accent insensitive” collations?
  3. PostgreSQL DISTINCT ON with different ORDER BY
  4. PostgreSQL: running count of rows for a query ‘by minute’
  5. Split comma separated column data into additional columns
  6. IN vs ANY operator in PostgreSQL
  7. How to delete duplicate entries?
  8. Transpose latest rows per user to columns
  9. subquery in FROM must have an alias
  10. Compute percents from SUM() in the same SELECT sql query
  11. SQL Between clause with strings columns
  12. Insert an image in postgresql database
  13. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  14. Error: Column does not exist
  15. Postgresql Column Not Found, But Shows in Describe
  16. DROP FUNCTION without knowing the number/type of parameters?
  17. Multiple array_agg() calls in a single query
  18. Why can’t I use column aliases in the next SELECT expression?
  19. Postgres window function and group by exception
  20. Calculate working hours between 2 dates in PostgreSQL
  21. How do I ALTER a PostgreSQL table and make a column unique?
  22. Find records where join doesn’t exist
  23. Rails 4 LIKE query – ActiveRecord adds quotes
  24. How to compare dates in datetime fields in Postgresql?
  25. How to perform a select query in a DO block?
  26. Cannot SELECT from UPDATE RETURNING clause in postgres
  27. Subtract hours from the now() function
  28. Postgres: convert single row to multiple rows (unpivot)
  29. Get most common value for each value of another column in SQL
  30. PostgreSQL: top n entries per item in same table

Leave a Comment