How to clone or copy records in same table in postgres?

Question

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

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

Endymion

Answer 1

You can do it all in one INSERT combined with a SELECT.

i.e. 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 doing the kind of switch you're talking about without using a TEMP TABLE, like this:

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

Here's an sqlfiddle.

This should also be faster (although for tiny data sets probably not hugely so in absolute time terms), since it's doing only one INSERT and SELECT as opposed to an extra SELECT and CREATE TABLE plus an UPDATE.

Rancho

Answer 2

Did a bit of research, came up with a logic :

1. Create temp table
2. Copy records into it
3. Update the records in temp table
4. Copy it back to original table

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

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

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

Was wondering if there was any shorter way to do it.

Endymion