PostgreSQL add new not null column and fill with ids from insert statement

Question

I´ve got 2 tables.

CREATE TABLE content (
  id bigserial NOT NULL,
  name text
);


CREATE TABLE data (
    id bigserial NOT NULL,
    ...
);

The tables are already filled with a lot of data. Now I want to add a new column content_id (NOT NULL) to the data table. It should be a foreign key to the content table.

Is it possible to automatically create an entry in the content table to set a content_id in the data table.

For example

**content**
| id | name |
|  1 | abc  | 
|  2 | cde  |

data

| id |... |
| 1  |... |
| 2  |... |
| 3  |... |

Now I need an update statement that creates 3 (in this example) content entries and add the ids to the data table to get this result:

content

| id | name |
|  1 | abc  |
|  2 | cde  |
|  3 | ...  |
|  4 | ...  |
|  5 | ...  |

data

| id |... | content_id |
| 1  |... | 3          |
| 2  |... | 4          |
| 3  |... | 5          |

Where do the values `3,4,5` for the new `content_id` come from? What is the logic? — S-Man, Feb 24 '21 at 09:49
Does this answer your question? [How can I add a column that doesn't allow nulls in a Postgresql database?](https://stackoverflow.com/questions/512451/how-can-i-add-a-column-that-doesnt-allow-nulls-in-a-postgresql-database) — S-Man, Feb 24 '21 at 09:52
it´s an auto generated id. The answer doesn´t help, because i am seraching for the statement in the middle of the example: ... some work (set real values as you want)... — A.K., Feb 24 '21 at 10:21
You must describe what exactly you want to do. How do you find the correct content_id? Or do you simply want to add random data? — S-Man, Feb 24 '21 at 10:23
For every existing entry in the table data i need to add a new line to the table content and add the auto generated id from the new content entries to the existing data entries. — A.K., Feb 24 '21 at 10:25
How is the name column in the new content records be filled? — S-Man, Feb 24 '21 at 10:39
There is no existing link between "data" and "content" so what is the rule to create the foreign key from "content" to "data"? — , Feb 24 '21 at 11:41

score 1 · Accepted Answer · answered Feb 24 '21 at 10:58

demo:db<>fiddle

According to the answers presented here: How can I add a column that doesn't allow nulls in a Postgresql database?, there are several ways of adding a new NOT NULL column and fill this directly.

Basicly there are 3 steps. Choose the best fitting (with or without transaction, setting a default value first and remove after, leave the NOT NULL contraint first and add afterwards, ...)

Step 1: Adding new column (without NOT NULL constraint, because the values of the new column values are not available at this point)

ALTER TABLE data ADD COLUMN content_id integer;

Step 2: Inserting the data into both tables in a row:

WITH inserted AS (                -- 1
    INSERT INTO content
    SELECT
        generate_series(
            (SELECT MAX(id) + 1 FROM content),
            (SELECT MAX(id) FROM content) + (SELECT COUNT(*) FROM data)
        ),
        'dummy text'
    RETURNING id
), matched AS (                   -- 2
   SELECT
       d.id AS data_id,
       i.id AS content_id
   FROM (
       SELECT
           id,
           row_number() OVER ()
       FROM data
   ) d 
   JOIN (
      SELECT
          id,
          row_number() OVER ()
      FROM inserted
   ) i ON i.row_number = d.row_number
)                                 -- 3
UPDATE data d
SET content_id = s.content_id
FROM (
    SELECT * FROM matched
) s
WHERE d.id = s.data_id;

Executing several statements one after another by using the results of the previous one can be achieved using WITH clauses (CTEs):

Insert data into content table: This generates an integer series starting at the MAX() + 1 value of the current content's id values and has as many records as the data table. Afterwards the new ids are returned
Now we need to match the current records of the data table with the new ids. So for both sides, we use row_number() window function to generate a consecutive row count for each records. Because both, the insert result and the actual data table have the same number of records, this can be used as join criterion. So we can match the id column of the data table with the new content's id values
This matched data can used in the final update of the new content_id column

Step 3: Add the NOT NULL constraint

ALTER TABLE data ALTER COLUMN content_id SET NOT NULL;

PostgreSQL add new not null column and fill with ids from insert statement

1 Answers1