Sunday, November 7, 2010

Interacting with PostgreSQL: pgAdmin

You have a PostgreSQL account but where do you start? There are several ways of interacting with PostgreSQL:
  • 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.
Let's start with pgAdmin. When first using PostgreSQL this is probably the easiest way to connect to a database and browse the database structure, tables, stored procedures etc. Later posts, however, will focus on the psql terminal.

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 (#)
where the hashes represent some numbers. At this stage we are only interested in the Databases section.

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.

15 comments:

  1. very nice explanation ,,,
    it really helped me ...
    thanks

    ReplyDelete
  2. Thank you so much for your help with this blog. Your explanations are excellent.

    ReplyDelete
  3. Nice!

    Clearest explanation I've found of pgadmin. :)

    ReplyDelete
  4. This was a nice little Intro to get things up and running. Appreciated!

    ReplyDelete
  5. Muchas gracias, alma caritativa.

    ReplyDelete
  6. Take a look on a free tool Valentina Studio, is the best way to transform your data into meaningful information; create, administer, query and explore Valentina DB, MySQL, Postgre and SQLite databases for free on 32/64 bit Windows, Linux and Mac OS X. http://www.valentina-db.com/en/valentina-studio-overview

    ReplyDelete
  7. Thanks a lot, bruh

    ReplyDelete
  8. Thanks a lot
    Exactly what I was looking for :)

    ReplyDelete
  9. Thanks - this was helpful.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Postgresql Admin
    , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on in Postgresql Admin We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us:
    Name : Arunkumar U
    Email : arun@maxmunus.com
    Skype id: training_maxmunus
    Contact No.-+91-9738507310
    Company Website –http://www.maxmunus.com


    ReplyDelete
  12. I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in TECHNOLOGY , kindly contact us http://www.maxmunus.com/contact
    MaxMunus Offer World Class Virtual Instructor led training on TECHNOLOGY. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
    For Demo Contact us.
    Sangita Mohanty
    MaxMunus
    E-mail: sangita@maxmunus.com
    Skype id: training_maxmunus
    Ph:(0) 9738075708 / 080 - 41103383
    http://www.maxmunus.com/

    ReplyDelete