Run Code
|
Code Wall
|
Users
|
Misc
|
Feedback
|
About
|
Login
|
Theme
|
Privacy
deep copy map
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Editor:
CodeMirror
EditArea
Simple
Layout:
Vertical
Horizontal
drop table if exists table_a, table_b, table_c cascade; create table table_a ( id serial primary key, some_data text ); create table table_b ( id serial primary key, some_data integer ); create table map_a2b ( a_id integer not null references table_a, b_id integer not null references table_b, primary key (a_id, b_id) ); insert into table_a (id, some_data) values (1, 'one'), (2, 'two'); insert into table_b (id, some_data) values (1, 10), (2, 20), (3, 30), (4, 40); insert into map_a2b (a_id, b_id) values (1, 1), (1, 2), (1,3), (2,2), (2,4); -- the above used hardcoded PK values, we need to adjust the sequence -- to avoid duplicate values. It also makes it easier to spot (and validate) the -- newly created rows as all copied IDs start with 100 select setval('table_a_id_seq', 100); select setval('table_b_id_seq', 100); with new_a_values as ( select a.id as old_a_id, nextval(pg_get_serial_sequence('table_a', 'id')) as new_a_id, 'Copy of '||a.some_data as some_data -- other columns from table_a a where a.id = 1 ), new_b_values as ( select b.id as old_b_id, a.new_a_id, nextval(pg_get_serial_sequence('table_b', 'id')) as new_b_id, b.some_data -- other columns from table_b b join map_a2b m on m.b_id = b.id join new_a_values a on a.old_a_id = m.a_id ), new_a as ( insert into table_a (id, some_data) select new_a_id, some_data from new_a_values ), new_b as ( insert into table_b (id, some_data) select new_b_id, some_data from new_b_values ) insert into map_a2b (a_id, b_id) select new_a_id, new_b_id from new_b_values; select * from table_a; select * from table_b; select * from map_a2b;
absolute service time: 0,47 sec
edit mode
|
history
setval
1
100
setval
1
100
id
some_data
1
1
one
2
2
two
3
101
Copy of one
id
some_data
1
1
10
2
2
20
3
3
30
4
4
40
5
101
10
6
102
20
7
103
30
a_id
b_id
1
1
1
2
1
2
3
1
3
4
2
2
5
2
4
6
101
101
7
101
102
8
101
103