SQL ET POSTGRE 3.6 : Creating our first table

Now that we know everything we need about databases — primary and foreign keys, constraints and data types — we are ready to build our first real table. We will create a database that holds information about films, actors and directors, with one table for each and a junction table between films and actors. We will get to the junction table later.

Creating the film database

In pgAdmin, on the left, we expand Servers, then PostgreSQL with the matching version. Under Databases there is a default postgres database, but we are not going to work on it. Right-click on Databases, choose Create Database, type the name film and save. You will find your new film database in the tree. Right-click it and choose Create Script — this is where we will write our SQL. Skip a few lines for readability, add a comment, then start with the directors table. 
CREATE TABLE realisateur (
  realisateur_id SERIAL PRIMARY KEY,
  prenom VARCHAR(30),
  nom VARCHAR(30) NOT NULL,
  date_naissance DATE,
  nationalite VARCHAR(20)
);
  • Use CREATE TABLE with the table name, lower case and without accents.
  • Each column gets a name, a type and optional constraints.
  • SERIAL PRIMARY KEY auto-increments your id.
  • Use NOT NULL for mandatory columns like nom.
Type CREATE TABLE realisateur followed by an opening parenthesis and close the statement with a semicolon. Inside, declare the columns one per line: realisateur_id SERIAL PRIMARY KEY, then prenom VARCHAR(30), then nom VARCHAR(30) NOT NULL, then date_naissance DATE and finally nationalite VARCHAR(20). Select the script and run it — the query returns successfully. To verify, run SELECT * FROM realisateur: the table appears with its five columns and the types we defined. Congratulations, you have created your first table.

Summary

This lesson introduces the creation of the first table in a PostgreSQL film database. Using pgAdmin4, the instructor creates a 'directors' table with columns for director ID (primary key using SERIAL type), first name, last name, birth date, and nationality. The lesson demonstrates the complete CREATE TABLE syntax, including data type assignments (VARCHAR, DATE, SERIAL) and constraints (PRIMARY KEY, NOT NULL), then verifies the table creation using a SELECT query.

Key points

  • Use CREATE TABLE statement to define a new table with column names and data types
  • SERIAL type auto-increments the ID field; mark it as PRIMARY KEY for the director table
  • VARCHAR(n) defines variable-length character fields with a maximum length limit (e.g., VARCHAR(30) for names)
  • NOT NULL constraint ensures critical columns like director name cannot contain empty values
  • Use SELECT * FROM table_name to verify table structure and confirm successful creation
  • Organize code with comments and proper formatting when writing SQL scripts for clarity

FAQ

What data types should I use for different film database columns?

Use SERIAL for auto-incrementing ID fields (primary keys), VARCHAR(n) for text fields like names and nationalities (specify max character length), and DATE for birth dates and other temporal data.

Why is the NOT NULL constraint important for the director name column?

NOT NULL ensures that every director record must have a name entered, preventing incomplete or invalid data from being stored in the database.

How do I verify that my table was created successfully?

Execute a SELECT * FROM directors query to display the table structure with all column names and their data types. If the query returns without errors and shows the correct columns, the table was created successfully.