[nycbug-talk] (OT) Postgres Migration

[Bob Ippolito]

On Mar 14, 2006, at 9:54 AM, Jonathan Vanasco wrote:

> I'm migrating from MySQL to Postgres, which I know a bunch of people
> here use
>
> My schema has 161 tables, and heavy use of foreign key constraints.
> the perl script migration utilities out there do an okay job, and I
> can get by with editing the schema dump from there...
>
> but i've run into this one issue - postgres doesn't like it when i
> add a table w/a constraint check that doesn't exist yet.
>
> right now, i'm recursing backwards, and entering the least dependant
> tables first.

CREATE all of the tables first then add the foreign key constraints  
with ALTER TABLE.

http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

> just wondering if anyone knows a way to defer constraint checks
> during a transaction - the only docs i could find let one do that on
> a per table basis in the definition - so it would seem that I'd
> create the table with a defferred check, then edit it to not have the
> constraint.  not fun.

You can only do it on a per constraint basis, and only for foreign  
keys.  Either the docs you found were wrong or you read them  
incorrectly.

You can specify whether they're deferred or not by default when you  
create them, but you can change that at runtime with SET CONSTRAINTS.

http://www.postgresql.org/docs/8.1/interactive/sql-set-constraints.html

-bob