Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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).

like image 630
Jay Avatar asked Oct 21 '14 20:10

Jay


People also ask

How do I COPY the same data in the same table in SQL?

If you want to copy the data of one SQL table into another SQL table in the same SQL server, then it is possible by using the SELECT INTO statement in SQL. The SELECT INTO statement in Structured Query Language copies the content from one existing table into the new table.

How do I clone a table in PostgreSQL?

To copy a table with partial data from an existing table, users can use the following statement: Syntax: CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition; The condition in the WHERE clause of the query defines which rows of the existing table will be copied to the new table.

How do you duplicate rows in pgAdmin?

On the pgAdmin 4 GUI Tool page, it seems the only proposed way to copy is to click on this button : Click the Copy icon to copy the currently selected row.

How do I use Copy command in PostgreSQL?

PSQL \Copy Command for Client-Side ExportTo copy the entire table to a csv file, use \copy. This will copy the contents of a table to the client computer as a csv file. The file will not contain the headers of the table. \copy employees to '/var/lib/postgresql/emp.


2 Answers

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.

like image 129
khampson Avatar answered Sep 23 '22 05:09

khampson


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.

like image 45
Jay Avatar answered Sep 22 '22 05:09

Jay