Creating a table in PostgreSQL with PgAdmin

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.

Contents

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:

PgAdmin Login
Let’s input now the credentials we define, we should be looking at this next webpage:
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

Postgres Register Server 1

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.

Postgres Register Server 2

After doing this and clicking save we should see our database in our console under servers as you see here:

Postgres Register Server 3

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…

Creating Schema 1

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 Schema 2

Creating a table

Under Schemas, in our database, our Schema will be shown, now let’s right-click it and then Create -> Table

Creating Table 1

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 table 2

Creating the columns in our Table

Let’s go now to columns tab and click the plus sign to add our firsts columns

Creating columns 1

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.

Column id

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

ID as identity

Let’s click on the pencil again and click the plus sign to add a new column, let’s call it “firstname”

firstname column

Our column firstname will be a text, let’s do the same for “lastname”

id, firstname and lastname columns

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.

id, firstname, lastname and owed_money columns

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

Insert into table 1

You should now see something like this:

Insert into table 2

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:

Selecting table

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:

Summing array in PostgreSQL

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!

Leave a Reply