postgresql - Move SQL data from one table to another with preserving foreign keys in third - Stack Overflow

admin管理员组

文章数量:1122832

I want to move data from several similarly structured tables like old_metadata to table new_metadata and update references in tables respectively. For example deposit has field metadata_id that references old_metadata.id. Trick is I don't know id's of records in new_metadata tables until they are created; Table structures are like:

old_metadata

id | amount
---+-------
0  | 30
1  | 40

new_metadata

id | amount | metadata_type
---+--------+--------------
31 | 30     | OLD_METADATA
32 | 40     | OLD_METADATA

deposits

id | metadata_id | ...
---+-------------+----
11 | 1           |
12 | 2           |

So what i've tried was

ALTER TABLE deposits ADD COLUMN new_metadata_id INT REFERENCES new_metadata(id);

WITH metadata AS (
    INSERT INTO new_metadata (amount, metadata_type)
        SELECT amount, 'OLD_METADATA' FROM old_metadata
        RETURNING id, old_metadata.id as old_id
) 
UPDATE deposits SET new_metadata_id = metadata.id
FROM metada
WHERE metadata_id = metadata.old_id;

My next steps would be replace data in old column with new but it fails saying missing FROM-clause entry for table "old_metadata". Alternatively may be it can be done with cursors or temp tables but I believe it's not the most straightforward way.

Sloppy but working solution would be something like

ALTER TABLE new_metadata ADD COLUMN old_id INT;
ALTER TABLE deposits ADD COLUMN new_metadata_id INT;

WITH metadata AS (
    INSERT INTO new_metadata (amount, metadata_type, old_id)
        SELECT amount, 'OLD_METADATA', id FROM old_metadata
        RETURNING *
)
UPDATE deposits SET new_metadata_id = metadata.id
FROM new_metadata, old_metadata
WHERE metadata_id = new_metadata.old_id;

UPDATE deposits SET metadata_id = deposits.new_metadata_id

ALTER TABLE deposits DROP COLUMN new_metadata_id
ALTER TABLE new_metadata DROP COLUMN old_id

I want to move data from several similarly structured tables like old_metadata to table new_metadata and update references in tables respectively. For example deposit has field metadata_id that references old_metadata.id. Trick is I don't know id's of records in new_metadata tables until they are created; Table structures are like:

old_metadata

id | amount
---+-------
0  | 30
1  | 40

new_metadata

id | amount | metadata_type
---+--------+--------------
31 | 30     | OLD_METADATA
32 | 40     | OLD_METADATA

deposits

id | metadata_id | ...
---+-------------+----
11 | 1           |
12 | 2           |

So what i've tried was

ALTER TABLE deposits ADD COLUMN new_metadata_id INT REFERENCES new_metadata(id);

WITH metadata AS (
    INSERT INTO new_metadata (amount, metadata_type)
        SELECT amount, 'OLD_METADATA' FROM old_metadata
        RETURNING id, old_metadata.id as old_id
) 
UPDATE deposits SET new_metadata_id = metadata.id
FROM metada
WHERE metadata_id = metadata.old_id;

My next steps would be replace data in old column with new but it fails saying missing FROM-clause entry for table "old_metadata". Alternatively may be it can be done with cursors or temp tables but I believe it's not the most straightforward way.

Sloppy but working solution would be something like

ALTER TABLE new_metadata ADD COLUMN old_id INT;
ALTER TABLE deposits ADD COLUMN new_metadata_id INT;

WITH metadata AS (
    INSERT INTO new_metadata (amount, metadata_type, old_id)
        SELECT amount, 'OLD_METADATA', id FROM old_metadata
        RETURNING *
)
UPDATE deposits SET new_metadata_id = metadata.id
FROM new_metadata, old_metadata
WHERE metadata_id = new_metadata.old_id;

UPDATE deposits SET metadata_id = deposits.new_metadata_id

ALTER TABLE deposits DROP COLUMN new_metadata_id
ALTER TABLE new_metadata DROP COLUMN old_id

• postgresql

Share Improve this question Follow edited Nov 22, 2024 at 16:55 Ahnassi asked Nov 22, 2024 at 15:39 AhnassiAhnassi 7599 bronze badges 6

• Combine your multiple-table data into one plain table. Then insert this data onto new places saving the references. – Akina Commented Nov 22, 2024 at 15:50
• Removed mysql tag, this problem is clearly using PostgreSQL, because MySQL doesn't support INSERT...RETURNING syntax. – Bill Karwin Commented Nov 22, 2024 at 15:50
• @BillKarwin but i would be totally ok with pure sql solution – Ahnassi Commented Nov 22, 2024 at 15:54
• Why not?: ALTER TABLE old_meta ADD COLUMN metadata_type varchar – Adrian Klaver Commented Nov 22, 2024 at 16:09
• I want to merge several old tables with exact structure in to one, so even if I edit old_metadata there are like 10 similar another_old_metadata tables with exact same problem. – Ahnassi Commented Nov 22, 2024 at 16:15

 |  Show 1 more comment

1 Answer 1

Sorted by: Reset to default 0

See example

Data model for test:

create table old_metadata ( id int primary key generated by default as identity, amount int);
insert into old_metadata values
 (0, 30)
,(1, 40)
,(2, 50)
;
create table new_metadata ( id int primary key generated by default as identity
  , amount int,metadata_type varchar(20));
insert into new_metadata values
 (31 , 30,    'OLD_METADATA')
,(32 , 40,    'OLD_METADATA')
;
create table deposits ( id int primary key generated by default as identity
  , metadata_id int );
alter table deposits add constraint 
    cx_reference_fk_old_metadata FOREIGN KEY (metadata_id) references old_metadata;
insert into deposits values
 (11 , 1)
,(12 , 2)
;

CREATE TABLE, INSERT 0 3, CREATE TABLE, INSERT 0 2, CREATE TABLE, ALTER TABLE, INSERT 0 2

old_metadata

id	amount
0	30
1	40
2	50

new_metadata

id	amount	metadata_type
31	30	OLD_METADATA
32	40	OLD_METADATA

deposits

id	metadata_id
11	1
12	2

Add column old_metadata_id to table new_metadata. This column can be deleted later.

ALTER TABLE new_metadata ADD COLUMN old_metadata_id INT REFERENCES old_metadata(id);

We will insert into new_metadata values from old_metadata with new Id. For simplicity, let's make sure that the next Id does not overlap with those already present in new_metadata. We set next Id to max(id)+1=32+1=33.

SELECT setval('new_metadata_id_seq', max(id)) FROM new_metadata;

setval
32

Drop reference to old_metadata.

ALTER TABLE deposits DROP CONSTRAINT cx_reference_fk_old_metadata;

WITH metadata AS (
    INSERT INTO new_metadata (amount, metadata_type,old_metadata_id)
        SELECT amount, 'OLD_METADATA' metadata_type,id as old_metadata_id FROM old_metadata
        RETURNING id, old_metadata_id 
) 
UPDATE deposits SET metadata_id = metadata.id
FROM metadata
WHERE metadata_id = old_metadata_id;

UPDATE 2

Set reference to new_metadata

alter table deposits add constraint 
    cx_reference_fk_new_metadata FOREIGN KEY (metadata_id) references new_metadata;

After UPDATE

old_metadata

id	amount
0	30
1	40
2	50

new_metadata

id	amount	metadata_type	old_metadata_id
31	30	OLD_METADATA	null
32	40	OLD_METADATA	null
33	30	OLD_METADATA	0
34	40	OLD_METADATA	1
35	50	OLD_METADATA	2

deposits

id	metadata_id
11	34
12	35

fiddle

For question can this part cause collision when newely set metadata_id has the id that is in old_metadata_id variables?

UPDATE deposits SET metadata_id = metadata.id FROM metadata WHERE metadata_id = old_metadata_id;

If you have one old_metadata table, then the intersection of new_metadata_id and old_metadata_id does not cause problems.
If you have several old_metadata tables, then this problem may arise if you consistently process these tables in the specified way.
In this case, it is better to collect all old_metadata in one table and then apply the update 1 time.
Or calculate max(old_metadata_id) in all old_metadata tables and set this value as nextval for new_metadata_id. In this case new_metadata_id will be greater than all old_metadata_id.

If you have intersections by old_metadata_id in the old_metadata tables, then you first need to resolve this collision in some way. This is no longer relevant to your question.

SELECT * FROM pg_sequences WHERE sequencename LIKE '%id%';

schemaname	sequencename	sequenceowner	data_type	start_value	min_value	max_value	increment_by	cycle	cache_size	last_value
public	old_metadata_id_seq	postgres	integer	1	1	2147483647	1	f	1	null
public	new_metadata_id_seq	postgres	integer	1	1	2147483647	1	f	1	35
public	deposits_id_seq	postgres	integer	1	1	2147483647	1	f	1	null

本文标签： postgresqlMove SQL data from one table to another with preserving foreign keys in thirdStack Overflow