Sweat the small stuff, it really matters

In most release notices, it's the big shiny sexy features that get all the glamor, but in reality on day to day use it's the small usability enhancements that make the most difference. I'm reminded about this now that I'm working on upgrade scripts and extensions for PostGIS. There are a couple of new features that make application upgrades easier that I regret not having in older versions of PostgreSQL we support and additional ones I had in other databases that I find lacking in PostgreSQL. PostgreSQL 8.2 for example brought us DROP IF EXISTS ... and all I can say is thank goodness we dropped support of prior versions of PostgreSQL in PostGIS 1.4 otherwise developing upgrade scripts would have been more of a nightmare. PostgreSQL 8.4 introduced the ability to add additional columns to a view using CREATE OR REPLACE VIEW as long as those columns were at the end of the view which Gabrielle Roth demonstrates an example of in This week’s find: CREATE OR REPLACE VIEW If you were a MySQL user or application developer not having such features would be one reason to frown on PostgreSQL and MySQL users and other database converts still have reasons to frown for lack of usability features they had in their other database that they feel naked without in PostgreSQL.

In 9.1 we got two new DDL commands not much talked about that I am very excited about.

• CREATE TABLE .. IF NOT EXISTS. I can't tell you how many times I've heard MySQL users whine about the lack of this in PostgreSQL and I felt their pain. It would be really nice to have this feature for other things such as TYPES or even possibly a CREATE OR REPLACE TYPE which would allow some alteration of types like adding attributes at the end.
• ALTER TYPE ..[ADD ATTRIBUTE] [RENAME ATTRIBUTE] [ADD VALUE]. The [ADD VALUE] is a clause specific to ENUM types which allows you to add new enum values before or after an existing. The lack of that feature in prior versions was the major reason I stayed away from enums.
• And of cause my favorite CREATE EXTENSTION ALTER EXTENSION family which admittedly do get talked about a lot more often and which I'll discuss more in a later article.

.

I know it sounds like I'm complaining. That's because I am. Honestly though, I think the first step to caring about something is really taking notice of its flaws and wanting to change them. The strength of an open source project is the ease with which it allows its developers and users to have a great impact on its direction. This is something I do think PostgreSQL excels much much better than most open source projects. I find a ton of flaws in PostGIS I'd like to change and have and I am greatful that PostGIS, like PostgreSQL is not resistant to change if the community wants it. If you are going to take notice of flaws in other products without admitting to your own or admitting that some things are easier in other products and learning from them, then you are a hypocrite or living in a closet. Now getting back to my complaining. Things I miss in PostgreSQL that I had in others which I'm sure I'm not alone.

• Being able to change a table column type of a table column that is used in a VIEW and have PostgreSQL just correct the type in the view or allow me the option to change it later. This is something we had in SQL Server which Leo whines about often. Actually Leo's whining is more annoying than the actual problem itself. The notice is at least very descriptive which is more than I can say for other databases.
• Being able to reorder columns in a table. Again something fairly trivial to do in SQL Server and MySQL but not possible in PostgreSQL.