Reputation: 415
As our system currently stands we have a table (ExistingEntity) containing a column (type)
As part of a structural change, this value needs to be "extracted" to a row in a new table (Properties) and store the id of the new row as a foreign key in ExistingEntity.properties. Properties.id is an autoincremented sequence.
So what I need to do:
e.type
from ExistingEntity e
into Properties p
e.properties
to p.id
How would I go about doing this as a query? Our database is using PostgreSQL 9.6
EDIT: As it seems my explanation seems to be a little too vague for some, what I wish to accomplish is that the two tables
ExistingEntity
+------+------+
| id | type |
+------+------+
| 1735 | 4 |
| ... | ... |
+------+------+
Type
+-----+---------------------+
| id | data |
+-----+---------------------+
| 4 | "imageSomeDataHere" |
| ... | ... |
+-----+---------------------+
is extracted into the three tables
ExistingEntity
+------+------------+
| id | properties |
+------+------------+
| 1735 | 1 |
| ... | ... |
+------+------------+
Properties
+-----+------+
| id | type |
+-----+------+
| 1 | 4 |
| ... | ... |
+-----+------+
Type
+-----+---------------------+
| id | data |
+-----+---------------------+
| 4 | "imageSomeDataHere" |
| ... | ... |
+-----+---------------------+
I thought the query
UPDATE ExistingEntity e SET properties = (
INSERT INTO Properties (type) VALUES (e.type) RETURNING id
);
would work, but IntelliJ complains about syntax on that one
Upvotes: 1
Views: 83
Reputation: 13139
Use the fact that you have transactions in your favour here and just execute in order. The DDL code might not work exactly, because I am on my phone, but should be easy to find.
BEGIN TRANSACTION;
CREATE TABLE Properties...; -- id and type columns, and a temporary entity_id
-- Properties.Id should auto-increment
INSERT INTO Properties(entity_id, type)
SELECT id, type
FROM ExistingEntity;
-- Make property NULLABLE for now
ALTER TABLE ExistingEntity DROP COLUMN type;
ALTER TABLE ExistingEntity ADD COLUMN property NULL;
-- Connect ExistingEntity to Properties
UPDATE ExistingEntity
SET property = Properties.id
FROM Properties
WHERE Properties.entity_id = ExistingEntity.id;
-- Cleanup
ALTER TABLE Properties DROP COLUMN entity_id;
ALTER TABLE ExistingEntity ALTER COLUMN property NON NULL;
-- Finally, commit
COMMIT;
Upvotes: 1