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.
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")
);
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")
);
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")
);
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")
);
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")
);
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";
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")
);
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";
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");
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";
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
);
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";
ADVERTISEMENT