PostgreSQL - ALTER Table - PostgreSQL - dyclassroom

In this tutorial we will learn to alter table in PostgreSQL.

We use the ALTER TABLE table_name command in PostgreSQL to alter tables. In this tutorial we will cover the following.

Add new column
Modify column
Rename column
Rename and modify column
Drop column
Add unique key
Drop unique key
Add index
Drop index
Add foreign key
Drop foreign key

In the following tutorial we will work with the employee table that we created in the CREATE Table tutorial.

This is how our employee table looks now.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

Add new column

Let's add a new column birthday of date data type.

alter table employee 
add column birthday date null;

Now our table will look like the following.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int4 NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  birthday date NULL,
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

Modify column

To modify column in PostgreSQL we use the ALTER COLUMN command.

Let's modify the birthday column that we added to the employee table by changing its type from DATE to TIMESTAMP.

alter table employee 
alter column birthday type timestamp;

Now our table will look like the following.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int4 NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  birthday timestamp NULL,
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

Rename column

To rename a column in PostgreSQL we use the RENAME COLUMN command.

Let's rename the birthday column to dateOfBirth.

alter table employee 
rename column birthday to "dateOfBirth";

Now our table will look like the following.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int4 NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  "dateOfBirth" timestamp NULL,
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

Rename and modify column

Let's rename the dateOfBirth column back to birthday and modify the column type from TIMESTAMP to DATE.

alter table employee
rename column "dateOfBirth" to birthday;

alter table employee 
alter column birthday type date;

Now our table will look like the following.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int4 NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  birthday date NULL,
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

Drop column

To drop a column from a table we use the DROP COLUMN command.

Let's say we have a temp column in the employee table and we want to drop it.

Let the temp column be of boolean data type.

I added the temp column using the following command.

alter table employee 
add column "temp" bool default false;

Right now the table looks like the following.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int4 NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  birthday date NULL,
  "temp" bool NULL DEFAULT false,
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

Now we will drop the temp column from the table.

alter table employee
drop column "temp";

Add unique key

To ensure that a column contains distinct values we add the UNIQUE constraint.

In the following example we are adding a new column email to the employee table.

alter table employee
add column "email" varchar(255);

Now let's add the unique constraint.

alter table employee 
add constraint "email_employee_unique" unique("email");

Now, our table will look like the following.

CREATE TABLE employee (
  "employeeId" varchar(20) NOT NULL,
  "firstName" varchar(100) NOT NULL,
  "lastName" varchar(100) NOT NULL,
  score int4 NULL DEFAULT 0,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  birthday date NULL,
  email varchar(255) NULL,
  CONSTRAINT email_employee_unique UNIQUE (email),
  CONSTRAINT "employeeId_employee_pk" PRIMARY KEY ("employeeId")
);

Drop unique key

To drop a unique key constraint we use the DROP CONSTRAINT command.

For example, if we want to drop the UNIQUE constraint from the email column by the symbol email_employee_unique we will run the following command.

alter table employee 
drop constraint "email_employee_unique";

Add index

We can add index to table columns to speed up search. We do it using the CREATE INDEX command.

In the following example we are adding an index to the firstname column.

create index "idx_firstName_employee"
on employee("firstName");

Drop index

To drop an index from a column we use the DROP INDEX command.

In the following example we are dropping the idx_firstName_employee index from the firstname column.

drop index "idx_firstName_employee";

Add foreign key

To add a foreign key to a table we use the ADD CONSTRAINT ... FOREIGN KEY ... command.

In the CREATE Table tutorial we created the comments table which looks like the following at the moment.

CREATE TABLE "comments" (
  "commentId" serial NOT NULL,
  "commentBody" text NOT NULL,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  CONSTRAINT "commentId_comments_pk" PRIMARY KEY ("commentId")
);

Let's add a new column employeeId to the comments table. It will be the foreign key and will refer to the employeeId column of the employee table.

alter table "comments" 
add column "employeeId" varchar(20);

Now we will add the foreign key constraint.

alter table "comments" 
add constraint "employeeId_comments_fk"
foreign key ("employeeId") 
references "employee" ("employeeId")
on delete cascade on update cascade;

We have ON DELETE CASCADE ON UPDATE CASCADE which means if the employeeId in the employee table is updated or deleted then it will be reflected to the comments table.

Now, our comments table will look like the following.

CREATE TABLE public."comments" (
  "commentId" serial NOT NULL,
  "commentBody" text NOT NULL,
  "lastModifiedAt" timestamp NULL,
  "createdAt" timestamp NOT NULL,
  "employeeId" varchar(20) NULL,
  CONSTRAINT "commentId_comments_pk" PRIMARY KEY ("commentId"),
  CONSTRAINT "employeeId_comments_fk" FOREIGN KEY ("employeeId") REFERENCES employee("employeeId") ON UPDATE CASCADE ON DELETE CASCADE
);

Drop foreign key

To drop or delete a foreign key contraint we use the DROP CONSTRAINT command.

For example, if we want to drop the foreign key constraint employeeId_comments_fk from the comments table we will run the following command.

alter table "comments" 
drop constraint "employeeId_comments_fk";