Conditionally altering tables

From: Colin McGuigan <cmcguigan(at)earthcomber(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Conditionally altering tables
Date: 2005-01-10 23:07:23
Message-ID: 41E30AAB.7020406@earthcomber.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Coming from Microsoft SQL Server here...

Is there a way to write a script so that it will make required schema
changes, only if they haven't already been done?

Eg, I have a script that should add a NewField field to MyTable, as so:

ALTER TABLE MyTable ADD COLUMN NewField integer NULL

This works fine the first time it runs, but if I run this script against
the database again, I get a message saying that NewField already exists,
and the script errors out. In MSSQL, I could write something like:

IF NOT EXISTS (SELECT * FROM sysobjects so INNER JOIN syscolumns sc ON
so.id = sc.id WHERE so.Name = 'MyTable' and sc.Name = 'NewField) ALTER
TABLE MyTable ADD COLUMN NewField integer NULL

And it would be fine. Is there something similar I could do in Postgres
without having to write individual stored procedures for each one?

--Colin McGuigan

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Mike G. 2005-01-11 03:46:53 Re: Conditionally altering tables
Previous Message Michael Fuhr 2005-01-10 20:06:14 Re: index problem