Imagine the followign table:
tmp_migration.asset
╔══════════╦══════════════════════════╗
║ id ║ ...many other columns... ║
╠══════════╬══════════════════════════╣
║ 15 ║ ... ║
║ 16 ║ ... ║
║ 17 ║ ... ║
║ 18 ║ ... ║
║ 10020 ║ ... ║
║ 10021 ║ ... ║
╚══════════╩══════════════════════════╝
You see, the index doesn't start at 1, has gaps, etc.
Problem
I want to add a new column tempId
with a continous index. The table has 80m rows. How can I do that? I googled alot of things and ended up nowhere.
Background
The table is part of a data migration project. tmp_migration
is a temporary schema created as the source of the data migration. In the current step I'm trying to copy over from tmp_migration.asset
to public.asset
while doing data transformation. I'm using a combined INSERT INTO ... SELECT ...
query for that.
The problem with that is, it takes several hours (80m rows) and I don't receive any progress notification during the run. To solve that, I wanted to use "pagination". In the bash, which is calling psql
with the insert/select script, I created a loop which sets borders passed to the script.
I started with using limit / offset by adding
LIMIT :limit
OFFSET :offset;
to the script, but this slows dramatically down after being at higher "pages". So, it is advised to use WHERE
on your PK over limit/offset. However, for this I need a continous PK, which I have not. Thus, I thought of adding a temporary consistent index.
Maybe there are other solutions that I don't see right now. Would be very happy about assistence.