Modifying Table Columns in PostgreSQL (ALTER COLUMN)

Suppose we have the table employees initially like this:

Column	Data Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
email	VARCHAR(100)
salary	INTEGER

1. Change data type of salary to NUMERIC(10,2)

ALTER TABLE employees
ALTER COLUMN salary SET DATA TYPE NUMERIC(10,2);

Table now looks like:

Column	Data Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
email	VARCHAR(100)
salary	NUMERIC(10,2)

Note: This command does not produce output.

2. Set default value for salary

ALTER TABLE employees
ALTER COLUMN salary SET DEFAULT 50000;

Table now looks like:

Column	Data Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
email	VARCHAR(100)
salary	NUMERIC(10,2)	DEFAULT 50000

Note: This command does not produce output.

3. Remove default value for salary

ALTER TABLE employees
ALTER COLUMN salary DROP DEFAULT;

Table now looks like:

Column	Data Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
email	VARCHAR(100)
salary	NUMERIC(10,2)

Note: This command does not produce output.

4. Add NOT NULL constraint to email

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

Table now looks like:

Column	Data Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
email	VARCHAR(100)	NOT NULL
salary	NUMERIC(10,2)

Note: This command does not produce output.

5. Remove NOT NULL constraint from name

ALTER TABLE employees
ALTER COLUMN name DROP NOT NULL;

Table now looks like:

Column	Data Type	Constraints
id	SERIAL	PRIMARY KEY
name	VARCHAR(100)
email	VARCHAR(100)	NOT NULL
salary	NUMERIC(10,2)

Note: This command does not produce output.