EN
PostgreSQL - Alter Table
0 points
In this article, we would like to show you how to modify a table in PostgreSQL.
Quick solution:
xxxxxxxxxx
1
ALTER TABLE "table_name"
2
ADD "column_name" DATA_TYPE;
xxxxxxxxxx
1
ALTER TABLE "table_name"
2
ALTER COLUMN "column_name" TYPE DATA_TYPE;
xxxxxxxxxx
1
ALTER TABLE "table_name"
2
DROP COLUMN "column_name";
Note:
Go to the official documentation to see what you can use as
DATA_TYPE
.
To show how the ALTER TABLE
statement works, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will add email
column to the users
table.
Query:
xxxxxxxxxx
1
ALTER TABLE "users"
2
ADD "email" VARCHAR(255);
Output (SELECT * FROM "users"
):
In this example, we will delete the department_id
column.
Query:
xxxxxxxxxx
1
ALTER TABLE "users"
2
DROP COLUMN "department_id";
Output (SELECT * FROM "users"
):
In this example, we will modify salary
column datatype from DECIMAL
to VARCHAR
.
Query:
xxxxxxxxxx
1
ALTER TABLE "users"
2
ALTER COLUMN "salary" TYPE VARCHAR(255);
Output:
create_tables.sql
file:
xxxxxxxxxx
1
CREATE TABLE "users" (
2
"id" SERIAL,
3
"name" VARCHAR(50) NOT NULL,
4
"surname" VARCHAR(50) NOT NULL,
5
"department_id" INTEGER,
6
"salary" DECIMAL(15,2) NOT NULL,
7
PRIMARY KEY ("id")
8
);
insert_data.sql
file:
xxxxxxxxxx
1
INSERT INTO "users"
2
( "name", "surname", "department_id", "salary")
3
VALUES
4
('John', 'Stewart', 1, '3512.00'),
5
('Chris', 'Brown', 2, '1344.00'),
6
('Kate', 'Lewis', 3, '6574.00'),
7
('Ailisa', 'Gomez', NULL, '6500.00'),
8
('Gwendolyn', 'James', 2, '4200.00'),
9
('Simon', 'Collins', 4, '3320.00'),
10
('Taylor', 'Martin', 2, '1500.00'),
11
('Andrew', 'Thompson', NULL, '2100.00');