Thread

Re: Problem with COPY in 8.0.3

Oliver Elphick <olly@lfix.co.uk> — 2005-10-12T19:17:23Z

On Wed, 2005-10-12 at 12:37 -0600, Michael Fuhr wrote:
> On Wed, Oct 12, 2005 at 12:19:41PM -0600, Michael Fuhr wrote:
> > On Wed, Oct 12, 2005 at 07:08:20PM +0100, Oliver Elphick wrote:
> > > I should add that the table inherits from another one, but the
> > > swapped columns are a long way into the extra columns specific to
> > > this table.
> > 
> > Could you post the table definitions?
> 
> BTW, I meant the CREATE TABLE statements, not the \d output.
> 
Here it is: 

CREATE TABLE invoice
(
   invno        INTEGER        ,
   customer     VARCHAR(10)     NOT NULL,
   account      VARCHAR(8)      NOT NULL,
   invdate      DATE            NOT NULL
                                DEFAULT CURRENT_DATE,
   taxpoint     DATE            NOT NULL
                                DEFAULT CURRENT_DATE,
   discount     DECIMAL(5,3)    NOT NULL DEFAULT 0.0
                                CHECK (discount >= -50.0::DECIMAL(5,3) AND
                                       discount <= 50.0::DECIMAL(5,3)),
   ordno        INTEGER,
   custref      TEXT            NOT NULL DEFAULT 'NONE',
   currency     CHAR(3)         NOT NULL,
   carriage     DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
   printed      BOOLEAN         NOT NULL DEFAULT 'f',
   assigned     BOOLEAN         NOT NULL DEFAULT 'f',
   customer_ean VARCHAR(13),
   location_ean VARCHAR(13),
   exchange_rate DECIMAL(10,4),
   grpid        VARCHAR(2),
   postcode     VARCHAR(10),
   grn          VARCHAR(20),
   orderdate    DATE            CONSTRAINT "sane order date"
                                  CHECK(orderdate <= invdate),
   CONSTRAINT "EDI fields" CHECK ((customer_ean IS NULL AND location_ean IS NULL) OR (customer_ean IS
NOT NULL AND customer_ean != '' AND location_ean IS NOT NULL AND location_ean != '')),
CONSTRAINT "foreign exchange" CHECK ((currency = 'GBP' AND exchange_rate is NULL) OR (currency != 'GBP' AND exchange_rate IS NOT NULL AND exchange_rate > 0.))
,
PRIMARY KEY (invno),
CONSTRAINT invoice_list_fkey
   FOREIGN KEY (invno) REFERENCES invoice_list (invno)
                                  ON UPDATE CASCADE
                                  ON DELETE RESTRICT
                                  INITIALLY DEFERRED,
CONSTRAINT customer_accounts_fkey
   FOREIGN KEY (customer, account)
                    REFERENCES customer_accounts (customer, account)
                                  ON UPDATE CASCADE
                                  ON DELETE RESTRICT
                                  INITIALLY DEFERRED,
CONSTRAINT customer_fkey
   FOREIGN KEY (customer)
           REFERENCES customer (id)
           ON UPDATE CASCADE
           ON DELETE RESTRICT
           DEFERRABLE,
CONSTRAINT authorised_currency_fkey
   FOREIGN KEY (currency)
           REFERENCES authorised_currency (currency)
           ON UPDATE CASCADE
           ON DELETE RESTRICT
           DEFERRABLE,
CONSTRAINT customer_group_fkey
   FOREIGN KEY (grpid)
           REFERENCES customer_group (id)
           ON UPDATE CASCADE
           ON DELETE RESTRICT
           DEFERRABLE

);

CREATE TABLE export_invoice
(
   packages     INTEGER         NOT NULL
                                CHECK (packages > 0)
                                DEFAULT 1,
   packing      NUMERIC(12,2),
   other        NUMERIC(12,2),
   other_desc   TEXT,
   insurance    NUMERIC(12,2),
   pack_desc    TEXT,
   kilos        NUMERIC(12,3),
   nett         NUMERIC(12,3),
   dimensions   TEXT,
   terms        TEXT,
   bank         TEXT,
   goods        TEXT,
   marks        TEXT,
   port         TEXT,
   transport    TEXT,
   destination  CHAR(2)  CONSTRAINT destination
                              REFERENCES country (id)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION,
   origin       CHAR(2)  NOT NULL DEFAULT 'GB'
                         CONSTRAINT origin
                              REFERENCES country (id)
                                        ON UPDATE CASCADE
                                        ON DELETE NO ACTION,
   auth         TEXT,
   copies       INTEGER  NOT NULL DEFAULT 1
,
PRIMARY KEY (invno),
CONSTRAINT invoice_list_fkey
   FOREIGN KEY (invno) REFERENCES invoice_list (invno)
                                  ON UPDATE CASCADE
                                  ON DELETE RESTRICT
                                  INITIALLY DEFERRED,
CONSTRAINT customer_accounts_fkey
   FOREIGN KEY (customer, account)
                    REFERENCES customer_accounts (customer, account)
                                  ON UPDATE CASCADE
                                  ON DELETE RESTRICT
                                  INITIALLY DEFERRED,
CONSTRAINT customer_fkey
   FOREIGN KEY (customer)
           REFERENCES customer (id)
           ON UPDATE CASCADE
           ON DELETE RESTRICT
           DEFERRABLE,
CONSTRAINT authorised_currency_fkey
   FOREIGN KEY (currency)
           REFERENCES authorised_currency (currency)
           ON UPDATE CASCADE
           ON DELETE RESTRICT
           DEFERRABLE,
CONSTRAINT customer_group_fkey
   FOREIGN KEY (grpid)
           REFERENCES customer_group (id)
           ON UPDATE CASCADE
           ON DELETE RESTRICT
           DEFERRABLE

)
   INHERITS (invoice)
;

ALTER TABLE export_invoice ALTER COLUMN ordno SET NOT NULL;
CREATE INDEX export_inv_customer_ix ON export_invoice(customer);
CREATE INDEX export_inv_account_ix ON export_invoice(account);
CREATE INDEX export_inv_date_ix ON export_invoice(invdate);




-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html