> 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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2Ff3oKBk5NH1J3gimyxStc%2Fimage.png?alt=media\&token=8a02ca51-3316-4b00-994e-c63b29d73934)

* 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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FMvmRfcXct5TsYnINOT7C%2Fimage.png?alt=media\&token=afc2346b-8588-47a6-905b-f633b322902d)

### Creating an the instance to connect in PgAdmin 

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FXcxutE4GH7fpvCeaymSA%2Fimage.png?alt=media\&token=63e48930-4f66-4579-b209-9fea07bd1ed9)

* Under the `General` Tab provide the Name

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FavWRewrLLLifvISpoTH3%2Fimage.png?alt=media\&token=034d2408-d6a7-477e-ba17-3a3e5ec86553)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FXp5SuEpF2EaKsguXNpgB%2Fimage.png?alt=media\&token=53f85948-7940-4374-9175-e1967c46bc4e)

{% 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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FphDSD5pBwDftunx9IxvG%2Fimage.png?alt=media\&token=bccf3524-8d1b-4ff1-99ae-daa568ee5eac)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FqfI9kJ3MNKU8WdOHXLvZ%2Fimage.png?alt=media\&token=b14f4742-1ed6-4034-a50d-e306154d312b)

{% 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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FQhB7Pu1QVBUtADTRAoY3%2Fimage.png?alt=media\&token=c63f5e70-86af-4567-a183-80ee6237ba65)

### Create a Table

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2Fp5Sm4K7z8heAGXaaLlAt%2Fimage.png?alt=media\&token=2bf7816b-ffa1-4aa3-83e1-2cda0a08a969)

* Right Click --> Create --> Table

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F4F2w3oHNUCLiRzBWSmGY%2Fimage.png?alt=media\&token=57541ad8-4186-4364-a25f-bcb467e2a14b)

* Add Table Name under the `General` tab

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FyPc2Z4ypuJLY2Cg6nEpN%2Fimage.png?alt=media\&token=541e5c26-d1d9-4d3c-b912-610ec7db2541)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FMcWK5eutJgUzBpWNJbDZ%2Fimage.png?alt=media\&token=b7ab9e25-6481-4927-bfa9-c4bcb4767718)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FCtA18IspfZUxJ6VUjRnt%2Fimage.png?alt=media\&token=048b8100-4670-45b4-88d9-7c81b0970ab7)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F0pbDfPPJIJFdujR1zJJb%2Fimage.png?alt=media\&token=8cd9141b-007c-4f94-b7d2-95e82d26cd96)

* 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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2F4dPPaeQ4E1UHBvuQabSS%2Fimage.png?alt=media\&token=c44fc74c-2ea2-49f0-bfe7-0c4c607121eb)

### Adding a brand new column

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FK6vF7rlsGxCjcD3pjvdw%2Fimage.png?alt=media\&token=3dd5bec2-028e-4dcb-9291-0f446ba47eb8)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FK6BokjJSkrqz2DOgeOI8%2Fimage.png?alt=media\&token=2840ddf0-1798-406b-b5ba-530bd0551534)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FByGloD8Q8G60jAxbX4UL%2Fimage.png?alt=media\&token=97dfff15-9a02-4f70-9b0b-b34f2eb97734)

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

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FyYMLVJLxFjXmRcTAo8km%2Fimage.png?alt=media\&token=3273b20b-1704-4ede-9630-31eaeefcc0fe)

* How it should look like at the end:

![](https://3885248957-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FoE4wMO1dMVDOGDjh0En7%2Fuploads%2FqxkcsCPLOExXFslYKpCa%2Fimage.png?alt=media\&token=2f09234d-fc34-45f0-8eb3-ae1dab5e4eac)