CREATE TABLE: Make Your Own Table
So far, every table was already created for you. Now you will create your own.
CREATE TABLE makes a new, empty table. You give the table a name, and you list its columns. Each column has a name and a data type.
Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
Example: Create a Students Table
CREATE TABLE students (
student_id INT,
name VARCHAR(50),
age INT
);
Output:
CREATE TABLE
psql answers CREATE TABLE. The table exists now, but it has no rows yet.
Use \d to see the new table's columns:
\d students
Output:
Table "public.students"
Column | Type | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
student_id | integer | | |
name | character varying(50) | | |
age | integer | | |
Change a Table with ALTER TABLE
ALTER TABLE changes a table that already exists. For example, ADD COLUMN adds a new column:
ALTER TABLE students ADD COLUMN email VARCHAR(100);
INSERT INTO students
VALUES (1, 'Riya', 20, '[email protected]');
SELECT * FROM students;
Output:
ALTER TABLE
INSERT 0 1
student_id | name | age | email
------------+------+-----+------------------
1 | Riya | 20 | [email protected]
(1 row)
Remove a Table with DROP TABLE
DROP TABLE deletes a table, with all its rows:
DROP TABLE students;
Output:
DROP TABLE
Be careful. Unlike TRUNCATE, the table itself is gone, not just its rows.
The next lessons add constraints to tables. A constraint is a rule that a column's values must follow.
In the exercise, create a table of your own.