Altering table columns

alter is the keyword allow us to make changes in the table structures and database

we can rename tables, make changes in the columns names, inserting new columns, removing existing columns for all those activites alter keywords will allow us to do it.

let see how to do rename a column

to see tables in our connect database we can see \dl

\dt

here we can see that there is only one table in this database, that is employee

create table dummytable(id int);

\dt

now we see that we have two tables, that are dummytable and employee

let we see how to rename a table

alter is the keyword to indicate we are actually going to change something and we want to specify what we are actually going to change by using the alter keyword whether we are changing the table or database.

After if we specify table this query will going to work for the table, other wise it we specify table it will work for the table

finally we want say for which table we are going to this work by name of the table, here we will do for the dummytable

the rename keyword explicity specifies our table activity is rename activity and at last we want to give the new name of the table using the to keyword

alter table dummytable rename to tabledummy; 

now check our table does the name was changed or not

this time the dummytable is changed into tabledummy

it shows error for doing same thing for again

adding new columns

for now we have only one column and it's type is intger

let see how to add new column

after specifying the table designigation we want to say which changes we are going to do, if we want to add new columns we want say that ADD COLUMN and new column name and its type

alter table tabledummy add column name varchar(20);

here we are specifying the our new column name is as name and it type as varchar with limit 20

to see the changes in the tabledummy

note only one columns we can add more then one column at the same type

alter table tabledummy 
add column age smallint
add column city varchar(20)
add column phone bigint;

NOTE do not forget to sperate the each columns by comma

alter table tabledummy 
add column age smallint,
add column city varchar(20),
add column phone bigint;

to check the alters

for saft querying we can use the IF NOT EXISTS

ALTER TABLE tabledummy
ADD COLUMN IF NOT EXISTS age smallint;

ALTER TABLE tabledummy
ADD COLUMN IF NOT EXISTS age smallint,
ADD COLUMN IF NOT EXISTS city varchar(20);

ALTER TABLE tabledummy
ADD COLUMN IF NOT EXISTS age smallint,
ADD COLUMN IF NOT EXISTS city varchar(20),
ADD COLUMN IF NOT EXISTS email varchar(20);

remove a columns

in new column inserting query if we replace add as drop it will work as the drop query, do not forget that we do not need the data type for the removing the columns

alter table tabledummy 
drop column city,
drop column phone;

just like safe column inserting we can perfor the safe removing the column

\d tabledummy

alter table tabledummy 
drop column if exists age;

alter table tabledummy 
drop column if exists name;
drop column if exists age;
drop column if exists email;

\d tabledummy

also we can conver the type of the table column to another type we will see about it later