Mixing
Deep copy of entity and relationships using SQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have three tables
Store
Book
Page
A store is one-to-many to books, book is one-to-many to pages and they all have the foreign keys set. I want to create copies of the store (and consequently, the books and pages) using a SQL query. I've tried using CTE's, but I'm having trouble maintaining the relationships between the entities.
I'm not trying to create a new table, just creating a duplicate of a specific Store row (and its relationships), the ids on the tables are serial.
So a copy of
Store 1
Book 1 (store_id: 1)
Page 1 (book_id: 1)
Page 2 (book_id: 1)
Would be
Store 2
Book 2 (store_id: 2)
Page 3 (book_id: 2)
Page 4 (book_id: 2)
sql postgresql deep-copy
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
add a comment |
0
I have three tables
Store
Book
Page
A store is one-to-many to books, book is one-to-many to pages and they all have the foreign keys set. I want to create copies of the store (and consequently, the books and pages) using a SQL query. I've tried using CTE's, but I'm having trouble maintaining the relationships between the entities.
I'm not trying to create a new table, just creating a duplicate of a specific Store row (and its relationships), the ids on the tables are serial.
So a copy of
Store 1
Book 1 (store_id: 1)
Page 1 (book_id: 1)
Page 2 (book_id: 1)
Would be
Store 2
Book 2 (store_id: 2)
Page 3 (book_id: 2)
Page 4 (book_id: 2)
sql postgresql deep-copy
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
Sample data and desired results would help. Are you trying to create a new table? Or just adding rows into existing tables? Do you want to copy all stores? Or just one store? Are the ids in the tables serial columns?
– Gordon Linoff
Jan 3 at 12:15
@GordonLinoff you're right, added missing information.
– Diogo Martins
Jan 3 at 12:23
add a comment |
0
0
0
I have three tables
Store
Book
Page
A store is one-to-many to books, book is one-to-many to pages and they all have the foreign keys set. I want to create copies of the store (and consequently, the books and pages) using a SQL query. I've tried using CTE's, but I'm having trouble maintaining the relationships between the entities.
I'm not trying to create a new table, just creating a duplicate of a specific Store row (and its relationships), the ids on the tables are serial.
So a copy of
Store 1
Book 1 (store_id: 1)
Page 1 (book_id: 1)
Page 2 (book_id: 1)
Would be
Store 2
Book 2 (store_id: 2)
Page 3 (book_id: 2)
Page 4 (book_id: 2)
sql postgresql deep-copy
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
I have three tables
Store
Book
Page
A store is one-to-many to books, book is one-to-many to pages and they all have the foreign keys set. I want to create copies of the store (and consequently, the books and pages) using a SQL query. I've tried using CTE's, but I'm having trouble maintaining the relationships between the entities.
I'm not trying to create a new table, just creating a duplicate of a specific Store row (and its relationships), the ids on the tables are serial.
So a copy of
Store 1
Book 1 (store_id: 1)
Page 1 (book_id: 1)
Page 2 (book_id: 1)
Would be
Store 2
Book 2 (store_id: 2)
Page 3 (book_id: 2)
Page 4 (book_id: 2)
sql postgresql deep-copy
sql postgresql deep-copy
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
edited Jan 3 at 12:20
Diogo Martins
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
asked Jan 3 at 12:14
Diogo MartinsDiogo Martins
446
446
Sample data and desired results would help. Are you trying to create a new table? Or just adding rows into existing tables? Do you want to copy all stores? Or just one store? Are the ids in the tables serial columns?
– Gordon Linoff
Jan 3 at 12:15
@GordonLinoff you're right, added missing information.
– Diogo Martins
Jan 3 at 12:23
add a comment |
Sample data and desired results would help. Are you trying to create a new table? Or just adding rows into existing tables? Do you want to copy all stores? Or just one store? Are the ids in the tables serial columns?
– Gordon Linoff
Jan 3 at 12:15
@GordonLinoff you're right, added missing information.
– Diogo Martins
Jan 3 at 12:23
Sample data and desired results would help. Are you trying to create a new table? Or just adding rows into existing tables? Do you want to copy all stores? Or just one store? Are the ids in the tables serial columns?
– Gordon Linoff
Jan 3 at 12:15
Sample data and desired results would help. Are you trying to create a new table? Or just adding rows into existing tables? Do you want to copy all stores? Or just one store? Are the ids in the tables serial columns?
– Gordon Linoff
Jan 3 at 12:15
@GordonLinoff you're right, added missing information.
– Diogo Martins
Jan 3 at 12:23
@GordonLinoff you're right, added missing information.
– Diogo Martins
Jan 3 at 12:23
add a comment |
1 Answer
1
active
oldest
votes
0
I believe that Postgres will preserve ordering of the serial ids when an insert . . . select has an order by. So, you can do what you want by using returning and creating a mapping table from the old and the new values:
with s as (
insert into stores ( . . . )
select . . .
from stores
where store_id = @x
returning *
),
b as (
insert into books (store_id, . . . )
select s.store_id, . . .
from books b cross join
s
where b.store_id = @x
order by b.book_id
returning *
),
bb as (
select bold.book_id as old_book_id, bnew.book_id as new_book_id
from (select b.book_id,
row_number() over (order by book_id) as seqnum
from books b cross join
s
where b.store_id = @x
) bold join
(select b.*, row_number() over (order by book_id) as seqnum
from b
) bnew
on bnew.seqnum = bold.seqnum
)
insert into pages (book_id, . . .)
select bb.new_book_id, . . .
from pages p join
bb b
on p.book_id = bb.old_book_id;
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
Thank you for your answer, it works perfectly for the example I gave. But imagine that the page entity also had a child relationship, let's call it "Words" for the sake of the argument. Since the mapping is dependent on the store_id, how would it work? My real problem has a hierarchy of 6 entities, so I was trying to find a way to always access the old_id of a child's parent entity, but I can't seem to find a way to do it, maybe I'm not going the right way about it.
– Diogo Martins
Jan 3 at 14:05
@DiogoMartins . . . The simplest method is if you happen to have other columns that uniquely identify each row. Of course after this operation, that won't be true any more.
– Gordon Linoff
Jan 3 at 14:18
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022096%2fdeep-copy-of-entity-and-relationships-using-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I believe that Postgres will preserve ordering of the serial ids when an insert . . . select has an order by. So, you can do what you want by using returning and creating a mapping table from the old and the new values:
with s as (
insert into stores ( . . . )
select . . .
from stores
where store_id = @x
returning *
),
b as (
insert into books (store_id, . . . )
select s.store_id, . . .
from books b cross join
s
where b.store_id = @x
order by b.book_id
returning *
),
bb as (
select bold.book_id as old_book_id, bnew.book_id as new_book_id
from (select b.book_id,
row_number() over (order by book_id) as seqnum
from books b cross join
s
where b.store_id = @x
) bold join
(select b.*, row_number() over (order by book_id) as seqnum
from b
) bnew
on bnew.seqnum = bold.seqnum
)
insert into pages (book_id, . . .)
select bb.new_book_id, . . .
from pages p join
bb b
on p.book_id = bb.old_book_id;
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
Thank you for your answer, it works perfectly for the example I gave. But imagine that the page entity also had a child relationship, let's call it "Words" for the sake of the argument. Since the mapping is dependent on the store_id, how would it work? My real problem has a hierarchy of 6 entities, so I was trying to find a way to always access the old_id of a child's parent entity, but I can't seem to find a way to do it, maybe I'm not going the right way about it.
– Diogo Martins
Jan 3 at 14:05
@DiogoMartins . . . The simplest method is if you happen to have other columns that uniquely identify each row. Of course after this operation, that won't be true any more.
– Gordon Linoff
Jan 3 at 14:18
add a comment |
0
I believe that Postgres will preserve ordering of the serial ids when an insert . . . select has an order by. So, you can do what you want by using returning and creating a mapping table from the old and the new values:
with s as (
insert into stores ( . . . )
select . . .
from stores
where store_id = @x
returning *
),
b as (
insert into books (store_id, . . . )
select s.store_id, . . .
from books b cross join
s
where b.store_id = @x
order by b.book_id
returning *
),
bb as (
select bold.book_id as old_book_id, bnew.book_id as new_book_id
from (select b.book_id,
row_number() over (order by book_id) as seqnum
from books b cross join
s
where b.store_id = @x
) bold join
(select b.*, row_number() over (order by book_id) as seqnum
from b
) bnew
on bnew.seqnum = bold.seqnum
)
insert into pages (book_id, . . .)
select bb.new_book_id, . . .
from pages p join
bb b
on p.book_id = bb.old_book_id;
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
Thank you for your answer, it works perfectly for the example I gave. But imagine that the page entity also had a child relationship, let's call it "Words" for the sake of the argument. Since the mapping is dependent on the store_id, how would it work? My real problem has a hierarchy of 6 entities, so I was trying to find a way to always access the old_id of a child's parent entity, but I can't seem to find a way to do it, maybe I'm not going the right way about it.
– Diogo Martins
Jan 3 at 14:05
@DiogoMartins . . . The simplest method is if you happen to have other columns that uniquely identify each row. Of course after this operation, that won't be true any more.
– Gordon Linoff
Jan 3 at 14:18
add a comment |
0
0
0
I believe that Postgres will preserve ordering of the serial ids when an insert . . . select has an order by. So, you can do what you want by using returning and creating a mapping table from the old and the new values:
with s as (
insert into stores ( . . . )
select . . .
from stores
where store_id = @x
returning *
),
b as (
insert into books (store_id, . . . )
select s.store_id, . . .
from books b cross join
s
where b.store_id = @x
order by b.book_id
returning *
),
bb as (
select bold.book_id as old_book_id, bnew.book_id as new_book_id
from (select b.book_id,
row_number() over (order by book_id) as seqnum
from books b cross join
s
where b.store_id = @x
) bold join
(select b.*, row_number() over (order by book_id) as seqnum
from b
) bnew
on bnew.seqnum = bold.seqnum
)
insert into pages (book_id, . . .)
select bb.new_book_id, . . .
from pages p join
bb b
on p.book_id = bb.old_book_id;
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
I believe that Postgres will preserve ordering of the serial ids when an insert . . . select has an order by. So, you can do what you want by using returning and creating a mapping table from the old and the new values:
with s as (
insert into stores ( . . . )
select . . .
from stores
where store_id = @x
returning *
),
b as (
insert into books (store_id, . . . )
select s.store_id, . . .
from books b cross join
s
where b.store_id = @x
order by b.book_id
returning *
),
bb as (
select bold.book_id as old_book_id, bnew.book_id as new_book_id
from (select b.book_id,
row_number() over (order by book_id) as seqnum
from books b cross join
s
where b.store_id = @x
) bold join
(select b.*, row_number() over (order by book_id) as seqnum
from b
) bnew
on bnew.seqnum = bold.seqnum
)
insert into pages (book_id, . . .)
select bb.new_book_id, . . .
from pages p join
bb b
on p.book_id = bb.old_book_id;
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
answered Jan 3 at 13:31
Gordon LinoffGordon Linoff
797k37318423
797k37318423
Thank you for your answer, it works perfectly for the example I gave. But imagine that the page entity also had a child relationship, let's call it "Words" for the sake of the argument. Since the mapping is dependent on the store_id, how would it work? My real problem has a hierarchy of 6 entities, so I was trying to find a way to always access the old_id of a child's parent entity, but I can't seem to find a way to do it, maybe I'm not going the right way about it.
– Diogo Martins
Jan 3 at 14:05
@DiogoMartins . . . The simplest method is if you happen to have other columns that uniquely identify each row. Of course after this operation, that won't be true any more.
– Gordon Linoff
Jan 3 at 14:18
add a comment |
Thank you for your answer, it works perfectly for the example I gave. But imagine that the page entity also had a child relationship, let's call it "Words" for the sake of the argument. Since the mapping is dependent on the store_id, how would it work? My real problem has a hierarchy of 6 entities, so I was trying to find a way to always access the old_id of a child's parent entity, but I can't seem to find a way to do it, maybe I'm not going the right way about it.
– Diogo Martins
Jan 3 at 14:05
@DiogoMartins . . . The simplest method is if you happen to have other columns that uniquely identify each row. Of course after this operation, that won't be true any more.
– Gordon Linoff
Jan 3 at 14:18
Thank you for your answer, it works perfectly for the example I gave. But imagine that the page entity also had a child relationship, let's call it "Words" for the sake of the argument. Since the mapping is dependent on the store_id, how would it work? My real problem has a hierarchy of 6 entities, so I was trying to find a way to always access the old_id of a child's parent entity, but I can't seem to find a way to do it, maybe I'm not going the right way about it.
– Diogo Martins
Jan 3 at 14:05
Thank you for your answer, it works perfectly for the example I gave. But imagine that the page entity also had a child relationship, let's call it "Words" for the sake of the argument. Since the mapping is dependent on the store_id, how would it work? My real problem has a hierarchy of 6 entities, so I was trying to find a way to always access the old_id of a child's parent entity, but I can't seem to find a way to do it, maybe I'm not going the right way about it.
– Diogo Martins
Jan 3 at 14:05
@DiogoMartins . . . The simplest method is if you happen to have other columns that uniquely identify each row. Of course after this operation, that won't be true any more.
– Gordon Linoff
Jan 3 at 14:18
@DiogoMartins . . . The simplest method is if you happen to have other columns that uniquely identify each row. Of course after this operation, that won't be true any more.
– Gordon Linoff
Jan 3 at 14:18
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54022096%2fdeep-copy-of-entity-and-relationships-using-sql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Sample data and desired results would help. Are you trying to create a new table? Or just adding rows into existing tables? Do you want to copy all stores? Or just one store? Are the ids in the tables serial columns?
– Gordon Linoff
Jan 3 at 12:15
@GordonLinoff you're right, added missing information.
– Diogo Martins
Jan 3 at 12:23