Clever Geek Handbook

How can I clone or copy records in the same table in postgres?

How to clone or copy records in the same table in PostgreSQL by creating a temporary table.

tries to create clones of records from one table to the same table with a changed name (which is basically a composite key in that table).

+3


source to share


2 answers


You can do it all in one INSERT

combined with SELECT

.

i.e. Let's say you have the following table definition and data populated in it:

create table original
(
  id serial,
  name text,
  location text
);

INSERT INTO original (name, location)
VALUES ('joe', 'London'),
       ('james', 'Munich');

And then you can INSERT

pretend to be the switch you are talking about without using TEMP TABLE

, for example:



INSERT INTO original (name, location)
SELECT 'john', location
FROM original
WHERE name = 'joe';

Here's a sqlfiddle .

It should also be faster (although for tiny datasets, it probably isn't that hard) as it only does one INSERT

and SELECT

unlike the extra SELECT

and CREATE TABLE

plus UPDATE

.

+9


source


Examined the logic a bit:

  • Create temporary table
  • Copy entries to it
  • Update records in temp table
  • Copy it back to your original table

CREATE TEMP TABLE temporary AS SELECT * FROM ORIGINAL WHERE NAME = 'joe';



UPDATE TEMP SET NAME = 'john' WHERE NAME = 'joe';

INSERT INTO ORIGINAL SELECTION * FROM temporary WHERE NAME = 'john';

I wonder if there is a shorter way to do this.

+1


source






More articles:


All Articles