> For the complete documentation index, see [llms.txt](https://docs.arkannis.net/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.arkannis.net/database/postgresql/managing-postgres-with-pgadmin-gui.md).

# 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

![](/files/cnrzqujpxTIjgd3l8nyd)

* 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

![](/files/s5ErFFQ1FGjU0mG4WKuP)

### Creating an the instance to connect in PgAdmin 

* Right Click Servers
* Click Register --> Click Server

![](/files/7K3CpTteYMmvX9U54fNI)

* Under the `General` Tab provide the Name

![](/files/O6dGQ416uWULUUfyHPE1)

* Under the `Connection` Tab specify `localhost` and `password`

![](/files/gglsJZyxyRjFoQMR51t2)

{% hint style="warning" %}
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`
{% endhint %}

### Create a new Database

* Go under Databases
* Click Create --> Database

![](/files/AFclHCmz5kt26Zf6hguB)

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

![](/files/ciTp9hwNsFLf3qQo4jYu)

{% hint style="info" %}
Note: If you go under the SQL tab, it will provide you with the SQL statement to create the database

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

{% endhint %}

* Database has been created successfully

![](/files/RftWeChjwV3P4J9wlJkY)

### Create a Table

* Connect to the Database
* Click Schemas --> Public --> Tables

![](/files/379qOJr0kx1hnIXtRbFb)

* Right Click --> Create --> Table

![](/files/cYRUzw6010fYxyZ4gYjO)

* Add Table Name under the `General` tab

![](/files/gkjGkoH4wkwCzy3DCEi3)

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

![](/files/O9i08VdBLLwTIgN3JftT)

* Columns --> Click `+` --> Add Name --> Add DataType --> Think if data can be NULL
* Repeat with new column for how the `PRODUCTS` table should be defined

{% hint style="info" %}
To see the differences between the Data Types in Postgres:

[Click here!](https://www.postgresql.org/docs/current/datatype.html)

#### 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
  {% endhint %}

* 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:

![](/files/9rHjRYiWG37S2TCyUXg9)

```sql
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;
```

{% hint style="info" %}
Better example would be something like this:

```sql
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()
);
```

{% endhint %}

* 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

![](/files/C48w1GpXH1Pgu7Q5oSoo)

* This also returns the SQL for the query

```sql
SELECT * FROM public.products
ORDER BY id ASC 
```

#### To Add Row:

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

![](/files/k3n4BsEvWXtV4YoprbzH)

### Adding a brand new column

* We will take a look if the product is on sale
* Right Click Table --> Properties

![](/files/5NC5gwUww3pNRa3NNWWP)

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

![](/files/mkbakrcd4T8CzmnsYHuZ)

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

![](/files/us1hURyepjOQGHbttBLa)

### 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

![](/files/LfnPi0jge5Ts15Yg8PCG)

* How it should look like at the end:

![](/files/BrKMhnlR9x11enTLnDqw)