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

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.