PostgreSQL Select and ALTER TABLE Statements

Day - 2

Select Data

To retrieve data from a data base, we use the SELECT statement.

Specify Columns

By specifying the column names, we can choose which columns to select:

select name,role from employee;

Return ALL Columns

Specify a * instead of the column names to select all columns:

select * from employee

The ALTER TABLE Statement

To add a column to an existing table, we have to use the ALTER TABLE statement.

• The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
• The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

ADD COLUMN

Alter table employee
add salary int

we can all Multiple columns

Alter table employee
add Department varchar(25),
add gender varchar(20);

to Rename column

alter table employee
rename name to emp_name

to Rename a table

alter table employee
rename to employee_details

To drop table

alter table employee_details
drop column department

Add NOT NULL

alter table employee_details
alter column emp_name set not null

Remove NOT NULL

alter table employee_details
alter column emp_name drop not null

Add and Remove DEFAULT value

alter table emp_details
alter column salary set default 25000;

alter table emp_details
alter column salary drop default ;

Setting a DEFAULT value only applies to new rows inserted into the table after the constraint is added. It does not update existing rows that already contain NULL values.

Add Primary Key

alter table emp_details
add primary key(id);

Alter Table statment used to change Table Structure

ADD COLUMN
DROP COLUMN
RENAME COLUMN ... TO
ALTER COLUMN ... TYPE
ALTER COLUMN ... SET NOT NULL
ALTER COLUMN ... DROP NOT NULL
ALTER COLUMN ... SET DEFAULT
ALTER COLUMN ... DROP DEFAULT
ADD CONSTRAINT ... UNIQUE
ADD CONSTRAINT ... CHECK