Обсуждение: Conditionally altering tables
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
Yes, Query pg_attribute table / catalog to see if it exists. See http://www.postgresql.org/docs/7.4/interactive/catalog-pg-attribute.html HTH Mike On Mon, Jan 10, 2005 at 05:07:23PM -0600, Colin McGuigan wrote: > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Mike G. wrote: > Yes, > > Query pg_attribute table / catalog to see if it exists. > > See http://www.postgresql.org/docs/7.4/interactive/catalog-pg-attribute.html > > HTH I'm sorry; my question was more along the lines of, "How can I put this into a SQL script and run it repeatedly without errors?" AFAIK, Postgres doesn't support any sort of conditional outside of a procedure, so something along the lines of: IF NOT EXISTS (SELECT * FROM pg_class INNER JOIN pg_attribute ON pg_class.oid = pg_attribute.attrelid WHERE pg_class.relname = 'MyTable' AND pg_attribute.attname = 'NewField') ALTER TABLE MyTable ADD COLUMN NewField integer NULL Doesn't work -- syntax error near "IF". If I put it into a procedure and run that, it'll work fine, but I'd prefer to avoid having to have a different procedure for each schema change that may be made. --Colin McGuigan