PostgreSQL: Create New Column Based on Another Column

Often in PostgreSQL you may want to create a new column in a table whose values are based on the values from another column.

You can use the following syntax to do so:

ALTER TABLE athletes
ADD COLUMN all_star BOOLEAN;

UPDATE athletes
SET all_star = (points > 20);

This particular example will create a new column named all_star whose rows take on the following values:

• true if the value in the existing points column is greater than 20
• false if the value in the existing points column is not greater than 20

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

Example: Create New Column Based on Another 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, 'Celtics', 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, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Mavs', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);

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

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

Suppose that we would like to create a new column named all_star that takes a value of true if the corresponding value in the points column is greater than 20 or false otherwise.

We can use the following syntax to do so:

--create new column whose values are based on 'points' column
ALTER TABLE athletes
ADD COLUMN all_star BOOLEAN;

UPDATE athletes
SET all_star = (points > 20);

-- view updated table
SELECT * FROM athletes;

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

Notice that a new column has been added named all_star that takes a value of true if the corresponding value in the points column is greater than 20 or false otherwise.

You can use similar syntax to add a new column whose values are based on another column in your own table in PostgreSQL.

Note: In this particular example we created a Boolean column but you can use a similar approach to create a new column with a different data type.

Additional Resources

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

PostgreSQL: How to Count True and False Values
PostgreSQL: Count Occurrences of Each Value in Column
PostgreSQL: Count Number of Occurrences of Character in String