Thread: DB creation script questions

Hey All,

I'm new to postgres and am wondering what the recommended way of
creating an automated db creation script is.  I've reading the
postgres 8.1 manual but haven't seen much on this and google doesn't
seem to be bringing up much either.

I need to create a script that will create a database if it currently
doesn't exist on the system.  It will then create tables if they don't
already exist and populate the tables with some data if this data
doesn't currently exist.  This script must be able to be run over an
over without causing harm to the db.

I have previously used MySQL and was able to accomplish this very
simply using IF NOT EXISTS for the table and database creation but
this doesn't seem to be available for postgres and i'm looking for a
more robust solution anyways.

Is there a way that from a sql script file/shell script you can find
out if certain postgres tables/databases exist?

If someone can point me in the right direction it would be greatly appreciated.

Thanks,

Jon.

On Wed, Aug 16, 2006 at 02:44:58PM -0400, Jon Horsman wrote:
> I have previously used MySQL and was able to accomplish this very
> simply using IF NOT EXISTS for the table and database creation but
> this doesn't seem to be available for postgres and i'm looking for a
> more robust solution anyways.

Well, you could query the SQL-standard Information Schema. 
Alternatively, you can query the system tables like pg_class.  But if
you want the more standard one, use the tables under schema
information_schema.  That's the reason there's a standard.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.     --Dennis Ritchie