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.