Prev Next

Create Demo Database

This section provides a sample script to create a demo database with a simple schema. It is helpful for testing and learning PostgreSQL features without affecting production data.

Key Topics

1. Creating the Demo Database

First, create a new database named demo_db:

CREATE DATABASE demo_db;

You can connect to demo_db using \c demo_db in psql.

2. Sample Tables

Create a sample customers table and orders table:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_amount NUMERIC(10,2) NOT NULL DEFAULT 0
);

Code Explanation: The orders table references customers(customer_id), creating a foreign key relationship between the two tables.

3. Inserting Sample Data

Add a few rows to the customers and orders tables:

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com'),
       ('Jane', 'Smith', 'jane.smith@example.com'),
       ('Alice', 'Johnson', 'alice.johnson@example.com');

INSERT INTO orders (customer_id, total_amount)
VALUES (1, 99.99),
       (2, 150.00),
       (3, 200.00);

You now have a basic demo database to play with PostgreSQL commands and features.

Best Practices

  • Use demo databases for experimentation or testing new features before deploying to production.
  • Regularly refresh or reset demo data to maintain consistency.
  • Document your demo schemas so other team members can replicate them easily.

Key Takeaways

  • A demo database is a safe environment to explore SQL and PostgreSQL features.
  • Foreign keys can help maintain relational integrity between tables.
  • Experimentation with sample data helps prevent issues in production systems.
Prev Next