Setup PostgreSQL And pgAdmin

Overview

In this post, I will walk you through the setup of your environment and data so you can start quickly.

As in the series introduction, I said that we will use PostgreSQL. The quickest way to set up a PostgreSQL database instance is by using docker-compose.

Setting up PostgreSQL using docker-compose

You can use the following code to setup pgadmin and PostgreSQL:

version: '3.8'

services:
  postgres:
    container_name: postgres_db
    image: postgres:13.6-alpine
    environment:
      POSTGRES_PASSWORD: root
      POSTGRES_USER: root
      POSTGRES_DB: learn_sql

    volumes:
       - postgres:/data/postgres
    restart: always

  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4:6.2
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: root
    volumes:
       - pgadmin:/var/lib/pgadmin
    ports:
      - "38081:80"
    depends_on:
      - postgres
    restart: always

volumes:
    postgres:
    pgadmin:

Here I set up both PostgreSQL and pgadmin in one script. As you can see, I don’t need to export port 5432 for the PostgreSQL container since I will only interact with PostgreSQL through pgadmin. If you want to use other clients (dBeaver for example), feel free to expose a port to connect.

Now, you can go to your command line and run:

docker-compose up -d

After a minute, you can open your browser to access pgadmin:

Access pg admin

If you use my docker-compose yaml above, the login is [email protected] and the password is root

When you are logged in, right click on Servers to add a new server:

Add a new PostgreSQL server
Add a new PostgreSQL server

Set a server name, could be anything
Set a server name, could be anything

Then switch to the Connection tab and set the connection details:

If you follow the configurations of the above yaml file, the details is as below:

Configure Postgresql connection in PgAdmin
Configure Postgresql connection in PgAdmin

The password is obviously root

Click on Save and your database is ready to serve:

If you see something like this, you have been successfully setup Postgresql with pgadmin
If you see something like this, you have been successfully setup Postgresql with pgadmin

Mock data

Through this series, I’m going to use the following data. You can copy the code and run them in the order they appear on this page:

-- Create a table for customers
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20)
);

-- Insert some data into the customers table
INSERT INTO customers (first_name, last_name, email, phone) VALUES
('John', 'Doe', '[email protected]', '555-1234'),
('Jane', 'Doe', '[email protected]', '555-5678'),
('Bob', 'Smith', '[email protected]', '555-9012'),
('Alice', 'Jones', '[email protected]', '555-3456'),
('Tom', 'Brown', '[email protected]', '555-7890');

-- Create a table for orders
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer_id INT REFERENCES customers(customer_id)
);

-- Insert some data into the orders table
INSERT INTO orders (order_date, customer_id) VALUES
('2022-01-01', 1),
('2022-01-02', 2),
('2022-01-03', 3),
('2022-01-04', 4),
('2022-01-05', 5);

-- Create a table for order items
CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_name VARCHAR(100),
    price NUMERIC(10,2),
    quantity INT
);

-- Insert some data into the order_items table
INSERT INTO order_items (order_id, product_name, price, quantity) VALUES
(1, 'Widget A', 9.99, 1),
(1, 'Widget B', 12.99, 2),
(2, 'Widget C', 7.99, 3),
(3, 'Widget D', 5.99, 4),
(4, 'Widget E', 14.99, 1),
(4, 'Widget F', 19.99, 2),
(4, 'Widget G', 4.99, 3),
(5, 'Widget H', 11.99, 4);

-- Let's add even more data:
-- Insert data into the customers table
INSERT INTO customers (first_name, last_name, email, phone)
VALUES 
    ('John', 'Doe', '[email protected]', '555-1234'),
    ('Jane', 'Smith', '[email protected]', '555-5678'),
    ('Bob', 'Johnson', '[email protected]', '555-9012'),
    ('Mary', 'Smith', '[email protected]', '555-3456'),
    ('Tom', 'Smith', '[email protected]', '555-7890'),
    ('Jim', 'Brown', '[email protected]', '555-2345'),
    ('Ann', 'Davis', '[email protected]', '555-6789'),
    ('Susan', 'Jones', '[email protected]', '555-0123'),
    ('David', 'Smith', '[email protected]', '555-4567'),
    ('Karen', 'Williams', '[email protected]', '555-8901');

-- Insert data into the orders table
INSERT INTO orders (order_date, customer_id)
VALUES 
    ('2022-01-01', 1),
    ('2022-01-01', 2),
    ('2022-01-02', 3),
    ('2022-01-02', 4),
    ('2022-01-02', 5),
    ('2022-01-03', 6),
    ('2022-01-03', 7),
    ('2022-01-03', 8),
    ('2022-01-04', 9),
    ('2022-01-05', 10),
    ('2022-01-05', 1),
    ('2022-01-06', 2),
    ('2022-01-06', 3),
    ('2022-01-07', 4),
    ('2022-01-08', 5);

-- Insert data into the order_items table
INSERT INTO order_items (order_id, product_name, price, quantity)
VALUES 
    (1, 'Widget A', 10.99, 2),
    (1, 'Widget B', 19.99, 1),
    (2, 'Widget C', 9.99, 3),
    (3, 'Widget D', 5.99, 2),
    (3, 'Widget E', 8.99, 1),
    (4, 'Widget F', 29.99, 1),
    (4, 'Widget G', 15.99, 2),
    (5, 'Widget H', 12.99, 3),
    (6, 'Widget I', 9.99, 1),
    (7, 'Widget J', 19.99, 2),
    (8, 'Widget K', 14.99, 2),
    (9, 'Widget L', 8.99, 3),
    (10, 'Widget M', 11.99, 1),
    (11, 'Widget N', 6.99, 4),
    (12, 'Widget O', 24.99, 1),
    (13, 'Widget P', 16.99, 2),
    (14, 'Widget Q', 8.99, 3),
    (15, 'Widget R', 12.99,3);

This is the sample database of a shopping site. To run these SQL queries, right click on the database name “learn_sql” and click on Query Tool

Select query tool to run SQL
Select query tool to run SQL

Then, you can paste the queries above and run:

Paste the query and run in Pgadmin
Paste the query and run it in Pgadmin

After clicking Execute/Refresh button, you should see the notification saying the execution was successful.

If you expand Schema->Public->Tables, the tables are there:

Table created in PostgreSQL
Table created in PostgreSQL

That’s it! You are now ready to learn the basics of SQL with PostgreSQL.

Conclusion

In this post, I’ve shown you how to quickly set up PostgreSQL and pgAdmin. In the next post, I’m going to show you how to perform queries to select the right data you need.

Leave a Comment