Managing Postgres with PgAdmin GUI

General Overview

• When you first connect you will have to set up the PgAdmin master password. This is separate from the Database password

• It will also set up the default postgres database connection

• The password that you have to enter, is the password that you have set up during the installation

• Now you are connected to the database

Creating an the instance to connect in PgAdmin

• Right Click Servers

• Click Register --> Click Server

• Under the General Tab provide the Name

• Under the Connection Tab specify localhost and password

Note: If the instance would run on AWS or other cloud provider, you would specify the IP or Domain Name to connect to under Hostname/address

Create a new Database

• Go under Databases

• Click Create --> Database

• Add Database name (Usually good to be after the app name)

Note: If you go under the SQL tab, it will provide you with the SQL statement to create the database

CREATE DATABASE fastapi
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

• Database has been created successfully

Create a Table

• Connect to the Database

• Click Schemas --> Public --> Tables

• Right Click --> Create --> Table

• Add Table Name under the General tab

• We need to define a column as well under the Columns tab

• Columns --> Click + --> Add Name --> Add DataType --> Think if data can be NULL

• Repeat with new column for how the PRODUCTS table should be defined

To see the differences between the Data Types in Postgres:

Click here!

Example:

We have 3 types of INTEGER

• integer

• bigint

• smallint

The main difference between these is the amount of bits that we have

• The maximum number to which we go to is going to be much higher or smaller depending on which you use

• There is an additional DataType called serial which will create our IDs in a logical order without specifying the ID every time

• This will increment by +1

How a PRODUCTS table should look logically:

CREATE TABLE public.products
(
    name character varying NOT NULL,
    price integer NOT NULL,
    id serial NOT NULL,
    PRIMARY KEY (id)
);

ALTER TABLE IF EXISTS public.products
    OWNER to postgres;

Better example would be something like this:

CREATE TABLE public.newtable (
	name varchar NOT NULL,
	price integer NOT NULL,
	id serial NOT NULL,
	is_sale boolean NULL DEFAULT false,
	inventory integer NOT NULL DEFAULT 0,
	created_at timestamp with time zone NOT NULL DEFAULT now()
);

• Click save and you've created the table

Interacting with Table

To fetch all Data in table:

Right Click Table --> View/Edit Data --> All Rows

• This also returns the SQL for the query

SELECT * FROM public.products
ORDER BY id ASC 

To Add Row:

Click on + --> Add Data --> Commit

Adding a brand new column

• We will take a look if the product is on sale

• Right Click Table --> Properties

• Create a new Row as before but add the constraint to take the default value as False (so not on sale)

• Because we provided a default value, it has autocompleted the previous entries with the default value

Adding a Timestamp to the Table

• Realistically you would need timestamps to the Table as well - This is best practice

• However we don't want the application to be responsible with this

• We do want Postgres to do this

• How it should look like at the end: