PostgreSQL
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.
ALTER TABLE table_name
In the following tutorial we will work with the employee table that we created in the CREATE Table tutorial.
employee
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") );
Let's add a new column birthday of date data type.
birthday
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") );
To modify column in PostgreSQL we use the ALTER COLUMN command.
ALTER COLUMN
Let's modify the birthday column that we added to the employee table by changing its type from DATE to TIMESTAMP.
DATE
TIMESTAMP
alter table employee alter column birthday type timestamp;
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") );
To rename a column in PostgreSQL we use the RENAME COLUMN command.
RENAME COLUMN
Let's rename the birthday column to dateOfBirth.
dateOfBirth
alter table employee rename column birthday to "dateOfBirth";
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") );
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;
To drop a column from a table we use the DROP COLUMN command.
DROP COLUMN
Let's say we have a temp column in the employee table and we want to drop it.
temp
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";
To ensure that a column contains distinct values we add the UNIQUE constraint.
UNIQUE
In the following example we are adding a new column email to the employee table.
email
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") );
To drop a unique key constraint we use the DROP CONSTRAINT command.
DROP CONSTRAINT
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.
email_employee_unique
alter table employee drop constraint "email_employee_unique";
We can add index to table columns to speed up search. We do it using the CREATE INDEX command.
CREATE INDEX
In the following example we are adding an index to the firstname column.
firstname
create index "idx_firstName_employee" on employee("firstName");
To drop an index from a column we use the DROP INDEX command.
DROP INDEX
In the following example we are dropping the idx_firstName_employee index from the firstname column.
idx_firstName_employee
drop index "idx_firstName_employee";
To add a foreign key to a table we use the ADD CONSTRAINT ... FOREIGN KEY ... command.
ADD CONSTRAINT ... FOREIGN KEY ...
In the CREATE Table tutorial we created the comments table which looks like the following at the moment.
comments
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.
employeeId
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.
ON DELETE CASCADE ON UPDATE CASCADE
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 );
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.
employeeId_comments_fk
alter table "comments" drop constraint "employeeId_comments_fk";