Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE content (
    id int,
    name text
);

INSERT INTO content VALUES
(1, 'abc'), (2, 'def');

CREATE TABLE data (
    id int,
    sth text
);

INSERT INTO data VALUES
(1, 'x'), (2, 'y'), (3, 'z');

CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 3


-- step 1: Create new column

ALTER TABLE data ADD COLUMN content_id integer;

SELECT * FROM data

ALTER TABLE




id
sth
content_id


1
x
null

2
y
null

3
z
null



SELECT 3


-- step 2: insert data, return the new ids and update data table

WITH inserted AS (
    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 (
   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
)
UPDATE data d
SET content_id = s.content_id
FROM (
    SELECT * FROM matched
) s
WHERE d.id = s.data_id;

SELECT * FROM data

UPDATE 3




id
sth
content_id


1
x
3

2
y
4

3
z
5



SELECT 3


-- step 3: Add NOT NULL CONSTRAINT

ALTER TABLE data ALTER COLUMN content_id SET NOT NULL

ALTER TABLE
id	sth	content_id
1	x	null
2	y	null
3	z	null