Thread: Migration Wizard problem

Migration Wizard problem

From
"Mark Levene"
Date:
Hi

I have a problem with a database brought in from an MS Access database
through the "Database Migration Wizard".

I have a table ("tblPurchaseOrderDetails") which has a field
("PurchaseOrderDetailID"). In Access this is an "Autonumber" field.

The "default" value generated for "PurchaseOrderDetailID" by the
Migration Wizard is:

nextval('"tblPurchaseOrderDetails_PurchaseOrderDetailID_key"'::text)

However, the "Sequence Object" created by the Migration Wizard is:

"tblPurchaseOrderDetails_Purchas"

so when the "nextval" is looked up, it can't find the "Sequence".

Can the Migration Wizard be changed to "chop" the "Default" object for a
column to 31 characters to match the "Sequence"?

A work around is to pg_dump the database, then edit the file, then bring
it back in with psql (a lot of work)

I hope that this makes sense!

Herewith the dump from pg_dump
--------------------------------------------------
--
-- TOC Entry ID 54 (OID 34903)
--
-- Name: tblPurchaseOrderHeader_Purchase Type: SEQUENCE Owner: xxxx
--

CREATE SEQUENCE "tblPurchaseOrderHeader_Purchase" start 3 increment 1
maxvalue 2147483647 minvalue 1  cache 1 ;

--
-- TOC Entry ID 102 (OID 34922)
--
-- Name: tblPurchaseOrderHeader Type: TABLE Owner: xxxx
--

CREATE TABLE "tblPurchaseOrderHeader" (
    "PurchaseOrderID" bigint DEFAULT
nextval('"tblPurchaseOrderHeader_PurchaseOrderID_key"'::text) NOT NULL,
    "CompanyID" integer,
    "CountryID" integer,
    "SupplierID" integer,
    "EmployeeID" integer,
    "OrderDate" date,
    "PurchaseOrderNumber" character varying(30),
    "RequiredByDate" date,
    "PromisedByDate" date,
    "ShipName" character varying(50),
    "ShipAddress" character varying(255),
    "ShipCity" character varying(50),
    "ShipState" character varying(50),
    "ShipStateOrProvince" character varying(50),
    "ShipPostalCode" character varying(20),
    "ShipCountryID" integer,
    "ShipPhoneNumber" character varying(30),
    "ShipDate" date,
    "ShippingMethodID" integer,
    "FreightCharge" money,
    "SalesTaxRate" double precision,
    Constraint "tblPurchaseOrderHeader_pkey" Primary Key
("PurchaseOrderID")
);
--------------------------------------------------

Thanks,
Regards,
Mark ...


Re: Migration Wizard problem

From
"Dave Page"
Date:
Hi Mark,

The easiest fix is simply to edit the sequence name, or the column's
default value using pgAdmin. Both should take a few seconds. I will take
a look at the bug though - thanks.

Regards, Dave.

> -----Original Message-----
> From: Mark Levene [mailto:Mark@UnderGlass.co.nz]
> Sent: 28 March 2003 03:30
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Migration Wizard problem
>
>
> Hi
>
> I have a problem with a database brought in from an MS Access
> database through the "Database Migration Wizard".
>
> I have a table ("tblPurchaseOrderDetails") which has a field
> ("PurchaseOrderDetailID"). In Access this is an "Autonumber" field.
>
> The "default" value generated for "PurchaseOrderDetailID" by
> the Migration Wizard is:
>
> nextval('"tblPurchaseOrderDetails_PurchaseOrderDetailID_key"'::text)
>
> However, the "Sequence Object" created by the Migration Wizard is:
>
> "tblPurchaseOrderDetails_Purchas"
>
> so when the "nextval" is looked up, it can't find the "Sequence".
>
> Can the Migration Wizard be changed to "chop" the "Default"
> object for a column to 31 characters to match the "Sequence"?
>
> A work around is to pg_dump the database, then edit the file,
> then bring it back in with psql (a lot of work)
>
> I hope that this makes sense!
>
> Herewith the dump from pg_dump
> --------------------------------------------------
> --
> -- TOC Entry ID 54 (OID 34903)
> --
> -- Name: tblPurchaseOrderHeader_Purchase Type: SEQUENCE Owner: xxxx
> --
>
> CREATE SEQUENCE "tblPurchaseOrderHeader_Purchase" start 3
> increment 1 maxvalue 2147483647 minvalue 1  cache 1 ;
>
> --
> -- TOC Entry ID 102 (OID 34922)
> --
> -- Name: tblPurchaseOrderHeader Type: TABLE Owner: xxxx
> --
>
> CREATE TABLE "tblPurchaseOrderHeader" (
>     "PurchaseOrderID" bigint DEFAULT
> nextval('"tblPurchaseOrderHeader_PurchaseOrderID_key"'::text)
> NOT NULL,
>     "CompanyID" integer,
>     "CountryID" integer,
>     "SupplierID" integer,
>     "EmployeeID" integer,
>     "OrderDate" date,
>     "PurchaseOrderNumber" character varying(30),
>     "RequiredByDate" date,
>     "PromisedByDate" date,
>     "ShipName" character varying(50),
>     "ShipAddress" character varying(255),
>     "ShipCity" character varying(50),
>     "ShipState" character varying(50),
>     "ShipStateOrProvince" character varying(50),
>     "ShipPostalCode" character varying(20),
>     "ShipCountryID" integer,
>     "ShipPhoneNumber" character varying(30),
>     "ShipDate" date,
>     "ShippingMethodID" integer,
>     "FreightCharge" money,
>     "SalesTaxRate" double precision,
>     Constraint "tblPurchaseOrderHeader_pkey" Primary Key
> ("PurchaseOrderID")
> );
> --------------------------------------------------
>
> Thanks,
> Regards,
> Mark ...
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>