Table Creation Basics: CREATE TABLE

In the first lectures, we covered the basics of relational databases and PostgreSQL, learned how to work with psql and pgAdmin, and figured out how to create and manage databases. Now it’s time to move on to the main “building blocks” of relational databases – tables. Tables are where all the info lives, and later we’ll be able to filter, sort, or join it as we want.

You can create tables in PostgreSQL using the CREATE TABLE command. This command describes the table’s structure: its name, columns, and their data types.

General Command Structure

Here’s what the basic syntax looks like:

CREATE TABLE table_name (
    column1 data_type constraints,
    column2 data_type constraints,
    ...
);

Let’s break it down:

• table_name – the name of the table (for example, students).
• column1 – the name of a column (for example, id, name).
• data_type – the data type of the column (for example, INTEGER, VARCHAR).
• constraints – data constraints (for example, NOT NULL, PRIMARY KEY).

Now let’s create our first table.

Table Creation Example

Scenario: In our learning database, we need to store info about students. So let’s create a students table with these fields:

• id: unique student identifier (auto-incremented);
• name: student’s name (up to 100 characters);
• age: student’s age (positive integer).

CREATE TABLE students (
    id SERIAL PRIMARY KEY,  -- unique student ID
    name VARCHAR(100),      -- name
    age INTEGER             -- age
);

What’s going on here:

1. id SERIAL PRIMARY KEY: creates an id column that auto-generates unique numbers (1, 2, 3...) for each row. It’s also the primary key for the table.
2. name VARCHAR(100): creates a text column, where VARCHAR(100) means the string can be up to 100 characters long.
3. age INTEGER: creates a numeric column for storing age.

Now, if you run this query in psql or pgAdmin, the students table will be created.

Data Types for Columns

When creating a table, it’s important to pick the right data type for each column. PostgreSQL offers a bunch of types, but let’s start with the most popular ones.

Choosing the Right Data Type

• If you’re storing numbers – use INTEGER.
• For strings with a known length limit – go with VARCHAR(n).
• For unlimited text – use TEXT.
• Store dates and times in DATE or TIMESTAMP.

Example: Creating a courses Table

Let’s say we also need to store info about courses in our database. Each course has:

• id: unique identifier;
• name: course name (up to 100 characters);
• description: course description (long text);
• start_date: course start date.

SQL code:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,      -- unique course ID
    name VARCHAR(100),          -- course name
    description TEXT,           -- description
    start_date DATE             -- start date
);

Practice Task

Now it’s your turn to create a table! Here’s your task:

1. Create a teachers table with these fields:
   • id: unique identifier.
   • name: teacher’s name (up to 50 characters).
   • subject: subject they teach (up to 50 characters).
   • hire_date: hire date.

Sample code to use:

CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    subject VARCHAR(50),
    hire_date DATE
);

After you run it, use the \d teachers command in psql to make sure the table was created.

Common Mistakes When Creating Tables

When you’re just starting out with tables, mistakes can happen. Here are a few common situations:

Mistake in table or column name. In PostgreSQL, names aren’t case-sensitive, but if you use double quotes " around names, you gotta be careful. For example, CREATE TABLE "MyTable" creates a table called "MyTable", and mytable won’t be recognized as its name.

Wrong data type. If you specify a data type that doesn’t exist, the query will fail.

CREATE TABLE example (
    column1 STRANGE_TYPE
);

This query won’t work because STRANGE_TYPE doesn’t exist.

Forgetting PRIMARY KEY. If you forget to add a primary key, the table will still be created, but it’ll be harder to identify rows.