PostgreSQL: How to Create a Duplicate Column in a Table

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

You can use the following syntax to do so:

ALTER TABLE athletes ADD COLUMN "points_dup" INTEGER;
UPDATE athletes SET points_dup = points;

This particular example creates a new column in the athletes table named points_dup that is an exact duplicate of the existing points column.

Note: Make sure that the data type specified after the ADD COLUMN statement matches the data type of the existing column that you wish to duplicate.

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

Example: How to Create a Duplicate Column 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 column whose values match the values from the points column.

We can use the following syntax to do so:

--add duplicate column named "points_dup"
ALTER TABLE athletes ADD COLUMN "points_dup" INTEGER;
UPDATE athletes SET points_dup = points;

-- view updated table
SELECT * FROM athletes;

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

When we view the updated table we can see that a new column has been created named points_dup whose values match the values from the original points column.

You can use similar syntax to create a duplicate column 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 Count Null Values in Column
PostgreSQL: How to Count Values by Range