Create Table

Tables are the building blocks of a relational database. Every piece of data in PostgreSQL is stored in a table. Think of a table like a spreadsheet — it has columns that define what kind of data is stored, and rows that hold the actual records.

Creating Your First Table

Let us create a table to store student information:

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  date_of_birth DATE,
  enrollment_date DATE DEFAULT CURRENT_DATE
);

Let us break this down:

• id SERIAL PRIMARY KEY — An auto-incrementing integer that uniquely identifies each row. PostgreSQL handles the numbering automatically.
• VARCHAR(50) — A text column with a maximum length of 50 characters.
• NOT NULL — This column cannot be empty.
• UNIQUE — No two rows can have the same value in this column.
• DEFAULT CURRENT_DATE — If no value is provided, use today's date.

Viewing Tables

To see all tables in the current database:

\dt

To see the structure of a specific table:

\d students

This shows you the column names, data types, and constraints. Very handy when you forget how a table is structured.

Modifying Tables

After creating a table, you can add, remove, or modify columns:

Add a Column

ALTER TABLE students ADD COLUMN phone VARCHAR(20);

Remove a Column

ALTER TABLE students DROP COLUMN phone;

Change a Column Type

ALTER TABLE students ALTER COLUMN email TYPE VARCHAR(150);

Rename a Column

ALTER TABLE students RENAME COLUMN first_name TO given_name;

Dropping a Table

To delete a table and all its data:

DROP TABLE students;

If you want to avoid an error when the table does not exist:

DROP TABLE IF EXISTS students;

Be very careful with DROP. Once a table is dropped, all its data is gone. There is no undo.

Best Practices

Here are some tips for designing good tables:

• Always have a primary key. It uniquely identifies each row.
• Use meaningful column names. first_name is better than fn.
• Choose appropriate data types. Do not use VARCHAR(1000) for a two-letter country code.
• Add NOT NULL constraints where empty values do not make sense.
• Use foreign keys to link related tables together.