I'm trying to load a database ported from Oracle. The commands I was given to
load data into the table are in alphabetical order; when I run them, I get
errors because I'm violating RI rules (eg. table "CUSTOMER" has RI to "SOURCE",
but CUSTOMER attempts to load before SOURCE).

Certainly, I could try to reorder the load statements, but that would be just a
huge PITA. Is there a way to temporarily suspend RI on a PostgreSQL
database/schema, and then "fix it later?" Either delete the rows that break
rules or add rows to fix the violations?

TIA

=================================================================
Jim Jarrett,Madison, WI 94 Passat GLX |
mailto:jarrett@rpa.net 81 Rabbit Convertible 16v |
Any problem can be solved with the proper application of |
Force, Heat, Chemicals, or Money. |
================================================================

Search Discussions

  • Jim Jarrett at Aug 9, 2005 at 1:14 pm
    I'm trying to port a database from Oracle to PostgreSQL. I used a perl script,
    ora2pg to extract the info from Oracle.

    The table data was extracted in alphabetical order. When I attempt to load it,
    I get referential integrity violations (eg. I attempt to load CUSTOMER, but
    CUSTOMOER depends on the SOURCE table, which hasn't been loaded yet).

    Is there a way to temporarily suspend RI checking so I can load the data and
    then fix it later?

    =================================================================
    Jim Jarrett,Madison, WI 94 Passat GLX |
    mailto:jarrett@rpa.net 81 Rabbit Convertible 16v |
    Any problem can be solved with the proper application of |
    Force, Heat, Chemicals, or Money. |
    ================================================================
  • Sean Davis at Aug 9, 2005 at 1:28 pm

    On 8/9/05 9:14 AM, "Jim Jarrett" wrote:

    I'm trying to port a database from Oracle to PostgreSQL. I used a perl
    script,
    ora2pg to extract the info from Oracle.

    The table data was extracted in alphabetical order. When I attempt to load
    it,
    I get referential integrity violations (eg. I attempt to load CUSTOMER, but
    CUSTOMOER depends on the SOURCE table, which hasn't been loaded yet).

    Is there a way to temporarily suspend RI checking so I can load the data and
    then fix it later?
    Not on a "whole database" level, as far as I know. You could:

    1) Make foreign keys deferrable and load related tables inside a
    transaction.

    2) Drop foreign key constraints until loading is complete and then
    reinstitute them (each one must be done individually).

    3) Make a separate "loader" script that does the loading into "loader"
    tables that have no foreign key constraints and then write a SQL script to
    build the database from the loaded tables.

    4) Reorder the oracle dump....

    I think all of these require a bit of work beyond something like "SET
    REFERENTIAL INTEGRITY OFF", but I would love to be corrected....

    Sean
  • Michael Fuhr at Aug 9, 2005 at 1:31 pm

    On Tue, Aug 09, 2005 at 09:14:50AM -0400, Jim Jarrett wrote:
    Is there a way to temporarily suspend RI checking so I can load the data and
    then fix it later?
    You could drop and then re-create the foreign key constraints with
    ALTER TABLE, or you could create the constraints as DEFERRABLE and
    defer them during the load with SET CONSTRAINTS (or make them
    INITIALLY DEFERRED).

    http://www.postgresql.org/docs/8.0/static/sql-altertable.html
    http://www.postgresql.org/docs/8.0/static/sql-createtable.html
    http://www.postgresql.org/docs/8.0/static/sql-set-constraints.html

    --
    Michael Fuhr
  • Bruno Wolff III at Aug 15, 2005 at 5:29 pm

    On Mon, Aug 08, 2005 at 09:49:38 -0400, Jim Jarrett wrote:
    I'm trying to load a database ported from Oracle. The commands I was given to
    load data into the table are in alphabetical order; when I run them, I get
    errors because I'm violating RI rules (eg. table "CUSTOMER" has RI to "SOURCE",
    but CUSTOMER attempts to load before SOURCE).

    Certainly, I could try to reorder the load statements, but that would be just a
    huge PITA. Is there a way to temporarily suspend RI on a PostgreSQL
    database/schema, and then "fix it later?" Either delete the rows that break
    rules or add rows to fix the violations?
    For an initial load the best thing is probably to not include the foreign key
    constraints in the initial table definitions and add them at the end of the
    load using alter table commands.
  • Sean Davis at Aug 15, 2005 at 5:38 pm

    On 8/15/05 1:31 PM, "Bruno Wolff III" wrote:

    On Mon, Aug 08, 2005 at 09:49:38 -0400,
    Jim Jarrett wrote:
    I'm trying to load a database ported from Oracle. The commands I was given
    to
    load data into the table are in alphabetical order; when I run them, I get
    errors because I'm violating RI rules (eg. table "CUSTOMER" has RI to
    "SOURCE",
    but CUSTOMER attempts to load before SOURCE).

    Certainly, I could try to reorder the load statements, but that would be just
    a
    huge PITA. Is there a way to temporarily suspend RI on a PostgreSQL
    database/schema, and then "fix it later?" Either delete the rows that break
    rules or add rows to fix the violations?
    For an initial load the best thing is probably to not include the foreign key
    constraints in the initial table definitions and add them at the end of the
    load using alter table commands.
    As an additional point, sometime I find it helps to do an initial load into
    a different schema (same database) and then use a sql script/function to do
    "cleanup" into the final schema with RI in place.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 8, '05 at 1:49p
activeAug 15, '05 at 5:38p
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase