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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.arkannis.net/database/postgresql/managing-postgres-with-pgadmin-gui.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.