Creating Databases and Tables in PostgreSQL

# CHAPTER 4

Creating Databases and Tables in PostgreSQL #

1. Introduction #

2. Learning Objectives #

• Use CREATE DATABASE to initialize a new database.

• Switch between databases using the CLI.

• Use CREATE TABLE to architect columns and data types.

• Understand basic PostgreSQL syntax rules.

• Use DROP TABLE to permanently delete structures.

3. Creating a Database #

Open the pgAdmin Query Tool (or the psql CLI) and type:

CREATE DATABASE university_db;

*Note: In pgAdmin, you may need to right-click the "Databases" tab and hit "Refresh" to see your new university_db appear in the left sidebar.*

Important: If you are using psql (the command line), you must explicitly connect to the new database before creating tables inside it. Type \c university_db to switch connections. In pgAdmin, simply open a new Query Tool specifically on the university_db node.

4. Creating a Table #

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    enrollment_year INTEGER,
    is_active BOOLEAN
);

Breaking down the syntax:

• CREATE TABLE students: Declares the name of the table.

• ( ... ): All columns are defined inside parentheses.

• student_id SERIAL PRIMARY KEY: SERIAL tells PostgreSQL to automatically increment the ID (1, 2, 3). PRIMARY KEY makes it the unique identifier.

• VARCHAR(50): Text that is a maximum of 50 characters long.

• ,: Every column definition is separated by a comma. (Do NOT put a comma after the final column!).

• ;: Every SQL statement must end with a semicolon.

5. Viewing Table Structures #

• In psql (CLI): Type \d students

• In pgAdmin: Expand university_db -> Schemas -> public -> Tables. Right-click students and select Properties.

6. Deleting a Table (DROP) #

-- DANGER: This is irreversible!
DROP TABLE students;

-- Safer version (prevents an error if the table doesn't exist)
DROP TABLE IF EXISTS students;

7. Mini Project: Student Management Schema #

-- 1. Create the Parent Table
CREATE TABLE professors (
    prof_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

-- 2. Create the Child Table
CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    credits INTEGER,
    prof_id INTEGER, -- The Foreign Key column
    
    -- The Mathematical Link!
    FOREIGN KEY (prof_id) REFERENCES professors(prof_id)
);

*(Notice we created professors first. You cannot create a child table that references a parent if the parent doesn't exist yet!)*

8. Common Mistakes #

• Trailing Commas: The most common syntax error for beginners is leaving a comma after the final column definition.

• Case Sensitivity: Unquoted identifiers (like table names) are automatically folded to lowercase in PostgreSQL. If you type CREATE TABLE Users, PostgreSQL saves it as users. Do not use Capital letters or Spaces in table names. Use snake_case.

9. Best Practices #

• Use IF NOT EXISTS: When writing automated deployment scripts, always use CREATE TABLE IF NOT EXISTS users. This prevents the script from crashing if it is run twice by accident.

10. Exercises #

1. 1. Write the SQL command to create a departments table with an auto-incrementing primary key and a department name.

1. 2. In the psql command line tool, what shortcut command is used to list the columns and structure of a specific table?

11. SQL Challenges #

DROP DATABASE IF EXISTS old_test_db;

12. MCQ Quiz with Answers #

13. Interview Questions #

• Q: Explain what DDL (Data Definition Language) is and provide three examples of DDL commands in PostgreSQL.

• Q: Describe the architectural naming conventions you would enforce when creating Tables and Columns in a PostgreSQL enterprise database.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #