We've started to play with PostgreSQL 9.1beta and the PgAdmin III 1.14.0 Beta 1. We'll briefly go over the cool gems found in PgAdmin III beta 1. Most of the new features are for navigating the upcoming PostgreSQL 9.1. Well first obstacle we ran into was we can't get our favorite extension, PostGIS, to compile against PostgreSQL 9.1beta though it did with the alphas, so you won't be seeing any windows experimental builds until we resolve this issue. Details of ticket here? PostGIS 2.0 won't compile for PostgreSQL 9.1 beta1
Despite that minor set back, we decided to push on and navigate the new features by using PgAdmin III 1.14.0 as our Tour Guide. Below is a list of new features you can experience via PgAdmin III 1.14.0 Beta 1. I'm sure there are more we missed, but these are the ones that were most flashing.
Extensions
In PostgreSQL 9.1 there is a new way to install contribs and other extensions. This makes it very easy to uninstall without knowing what you are doing. I was happy to see that PgAdmin III 1.14.0 has integrated this in the interface. Now hopefully when a newbie asks us How do I install hstore, tablefunc, ltree or whatever and how do I know what contribs I have installed? it will be as clear as water. Here is what it looks like:
- New colorful Extensions icon - shows what extensions you have. Right click to add a new extension
- Extensions dialog Right click to add a new extension
- Lets you choose which schema to install the extension
. For many of our smaller extensions, we usually create a
schema called contrib to install them in. What I really liked about this new feature is that if you accidentally installed the functions in the wrong schema -- say the default public, you
just have to right-click the extension, go to Definition tab and switch the schema. It automatically moves all the functions for you into the new schema. - Most Extensions have just one version, but if there are multiple, the Definition tab will allow you to choose which version you want.
- What I have always loved about PgAdmin is the SQL tab which is on most any activity tab, which shows you the SQL you would need to run to script what you just did. It makes for
a really easy to use sql script generator and intro to new DDL commands.
SQL/MED: Foreign Data: Accessing the option from PgAdmin
As many have already buzzed about SQL/MED (Management of External Data), you can now query more easily external data via the SQL-Standard SQL/MED protocol in PostgreSQL 9.1. The ability to do this with PgAdmin III is also present in 1.14, but not visible by default. In order to access this feature:
- Go to File -> Options -> Browser tab. Your screen should look something like:
- The key pieces of SQL/MED are Foreign Data Wrappers, Foreign Servers, User Mappings, and Foreign Tables, so you'll want to check all these
- Disconnect from server and reconnect. Your should see next to your extensions icon a Foreign Data Wrapper icon
.
In each schema -- you will also see a 
- There is a Foreign data handler (which you will need to define a wrap) already built-in for connecting to other PostgreSQL servers. In addition, for accessing flat files, there is another one you can install via the Extension icon called file_fdw. Add that one in to access flat files. This feature deserves its own article to demonstrate how to use it. You can expect to see that soon.
In an upcoming article. We;ll demonstrate how to connect to foreign data sources with the new SQL/MED feature.
New Table / View and column Usability features
The new table screen of PgAdmin has changed a bit to support the new features of PostgreSQL 9.1 as well as old features that you couldn't do without writing your own create table statement or changing the one generate. He re is a snapshot of definition tab.
Unlogged tables is a new feature in PostgreSQL 9.1 that allows you to create non-transactional tables. Think of these as PostgreSQL equivalent of a better MyISAM.
It's good for data you need to be able to load fast but don't mind loosing in the event of a crash. As you can see, there is a Unlogged checkbox to create such a table
- PostgreSQL 9.1 now supports column level collations. To support this PgAdmin has collation drop down in the New Column screen, as well as an option to define new Collations in the tree.
- For table constraints, though we could create Exclusion Constraints in PostgreSQL 9.0, there was no PgAdmin interface to define them in the table constraint dialog.
- View Triggers: Under views, we now have a trigger section to support the new feature in PostgreSQL 9.1 for using view triggers for updating.