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

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', locationFROM originalWHERE 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.

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

1. Create temp table
2. Copy records in to 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.