I'm using Postgres. I have an old table and a new table. The records in the old table need to have related records in the new table. The new table records are effectively going to act as the parents for records in the new table.
I'm trying to write a migration where each "child" from the old table will have "parent" in the new table.
OLD TABLE (I've added a foreign key in anticipation of populating it as part of the migration)
id | name | new_id (FK)
----+------+-------------
1 | 1st |
2 | 2nd |
NEW TABLE
id | name
----+------
|
I need to do the following:
SELECT
all records from the old tableINSERT
a record into the new table for each old record and RETURNING id
UPDATE
the old record's foreign key value with the RETURNING id
Is there a way to accomplish this using a set query? Or do I need to start delving into Postgres specific things like LOOP
s?
The typical way of doing this is to check that no row has a higher timestamp than any row we retrieve. SELECT timestamp, value, card FROM my_table t1 WHERE NOT EXISTS ( SELECT * FROM my_table t2 WHERE t2. timestamp > t1. timestamp );
In an INSERT , the data available to RETURNING is the row as it was inserted. This is not so useful in trivial inserts, since it would just repeat the data provided by the client. But it can be very handy when relying on computed default values.
The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).
You can use a writable cte as follows:
--open transaction
BEGIN;
--avoid concurrent writes
LOCK old_table IN ROW SHARE MODE;
LOCK new_table IN ROW SHARE MODE;
--create a sequence to populate new_table.id
CREATE SEQUENCE new_table_seq;
--using a writable cte to do the dirty job
WITH old_table_cte AS (
UPDATE old_table SET new_id = nextval('new_table_seq') RETURNING *
)
INSERT INTO new_table SELECT new_id, name FROM old_table_cte;
--Create a proper FK
ALTER TABLE old_table ADD CONSTRAINT old_table_new_table_fk FOREIGN KEY (new_id) REFERENCES new_table (id);
--end transaction
COMMIT;
This approach has some benefits:
update
step. Avoiding an unnecessary
extra SELECT; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With