How to Create a Duplicate Row in PostgreSQL (With Example)

Often in PostgreSQL you may want to create a duplicate row in a table.

You can use the following syntax to do so:

INSERT INTO athletes (athleteID, team, points)
SELECT (SELECT MAX(athleteID) + 1 FROM athletes), team, points
FROM athletes
WHERE athleteID = 8;

This particular example creates a duplicate of the row where the athleteID column has a value of 8.

Since the athleteID column is the primary key in this particular table, we used SELECT MAX(athleteID) + 1 to assign the next available value as the primary key for this new row.

The following example shows how to use this syntax in practice.

Example: How to Create a Duplicate Row in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Kings', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Celtics', 10);
INSERT INTO athletes VALUES (0006, 'Nuggets', 14);
INSERT INTO athletes VALUES (0007, 'Nuggets', 20);
INSERT INTO athletes VALUES (0008, 'Nuggets', 26);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points 
-----------+---------+--------
         1 | Mavs    |     22
         2 | Hawks   |     30
         3 | Kings   |     12
         4 | Kings   |     25
         5 | Celtics |     10
         6 | Nuggets |     14
         7 | Nuggets |     20
         8 | Nuggets |     26

Suppose that we would like to create a duplicate row of the row where the athleteID column has a value of 8.

We can use the following syntax to do so:

INSERT INTO athletes (athleteID, team, points)
SELECT (SELECT MAX(athleteID) + 1 FROM athletes), team, points
FROM athletes
WHERE athleteID = 8;

-- view updated table
SELECT * FROM athletes;

 athleteid |  team   | points 
-----------+---------+--------
         1 | Mavs    |     22
         2 | Hawks   |     30
         3 | Kings   |     12
         4 | Kings   |     25
         5 | Celtics |     10
         6 | Nuggets |     14
         7 | Nuggets |     20
         8 | Nuggets |     26
         9 | Nuggets |     26

When we view the updated table we can see that a new row has been added with an athleteID value of 9 whose values are an exact duplicate of the values in the row with an athleteID value of 8.

You can use similar syntax to create a duplicate row in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Count Duplicate Rows in Table
PostgreSQL: How to Find Duplicate Records in a Table
PostgreSQL: How to Create a Duplicate Column in a Table