- GUIs: you can use a GUI-based tool, such as pgAdmin, to connect to the database, browse the tables and run queries.
- command line: PostgreSQL comes with a very powerful command line utility or interactive terminal called psql. This will be covered in a later post.
- programmatically: one can use a database driver through code such as the Java JDBC driver. This is a more advanced topic that will not be covered.
If you haven't done so already install pgAdmin on your local machine. The install is very quick and easy. Done? OK, let's connect.
When your account was set up hopefully you were told its location. This will be the name of a server or a complete URL and they probably told you the name of the port to connect to. Typically this is 5432 for PostgreSQL.
Fire up pgAdmin and click on the connection button on the top left. It looks like a 2-pronged plug with a short section of cable and if you mouseover the button it says "add a connection to the server". This brings up a form. Fill in the details. The name is whatever you want to call this connection. The host is the name of the server. The port is going to be 5432 unless you were told otherwise. The maintenance DB is usually postgres; we will explain this later. Fill in your username and password. If this is your computer and you are not sharing this with anyone else then you probably want to check the "store password" checkbox. This means that you won't need to enter all this information next time. If this is a shared computer it is probably best not to check this.
Once everything is entered, click OK. If everything went well you will be connected and you will see not only the name of your server in a list on the left but you can click on the little triangle next to it to expand the view and you will see
- Databases (#)
- Tablespaces (#)
- Group Roles (#)
- Login Roles (#)
Expand this view. There will be at least one database. The reason is that there is a special database typically called "postgres". This is the maintenance DB. Something needs to store information about the other databases. Why not use a database to do that.
If you can, create a new database called "testdb". Right click on "Databases (#)" and click on "New Database..." Type "testdb" in the name field and click OK. Being a novice there is a chance that you might not have permission to create one. Perhaps you can ask the administrator to create a testdb for you to play around with.
Assuming that worked, expand the "schemas (#)" section. There will be a schema called "public". This is the default schema. We will cover schema in a later post. For now, just think of this as where the tables are listed. You are going to see a lot of confusing stuff: FTS configurations, FTS parsers and so on. The only thing we need now is the tables section.
Let's create a table. Right click on "tables (#)", click on "New Table...", input the name "mytable" for the table in the name field. Now click on the "columns" tab. Let's create a table with 2 columns. For each column click add, input a name, select a data type and click OK. Create a table with columns "name" of type "character varying" and "value" of type integer. Finish off by adding a primary key. Click the constraints tab. Select primary key, click "add", click the columns tab, select the name column and click OK twice. You now have a table with two columns and a primary key. When we use the psql command line this will actually be easier and more explicit as you do this in a single SQL query but you now know how to do it in the GUI.
You can view the data in a table by clicking on a table name and clicking on the icon that looks like a spreadsheet. Make sure that it is the basic one that says "view the data in the selected object" on mouseover and not the one with the purple funnel that says "apply a filter and view the data in the selected object.
The table is empty now so let's insert some. Click on that icon. You should now be able to click on a cell and enter data. Clicking enter on your keyboard will create a new row.
Let's finish by running a SQL query to select data from a table. Click the SQL icon which looks like a magnifying glass. In the large box in the top left input "select * from mytable". Click the green triangle (says "Execute query" on mouseover"). You should now see the data from the table.
OK, you have now explored a GUI that connected to PostgreSQL, you created a database and a table, inserted some data and ran a query. Good job! In subsequent posts we are going to use the psql command line instead allowing us to focus on the SQL commands to do what we need.