> 1. Is it possible to use syntax like "if object
> exists then drop it" before running your ddl
> statements? Which system table do you hit?
If there is I don't know about it... but if you want to find the tables,
they're in pg_class. Along with a bunch of other stuff.
> 2. In the documentation it says that of the character
> data types text is best, over varchar(). Why is this?
Best, depending on the scenario. In cases where you are using a fixed number
of characters, there's no need for the overhead of a varchar. For instance
if you are storing state abbreviations, they will ALWAYS be 2 characters.
The database can look up those fixed fields faster. But if you are storing
full state names, it would be a waste to have all those passing spaces so
that you could fit Mississippi with Maine. All that being said, it's my
understanding that there will be no benefit to using the CHAR type over the
VARCHAR type in 7.1 due to architectural changes.
> What if any are the benefits of using an array data
> type over, say, a child table holding all the values
> of the array?
One, it reduces the complexity of the database. Two, you don't have to
perform a join or extra query to extract those child values. I'd personally
use them more if there were better ways to query array information. Such as
the length of the array, etc. Perhaps there is and I'm just ignorant...
> 3. OK the CLUSTER statement. Say you cluster on the
> last name of an employee table. When you then do an
> insert into this table for someone named Smith is the
> record logically written between the R's and the T's?
> Just need to verify this is right :)
Can't comment much here, but CLUSTERing seems to be somewhat broken
currently. I'm not planning on doing anything with it until I hear that
everything works dandy.
> 4. Lastly I wanted to confirm that the best way to
> regularly run a piece of sql is to schedule a OS
> script to handle it. Is this right?
There are a number of ways to handle it... I would handle it in whatever way
you feel most comfortable. If that's running psql from crontab, so be it.
Greg