I often have to clone rows in my applications so I do it the long way :
INSERT INTO mytable (a, b, c) SELECT a,b,c FROM mytable WHERE id=12;
(id is declared as a
SERIAL PRIMARY KEY, 12 being the row to clone).
The thing is, when I add new fields to
mytable I must remember to update the clone functions to also clone those new fields.
I would like to clone rows in a more maintainable way. Something like :
INSERT INTO mytable SELECT *, id:DEFAULT FROM mytable WHERE id=12;
As I write it, I also think it would be nice to clone detail rows : if I have a detail/child table declared with referential integrity like this :
CREATE TABLE mychild ( id serial PRIMARY KEY, parentid int REFERENCES mytable(id), a text );
I could clone mytable and all childs in one command rather than :
- clone mytable (the long way)
- get the new
- clone all
parentidto the new
Is there anything in PostgreSQL (or extensions) that could help dealing with these cases ?