Web pgadmin.org

pgAdmin 1.10 Visual Tour

The long awaited 1.10 version of pgAdmin is finally available. We will highlight some of the new features which have been added since the last stable release on this page.

Major new features

There are two major new features in the Query Tool. They both came out of GSoC projects.

First, it is now possible to build a query graphically.

The graphical query builder (aka GQB) allows one to build a query mainly with the mouse: dragging tables and views from the navigator to the graphical pane, adding links between them, clicking on checkboxes to add columns, adding criteria and setting the order of the results.

The other new feature is the pgScript scripting framework. This allows one to write scripts to execute queries. Here is an example:

In this example, the user will insert random values into some tables. To execute this script, you need to click on the PGS button. The complete reference to pgscript is available here.

Derivative database support

There is enhanced support for Greenplum database: resource queues, external tables and partitioned tables are supported and the graphical explain tool knows much more about Greenplum query node types. There are also links to Greenplum help and Greenplum utility support.

Object browser

Unfortunately, pgAdmin 1.8 doesn't have support for FTS Objects. One of the things we had to do for 1.10 is to support these.

Each FTS object is handled by its own properties dialog.

The table contextual menu has a few more items that allow you to enable/disable rules on the selected table or to truncate (cascade if needed).

There's also a new DELETE script item:

And last, the role contextual menu has one more item to add Drop/Reassign Owned support:

Object properties

8.2 and 8.3 stats columns have been added to the tables list.

Each objects' list now show the owner of the object:

Only the function dialog was resizable. With 1.10, each property dialog is resizable:

We also have allowed the SQL tab to be edited. For example, when adding a new column with a default value and a NOT NULL constraint, you couldn't do it with pgAdmin in one action because one needs to first add the column, change the value for each existing row and then add the NOT NULL constraint. Now, you can use the UI to do this. You'll get this query:

Then, click on the "Read-only" checkbox to allow editing mode and update the query to execute.

Server properties

We no longer display the SQL tab for the Server properties dialog. It had no real meaning on this dialog. But we add a few things, like the color selection:

This color is the background color for each server:

Database properties

New features have been added to this dialog for 8.4: support for changing the tablespace of an existing database, support for renaming an opened database, support for per-database collation, character type and connection limits.

Table properties

Adding/removing inherited tables with 8.2+ servers has been added. The list control and the two buttons have moved to their own tab:

The new way to handle autovacuum settings in 8.4 is also supported. Toast tables are handled too.

Column properties

Column level priviledges are handled for 8.4 servers:

Index properties

pgAdmin 1.10 adds support for DESC and NULLs FIRST/LAST options for index columns.

Function properties

Window functions are handled for PostgreSQL 8.4+ servers.

pgAdmin 1.10 supports default values for function parameters in PostgreSQL 8.4+ and on EnterpriseDB, and variadic functions.

Type properties

Support for typmod in/out functions on custom types is finally available.

Language properties

One can now change a language's owner.

Role properties

Connection limit for roles was unavailable in previous release despite PostgreSQL handles this since 8.1.

Dump/restore dialog

There's now an option to ignore server version mismatches with pg_dump/pg_dumpall/pg_restore.

The --clean option is now supported.

Plugins menu

pgAdmin 1.10 adds the ability to register external applications on a plugins menu. Menu options can be activated based on the current object selection and a variety of variables - including everything needed to open a database connection - can be passed to the external utility.

The only plugin available by default is a psql console, though any application could conceivably be added by the user:

When clicked, the psql console is launched and connects to the database selected in pgAdmin.

Query Tool

Other than the Graphical Query Builder and the pgScript scripting framework, the Query Tool adds options to shift selected text to upper or lower case in the SQL editor, adds block indenting and outdenting, prevents the auto complete being used while a query is running and adds block commenting and uncommenting to the SQL editor.

Edit grid

The Edit grid can now work with the type bit(x).

It was already possible to sort and filter the grid but one has to go another dialog and choose the columns to sort by and write the filter as in a WHERE clause. A new feature allows a user to right click on a value he wants to filter (include or exclude). He can also right click on a column to sort by this column.

Server Status

There was a complete UI rework of the "Server Status" window.

It allows one to view all reports at the same time, to close some, to resize some, etc. The complete window layout is preserved between each run. Also, one can copy the contents of each grid. It also features a new toolbar to launch each action.

Other work happened on this window. A 'Blocked By' column appears to show if a transaction is blocked by another process. One can launch pgAdmin to only show the Server Status window, with the -S and -Sc command line options.


Other more minor changes in v1.10 include:

  • Remove an obsolete FORCE option on the Maintenance dialogue
  • Fix the index UI to handle columns with commas in the name
  • Add more index stats
  • Ensure a newline is included between notices in the query tool
  • Save and restore the user-selected line ending type in styled text controls
  • Avoid running excess queries on pg_shdescription when connecting to a server, fixing performance regression with large number of databases
  • Ensure that line endings stay consistent when editing in styled text controls
  • Fix listing of group roles in the combo box on privilege panels
  • Reverse engineer multi-word type names correctly when quoting
  • Allow external utilities such as pg_dump & pg_restore to work with SSL connections
  • Allow default settings to be set via a global config file. This allows redistributors and sysadmins to pre-configure an installation as required
  • Add support for TRUNCATE triggers on PostgreSQL 8.4+
  • Auto-discover Postgres Plus servers on Mac and Unix
  • Add the keyword 'WHILE' for the debugger
  • Allow the user a chance to abort if there is an error when deleting rows in the edit grid
  • Warn the user if connecting to a newer version of the server than is supported
  • Remove the un-maintained Tip of the Day feature
  • Fix handling of aggregate initial conditions to allow NULL or empty strings to be specified
  • Fix support for 'bit varying' columns
  • Restore old behaviour of the Edit Grid where <return> moves to the next column, but <enter> moves to the next row rather than both changing column
  • Add support for direct debugging of functions with variadic parameters
  • Add support for the many new node types in Postgres 8.4
  • Allow pgAgent to connect to databases on remote servers, such as hot standby machines
  • Ensure we never try to refresh the Servers node which would likely crash