CODEWITHSUNDEEP
postgresql
S. E.
S. E.

Reputation: 415

Extract a column from one table into another and store id of inserted row as foreign key (PostgreSQL)

As our system currently stands we have a table (ExistingEntity) containing a column (type)

Current table relation

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.

The new relation structure

So what I need to do:

  1. Insert e.type from ExistingEntity e into Properties p
  2. Set 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

Answers (1)

Ruben Helsloot
Ruben Helsloot

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

Related Questions