PGAdmin III's SQL Query Tool
PostgreSQL has really improved its support for all aspects of database administration - lead by the folks at Enterprise DB. This is covered in our overview of Postgres just posted. But nowhere is the improvement more obvious and welcome than in the changes and improvements made to PGAdmin III' SQL Query tool. The changes make a DBA's job a lot simpler.
Query Tool Features
Now PGAdmin III goes well beyond SQL Query implementation and allows DBA's to analyze and maintain their PostgreSQL databases with a variety of tools including backup, synchronization, server configuration among others. But so many of these administration tasks involve the ability to run various SQL commands quickly; hence the attraction of having a solid SQL Query tool. What makes this even better is that the tool is both easy to use and configurable so that its features can be extended to developers working on specific software projects with PostgreSQL.
The screenshot above shows the many ways to get the SQL Query tool started. The best choice, clicking on a table name, immediately creates a SELECT SQL statement with all the columns from that table as seen below:
Now it is simple matter of clicking the green GO button and the query is done for you - and very fast [see the lower Output pane in the screenshot]. Users thus can quickly see the data in their database; and if you know SQL it is simple to edit the SQL command in the SQL editor. Click on the ?-icon in the toolbar and the Query tool pops up a browser based Help file with a complete description of the SELECT command.
However, users new to SQL have another alternative [which I know quite a few developers use as well]. Instead of using the text based SQL Editor, clicking on the Graphical Query Builder tab [highlighted in yellow in the screenshot] and users are presented with a type of graphical query builder popularized in Borland's dBase and Paradox or Microsoft's Access.
One problem: users are not immediately presented with the last SQL query constructed but have to start from scratch - and know that they have to expand the database Tree, and then drag and drop or double click on the table they want to query as shown below:
Note in the screenshot that the lower Output pane has been changed to a SQL Command Setting pane. There are four tabs:
Columns - allows users to select the specific columns they want output
Criteria - allows users to filter the rows output by "where" clauses
Ordering - allows users to set the sorting order by specific columns
Joins - allows users to link together tables by primary keys in a multi-table query [see the previous screenshot of the products, stockup and vendor tables]. Again, press on the Green Go button in the toolbar. The query is done and shown in the Output pane:
And here you can switch to the SQL Editor tab to continue to edit the query directly [as shown in the screenshot above] or stay in the Graphical Query Builder - your choice. But note that the interaction is NOT 2-way. The Graphical Query Builder can feed the SQL Editor but not the reverse.
PGAdmin III can not only help create PostgreSQL queries but also is used to do many database administration tasks. Just right mouse click in the Object tree to see what operations are available:
In the screenshot above we show the many commands available when users right click on a PostgreSQL table. Many of these are tasks for database administration. Here is a sample list of functions that have been enabled in a specific database.
This type of list is important because PostgreSQL databases are so configurable as to datatypes and therefore functions, triggers, stored procedures and other rules available to developers. But this is only a few of the Database Administration commands on tap. So PostGreSQL users have a very versatile native SQL Query and DBA tool plus the choice of some excellent commercial tools as well.