PgAdmin is a useful tool to connect to our PostgreSQL server instance, it comes with a lot of features and functions that are useful for managing and visualizing our PostgreSQL databases.
Installing our PostgreSQL environment
If we haven’t done it before and we are making a testing environment, please take a look at this tutorial to install our PostgreSQL test environment in a Docker Container.
Connecting to our database from PgAdmin
After everything is setup, we can enter our PgAdmin console by running http://127.0.0.1:5050 (If you stick to our previous tutorial, if not, please see what port did you use).
This will open a PgAdmin console:
Ok, now let’s connect to our database, right-click where it says “Servers” and then > Register > Server…
Let’s define a name to our PostgreSQL server and then go to the connection tab
In hostname we will input our host IP address (If you follow our tutorial it would be the local IP address of your machine), then the username and the password.
After doing this and clicking save we should see our database in our console under servers as you see here:
Creating a Schema
Now we have our server showing our databases, we should create and Schema in it.
A schema is a collection of logical structures of data. Inside a schema, we can have views, tables, functions, scripts, constraints, indexes, sequences, etc.
We would focus on just the table right now. So right-click on the database and let’s create our first Schema!
Right-click on our database and then Create > Schema…
After doing this, a popup will appear asking us for the name of our Schema, also in this popup we can define the security and privileges of our Schema, but right now we are focusing on our first table, so we will just put a name to our schema and click Save.
Creating a table
Under Schemas, in our database, our Schema will be shown, now let’s right-click it and then Create -> Table
After doing this, the first thing we want to do is name our Table, so in name we are going to put “MyFirstTable”, please notice that you can also define the Tablespace and if the table is Partitioned in this tab.
Creating the columns in our Table
Let’s go now to columns tab and click the plus sign to add our firsts columns
Our first column would be id and we would like this column to be an integer or a bigint not nullable and set as primary key.
After doing this, let’s click on the pencil, go to constraints tab and define it as identity with a 1 autoincrement starting in 1
Let’s click on the pencil again and click the plus sign to add a new column, let’s call it “firstname”
Our column firstname will be a text, let’s do the same for “lastname”
One of the huge advantages of using PostgreSQL is we can define array variables, so let’s make use of it and also add another column called “owed_money” that will be an array of money.
Let’s click save and we have our first table created!
Inserting data into our table
Right-click in our table and then click Scripts > INSERT Script
You should now see something like this:
We know the id will be an autoincrement, so we don’t need to insert that, also, we know owed_money is an array value, and array values should be between {} in PostgreSQL, so let’s change that query to this one:
INSERT INTO "MyFirstSchema"."MyFirstTable"(
firstname, lastname, owed_money)
VALUES ('john', 'doe', '{10.00,21.31,31.11}');After doing this, we should receive a message saying ‘Query returned successfully in … msec.’
Now let’s change that query again and put this one instead:
(TIP: You can just highlight the part of the query you want to execute and click the play button, it will only execute the part you selected)
SELECT * FROM "MyFirstSchema"."MyFirstTable";If all goes well, we will see this output:
TIP: To sum the array you could use the next query:
SELECT id,firstname, lastname, (SELECT SUM(s) FROM UNNEST(owed_money) s) as total_owed_money from "MyFirstSchema"."MyFirstTable";
The output would be this one:
Dropping our table.
To drop our table, at this point you might guess this one, right-click on the table and Delete/Drop table.
Conclusion
PgAdmin 4 is a powerful database manager, you can do almost anything with some clicks, it’s a real advantage and you should use it to access your PostgreSQL servers (If you want to make your life easier)
If you get to this point, thank you for reading!
If you want to know more about PgAdmin there’s a lot of documentation on their website
See you at the next one!
