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.
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
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.
I have never seen a use for "CREATE TABLE .. IF NOT EXISTS." that did not involve poor schema change management at minimum, allowing configuration problems to be made worse by leaving tables where they should not be, and often accompanied by giving too many privileges to the web application. I guess if we have to let MySQL users abandon their sloppy database, we have to let them keep some of their sloppy DBA practices.
I think that in PostgreSQL, a table's column order represents the order of the columns on disk. A while ago, it was discussed that PostgreSQL could add a specified order for table_name.* expansion, so that column order changes does not require changes to the disk layout.
All I can say is you have lived a very sheltered life and it would do you good to "eat more dirt" as my mother would say - dirt is good for children - builds your immune system :)
A classic example in PostGIS 2.0 we are still in development -- we want people ideally to have an easy upgrade regardless of when they built PostGIS 2.0 right because they ahve a lot of data they are playing with that they ideally don't want to reload. Our structures are in flux. We may add a table -- we want to create the table only if they don't have it.
Regarding replacing a view I completely agree with you!
Regarding the re-ording of the columns: I have never understood why people want to do that (and I never missed this in Oracle or Postgres). The desired column order can always be specified within the SELECT list
For those interested, I created a page on the postgres wiki (http://wiki.postgresql.org/wiki/Alter_column_position) which reviews the "alter column position" problem, including work arounds, and one possible implementation of the feature into postgres core. It's been 5 years since that plan was developed, so I wouldn't expect it to happen, but the idea is there. (Someone once showed up claiming to have implemented the pieces for physical re-ordering, but they never showed the code, so the claim is probably questionable).