Postgresql Array types [www.postgresql.org]

I have just been reading about the array types in Postgres - boy do I wish I was using Postgres right now.

Using the SQL99 standard syntax, they can only be single dimensional and you have to specify a fixed dimension. The utility of fixed arrays seems to me as mostly a nice to have - you could, of course - emulate the same behaviour with columns.

Using the Postgres custom syntax however, you can specify multiple dimensions and leave the dimension unspecified. This seems to be a really useful extension that would suit many commen needs. I'm first thinking of a flag or parameter type of column.

Lets say that you are writing a more advanced job scheduler (think cron) and you have a db table where you're storing commandline arguments for the timed program executions. You could store that as a plain string to be parsed by the executing shell, but then you're losing information (and have to be insanely careful with quoting) and don't make the char limit too short. You could store the individual arguments as comma separated values in a string or text/clob field (how many times have you seen that little doozy in legacy code). Or you could have a seperate table for command line arguments, link it to the command table with a shared id, plus you would need an ordering integer so as not to lose the order of the arguments.

Or ... using Postgres array syntax, you could define a single column arguments as a variable dimensioned array of strings:

create table commands (
    id integer primary key,
    command    varchar,
    arguments  varchar[]
)

You can then get all the command details with a simple select command, arguments (assuming appropriate array support from your database driver), you can find the number of arguments:

select array_dims(arguments)
from
commands where ...

You can find every command that is called with both the argumets --ignore-errors and --destructive:

select *
from commands
where '--ignore-errors' = any (arguments)
  and '--destructuve' = any (arguments)

Another neat thing is that you can cast a resultset into an array, so you could eg. very easily denormalize a complex one to many lookup relationship into an array column with a trigger.

This is an example of why I love the Postgres project so much - they are way ahead of the curve on innovation (being behind only Oracle - but even then only in some areas) and yet more standards compliant than any commercial or open source RDBMS I have come across. They understand that the power of of an SQL database is in the relational database concepts and pursue being a good RDBMS - rather than trying to be a database that "you won't get fired for using" a la MySQL or MSSQL.