Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE users (data text, inserted_at timestamptz);

INSERT INTO users
SELECT 'foo', g
FROM   generate_series (timestamptz '2023-09-01 0:0+0', timestamptz '2023-09-22 0:0+0', interval '17 min') g;

CREATE INDEX ON users (inserted_at);

CREATE TABLE

INSERT 0 1779

CREATE INDEX

ALTER TABLE users ADD COLUMN id bigint;

CREATE SEQUENCE users_id_seq;

ALTER SEQUENCE users_id_seq OWNED BY users.id;

ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq');

ALTER TABLE

CREATE SEQUENCE

ALTER SEQUENCE

ALTER TABLE

DO
$do$
DECLARE
   _ts timestamptz := (SELECT COALESCE(min(inserted_at), now()) FROM users);  -- must not be NULL
   _step interval  := '7 days';  -- adjust to your data !!!
BEGIN
   LOOP
      -- RAISE NOTICE 'Updating rows starting from %', _ts;  -- not visible on dbfiddle
      
      UPDATE users
      SET    id = nextval('users_id_seq')
      WHERE  inserted_at >= _ts
      AND    inserted_at <  _ts + _step
      AND    id IS NULL;  -- optional

      EXIT WHEN NOT FOUND AND _ts >= now();  -- adjust to your case !!!

      COMMIT;  -- Requires Postgres 11 !!!

      PERFORM pg_sleep(0.1);  -- short for the demo
      _ts := _ts + _step;
   END LOOP;
END
$do$;

DO

CREATE UNIQUE INDEX CONCURRENTLY users_id_idx ON users (id);

CREATE INDEX

-- ALTER TABLE users ALTER id SET NOT NULL;  -- optional
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_idx;

ALTER TABLE

TABLE users LIMIT 10;

data
inserted_at
id

foo
2023-09-08 00:01:00+00
594

foo
2023-09-08 00:18:00+00
595

foo
2023-09-08 00:35:00+00
596

foo
2023-09-08 00:52:00+00
597

foo
2023-09-08 01:09:00+00
598

foo
2023-09-08 01:26:00+00
599

foo
2023-09-08 01:43:00+00
600

foo
2023-09-08 02:00:00+00
601

foo
2023-09-08 02:17:00+00
602

foo
2023-09-08 02:34:00+00
603

SELECT 10

data	inserted_at	id
foo	2023-09-08 00:01:00+00	594
foo	2023-09-08 00:18:00+00	595
foo	2023-09-08 00:35:00+00	596
foo	2023-09-08 00:52:00+00	597
foo	2023-09-08 01:09:00+00	598
foo	2023-09-08 01:26:00+00	599
foo	2023-09-08 01:43:00+00	600
foo	2023-09-08 02:00:00+00	601
foo	2023-09-08 02:17:00+00	602
foo	2023-09-08 02:34:00+00	603