This content originally appeared on DEV Community and was authored by Hardik Kanajariya

Day 3: Creating Your First Database and Understanding Schema

Now that PostgreSQL is installed, let's dive into creating databases and understanding their structure!

What is a Database?

A database is a container that holds related data organized into tables. Think of it as a digital filing cabinet where each drawer (table) contains specific types of information.

Creating Your First Database

Using psql (Command Line)

-- Connect to PostgreSQL
psql -U postgres

-- Create a new database
CREATE DATABASE my_first_db;

-- List all databases
\l

-- Connect to the new database
\c my_first_db

Using pgAdmin

1. Open pgAdmin
2. Right-click on "Databases" under your server
3. Select "Create" → "Database"
4. Enter name: my_first_db
5. Click "Save"

Understanding Database Components

1. Schemas

A schema is a namespace within a database. Every database has a default public schema.

-- View all schemas
\dn

-- Create a new schema
CREATE SCHEMA sales;

-- Create a schema for HR
CREATE SCHEMA hr;

Why use schemas?

• Organize tables logically
• Separate development from production
• Control access permissions

2. Tables

Tables store actual data in rows and columns.

-- Create your first table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10, 2)
);

Let's break this down:

• SERIAL: Auto-incrementing integer
• PRIMARY KEY: Unique identifier for each row
• VARCHAR(n): Variable-length text (max n characters)
• UNIQUE: Ensures no duplicates
• DATE: Calendar date
• DECIMAL(10,2): Numbers with precision (10 digits, 2 after decimal)

3. Data Types Overview

Let's Create a Sample Database

-- Create a library database
CREATE DATABASE library_db;

-- Connect to it
\c library_db

-- Create books table
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(100) NOT NULL,
    isbn VARCHAR(13) UNIQUE,
    published_date DATE,
    pages INTEGER,
    available BOOLEAN DEFAULT TRUE
);

-- Create members table
CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    join_date DATE DEFAULT CURRENT_DATE
);

Viewing Your Database Structure

-- List all tables
\dt

-- Describe a specific table
\d employees

-- View table with indexes and constraints
\d+ employees

Dropping (Deleting) Databases and Tables

⚠️ WARNING: These commands permanently delete data!

-- Drop a table
DROP TABLE IF EXISTS table_name;

-- Drop a database (disconnect first!)
\c postgres
DROP DATABASE IF EXISTS my_first_db;

Best Practices for Database Design

✅ Use meaningful names: customer_orders not tbl1
✅ Follow naming conventions: lowercase with underscores
✅ Always include PRIMARY KEY: Ensures unique identification
✅ Use appropriate data types: Don't store dates as text!
✅ Add NOT NULL where needed: Prevent incomplete data
✅ Document your schema: Use comments

COMMENT ON TABLE employees IS 'Stores employee information';
COMMENT ON COLUMN employees.salary IS 'Annual salary in USD';

Tomorrow's Preview: Day 4 - Inserting and Querying Data (Your First CRUD Operations)

Practice Exercise:
Create a database called school_db with two tables:

1. students (student_id, name, email, enrollment_date)
2. courses (course_id, course_name, credits)

Share your table creation queries in the comments! 🎓

This content originally appeared on DEV Community and was authored by Hardik Kanajariya

Print Share Comment Cite Upload Translate Updates

APA

Hardik Kanajariya | Sciencx (2025-10-21T02:30:00+00:00) Day 3: Creating Your First Database and Understanding Schema. Retrieved from https://www.scien.cx/2025/10/21/day-3-creating-your-first-database-and-understanding-schema-2/

MLA

" » Day 3: Creating Your First Database and Understanding Schema." Hardik Kanajariya | Sciencx - Tuesday October 21, 2025, https://www.scien.cx/2025/10/21/day-3-creating-your-first-database-and-understanding-schema-2/

HARVARD

Hardik Kanajariya | Sciencx Tuesday October 21, 2025 » Day 3: Creating Your First Database and Understanding Schema., viewed ,<https://www.scien.cx/2025/10/21/day-3-creating-your-first-database-and-understanding-schema-2/>

VANCOUVER

Hardik Kanajariya | Sciencx - » Day 3: Creating Your First Database and Understanding Schema. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/10/21/day-3-creating-your-first-database-and-understanding-schema-2/

CHICAGO

" » Day 3: Creating Your First Database and Understanding Schema." Hardik Kanajariya | Sciencx - Accessed . https://www.scien.cx/2025/10/21/day-3-creating-your-first-database-and-understanding-schema-2/

IEEE

" » Day 3: Creating Your First Database and Understanding Schema." Hardik Kanajariya | Sciencx [Online]. Available: https://www.scien.cx/2025/10/21/day-3-creating-your-first-database-and-understanding-schema-2/. [Accessed: ]

rf:citation

» Day 3: Creating Your First Database and Understanding Schema | Hardik Kanajariya | Sciencx | https://www.scien.cx/2025/10/21/day-3-creating-your-first-database-and-understanding-schema-2/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.