Learning Postgres: Adding and Updating Columns (Part 5)

In the previous part, we saw how to create a table and insert data into it. Let’s explore further, there are still a lot of things to do.

Add column

We forgot to add the color column to the characters table. What now? If the table is empty, it’s easy; we can add a NOT NULL constraint.

However, if there is already data in the table, we need to add the column allowing NULL values.

Alternatively, we can add the column with a default value. In our case, each character will initially have a red color.

ALTER TABLE characters
ADD COLUMN color TEXT NOT NULL DEFAULT 'red';

New column

Update

Alright, but what if we don’t want everyone to have the same red color? What is the next step?

We need to change each row based on certain conditions. For example, if we want to change Mario’s color, we need to find him and then update him using an update query.

UPDATE characters
SET color = 'blue'
WHERE name = 'mario';