If you want to learn how to use pgAdmin or get better at using it, this is the video for you.
Here, you’ll learn how to download pgAdmin, connect to a Postgres database, learn what’s in
the user interface, run some SQL statements, save files, browse objects, and much more.
If you want to follow along with this video, you’ll need to be able to access
a Postgres database. This could be on your computer, in the cloud, or a server at work.
I’ll have separate videos for setting this up on my channel if you want to know more.
Let’s get started with pgAdmin.
We’ll start with downloading pgAdmin. Open your browser and navigate to pgadmin.org.
Click on the Download button on the left of the screen.
pgAdmin is a free IDE and is available on many operating systems.
Click on the button for your operating system on this page. I’m on a Mac, so I’ll click macOS.
The next page shows a list of recent versions.
Click on the top entry here, which at the time of recording is v6.14.
You’ll then be taken to a File Browser page, like this. Click on the DMG file
here if you’re on a mac. If you’ve selected Windows, you’ll see an EXE file instead.
This file is the installation file for pgAdmin.
Save the file to your computer. It’s a couple of hundred megabytes
so should take a minute or so to download depending on your speed.
Once the download is complete, open the file. The steps will be different on Windows and Mac, and
I’ll show you the Mac install, but with Windows, you can simply follow the installation wizard.
On the first screen, click Agree. The package will then be verified which can take a minute or so.
This pgAdmin screen will then appear. Drag the pgAdmin 4 logo from the left
into the Applications folder on the right. The pgAdmin application will then be installed.
Once it is completed, you can close this pgAdmin window.
To open pgAdmin, you can open it using the Spotlight search, or from the Applications folder.
If you’re on Windows, you can open it from either
the Start menu or a shortcut on the desktop, if one was created.
Once it opens, you’ll be asked to set a “master password”. This is a password that lets you access
pgAdmin and the database connections that you’ll set up. A master password is highly
recommended by the pgAdmin team because of the way the software is designed and to increase security.
Enter a password here that you want to use, and click OK.
You’ll then see the main pgAdmin window.
This is what pgAdmin looks like when you first open it.
There are a few menus at the top, for File, Object, Tools, and Help.
On the main toolbar there are a range of icons, some of which we will cover in this video.
On the left panel is an entry called Servers.
This is where you can browse your connections and database objects, which we’ll see more of shortly.
The main panel on the screen is where you can write and run queries,
once we have connected to a database.
Let’s do that now.
To see all of your database connections,
you can click on the arrow next to the Servers entry on the panel on the left.
If you’ve just set up Postgres and pgAdmin, you probably won’t see anything here. I’ve got a
couple of connections already set up, one called Postgres 13 and another called Postgres Docker.
We’ll create a new connection, which is called a server in pgAdmin.
To do that, right-click on Servers and select Register, then Server.
You’ll see the New Server window appear. Enter a name for the server. I’ll enter Postgres Demo.
You can select the server group here, which is a way of grouping servers or connections
inside pgAdmin, and is helpful if you have a lot. We’ll leave this as the default.
You can also change the background and foreground colours if you like.
Click on the Connection tab.
In the Host name/address field, enter the address of your postgres database.
If you have postgres running on your computer, this would be localhost.
Otherwise, enter another hostname where you can access postgres.
Enter the password for your database.
Toggle the Save Password if you want to save the password and not have to enter it each time.
Now, click the Save button on the bottom right.
The server will be saved and pgAdmin will attempt to connect. If you get an error here,
check that the details you have entered are correct and if the database is running.
If you’re still not sure, I would suggest searching on Google for
the error message as others have likely got the same error and have resolved it.
Once your connection is successful, you’ll see the new server on the left
with a small postgres elephant icon. You’re now connected to the database!
There is a range of toolbar buttons at the top that let you get started with SQL,
but they are all disabled. This is because we need to select a database first.
To do this, expand the new server you created on the left panel.
Expand Databases, and click on a database that exists here. My server has several databases,
but yours may only have the postgres database which is the default.
Click on the postgres database. If you don’t have one at all, you can right click
on the Databases entry, select Create, then Database, and fill out the form to create one.
When you select a database, all of the buttons at the top will be enabled.
The first button on the toolbar is a cylinder with a triangle on it,
and is called Query Tool. Click on this button.
You’ll see a new tab open within pgAdmin.
The tab is underlined here, and has a title that includes the username of postgres,
the database of postgres, and the server name of Postgres Demo.
Within this tab there is a range of buttons and some panels.
At the top, there’s a dropdown that lets you select other connections or servers, if you want
to change the server you’re going to run these queries on. We can leave it as it is for now.
The next set of buttons will let you work with SQL files and run queries, which we will see shortly.
The main part of the window here is where you write and run your SQL queries.
On the right is a panel called Scratch Pad. This is helpful for storing pieces of text
as you work with queries, instead of having to add them to a query or another external editor.
You can close this if you like,
and can show it again by clicking on the Reset Layout button here on the top right.
Let’s write our first query.
We’ll create a new table, then we will insert data into it and select from it.
We’ll create a table called pgtest, with just one column.
Once you’ve written the Create Table statement, you can run it by pressing
F5 or clicking on the triangle button here which will execute the query.
The statement is run and is hopefully successful.
You’ll see a success message in the Message panel at the bottom of the window.
Now we have a table, let’s insert some data into it.
Remove the Create Table statement and write an Insert statement into our new pgtest table.
Run this statement by pressing F5 or clicking the Execute button again.
You’ll see in the Messages tab that the statement was successful.
Let’s write a Select statement to see the data in the table.
However, this time we’ll leave the Insert statement there.
Enter the Select statement after the Insert statement, like this.
We’ve got two statements here, but we only want to run the Select statement and not
the Insert statement. To do this, you can highlight the statement you want to run,
and click the Execute button. This will ensure only this statement is run.
We can see the output at the bottom of the screen. The Data Output tab is shown,
and it includes the column header, and a single row that we inserted with the value of Hello.
What’s also helpful is that it shows the data type of the column. In this example, it shows
“character varying 10”, which is the same as VARCHAR, which is what we used when we created the
table. This is a handy feature if you’re working with results and want to know what the format is.
If you don’t highlight any of the queries and just click Execute, then all queries will be run.
We can see here that the Data Output shows two rows,
because the Insert statement was run again, and then the Select statement.
If you run a Select statement and want to save the results to a file, such as a CSV file,
you can do that easily in pgAdmin. Click on this button here, called “Save results to file”.
You’ll then be prompted to enter a filename, or use the one that is generated.
You can also select a location. Click Save, and the results are saved to a CSV file.
You can save the SQL queries you have written to a file on your computer.
To do this, click on the Save File button on the toolbar.
You’ll be asked to enter a filename and select a location.
Once you do that and click Save, the file is saved. The title of the tab also changes to
match your filename, so you can identify it easily if you have more than one open.
If you want to open a file you have saved before,
you can click on the Open button here, browse to the file, and then open it.
Next to the Save button is an Edit button. Click on this and you get a range of options.
You can Find a value in your script,
which is helpful for long scripts and for finding a specific number or word.
You can Replace, which can replace all instances of one value with another.
The Jump option will let you easily navigate to a
specific line or a scroll percentage of the window.
Indent Selection will indent the selected lines, or the current line, in your code.
Unindent Selection will remove the indent from the line of code.
Toggle Comment will convert the line to a comment by adding two dashes in front of it,
which also changes the colour to orange. Select Toggle Comment again by removing the comment.
If you select some text, and click Toggle Case of Selected Text, you can change the
case between uppercase and lowercase. This is an easy way to convert between cases.
If you click Clear Query,
all of your queries will be removed and you’ll be left with a blank query editor.
Finally, the Format SQL option will format your queries based on some rules in pgAdmin,
to make your query easier to read.
Some of the things it does are separate clauses onto new lines and convert keywords to uppercase.
If you want to get the most out of pgAdmin,
I’ve created a bonus guide for you with some of the main features and common keyboard shortcuts.
Check out the link in the description below to get access to it.
Another feature of pgAdmin is seeing an explain plan.
This allows you to see how the database will run your query.
To see this, select your query and click the Explain button.
This will estimate the time it will take to run the query and show how it may be run.
Click on the Explain tab in the output if it’s not shown already.
This will show a graphical view of the query plan. It’s only a single entry called “pgtest”
because it’s a simple query, but more complicated queries will show more here.
You can click on Analysis to see more details of the plan,
and finally click on Statistics to see more information.
The next button on the toolbar is called Explain Analyse. This is similar to Explain,
except that it actually runs the query and shows you what was actually done. Both the Explain and
Explain Analyse can be helpful when trying to understand a slow query and how to improve it.
pgAdmin supports having multiple tabs or editors.
This allows you to work on more than one query at a time, or more than one connection.
To do this, you can click on the Query Tool button like you did before,
on the left of the main toolbar.
Another way to do it is to click on this button next to the connection dropdown.
It’s called “New query tool for the current connection”, and is an easy way to create a
new query window without having to find the connection on the left panel again.
When you click this button, a new tab is opened, with a new empty query editor.
You can perform all of the same things that we saw
in this editor without impacting your queries in the other editor.
If you want to close this editor tab, you can click on the X on the top right
of the window here. This tab is closed and you’ll return to the previous one.
A handy feature in pgAdmin is the ability to find a database object. If you know the name of
something and want to access it easily, or know part of the name, you can find it in pgAdmin.
Click on the magnifying glass icon here on the left panel,
and the Search Objects window will appear.
Enter the whole or part of an object name in this text box, and click Search.
You’ll see the results of the search appear, including the name of the object and its type.
If you double-click on it, the object will be selected in the left panel,
where you can perform additional tasks on it by right-clicking on it.
There are many other features in pgAdmin that you might find useful.
I’ll briefly cover them here as they probably deserve a video on their own.
You can generate an ERD or Entity Relationship Diagram in pgAdmin.
To do this, go to Tools, then ERD Tool. A new tab is opened that allows you to create an ERD.
I’ll have a separate video on how to use this. Click the X if you want to close this tab.
In the Tools menu, there is also a Schema Diff feature,
which is helpful for finding the differences between objects in different schemas.
If you click on this option, a new tab is opened that allows you to select the source
and target servers. It will then let you compare two objects and see the differences between them.
There is a range of other features in the Tools menu,
including database backup, import and export data, and accessing PSQL.
I’ll create videos on these features, but until then, feel free to play around with them.
pgAdmin has a range of preferences that you can change. To see them, go to File, then Preferences.
A new window is displayed. On the left panel is a list of categories
of preferences, and on the right is the preferences for the selected category.
Take a look through this list and see if there are any preferences you want to change.
Earlier, when we formatted our SQL, it used the settings that are available
in the Query Tool then SQL Formatting section. You can select the SQL Formatting entry to see
and change any of these settings if you want the formatter to work differently.
Once you’re done, click Cancel to exit or Save to save changes and exit.
So that’s a brief overview of the pgAdmin tool. We focused on the basics,
but as you can see there is a lot more that it can do.
It’s a big improvement over pgAdmin 3 and it’s still a pretty popular IDE for Postgres.
If you want to learn more about database design and SQL, visit my website at databasestar.com.
If you liked this video, consider subscribing to my channel.
Thanks for watching.