Обсуждение: Problem with COPY in 8.0.3
Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12 I am importing a table using COPY. The data is tab-delimited. COPY seems to be putting the data for one field into the preceding field, which should contain the empty string. Here is the data, with tabs indicated by ^I and end of line by $: 67829^I22058^IX378^I10 FEB 2004^I10 FEB 2004^I0.000^I5411^I15498-00^IGBP^I0.00^It^It^I\N^I\N^I\N^I\N^I\N^I\N^I10 FEB 2004^I60^I0.00^I0.00^I0^I0.00^ICarton^I570.000^I0.000^I^ICIF-MONTREAL/ACT30-DAYS^I^IChemist\'s sundries^IAMG MEDICAL;5411;MONTREAL;1-60;^ITHAMESPORT^ISEA/ALLIANCE^ICA^IGB^IMAIREADBOYCE, DIRECTOR^I1$ the command used to fill the table is: COPY export_invoice FROM '/usr1/avdumps/dbdump.exportinvoice'; and here is how COPY has rendered it: bray=# select * from export_invoice where invno = 67829; invno | customer | account | invdate | taxpoint | discount | ordno | custref | currency | carriage | printed | assigned| customer_ean | location_ean | exchange_rate | grpid | postcode | grn | orderdate | packages | packing | other| other_desc | insurance | pack_desc | kilos | nett | dimensions | terms | bank | goods | marks | port | transport | destination | origin | auth | copies -------+----------+---------+------------+------------+----------+-------+----------+----------+----------+---------+----------+--------------+--------------+---------------+-------+----------+-----+------------+----------+---------+-------+------------+-----------+-----------+---------+-------+--------------------------+-------+------+--------------------+---------------------------------+------------+--------------+-------------+--------+-------------------------+-------- 67829 | 22058 | X378 | 2004-02-10 | 2004-02-10 | 0.000 | 5411 | 15498-00 | GBP | 0.00 | t | t | | | | | | | 2004-02-10 | 60 | 0.00 | 0.00 |0 | 0.00 | Carton | 570.000 | 0.000 | CIF-MONTREAL/ACT 30-DAYS | | | Chemist's sundries | AMGMEDICAL;5411;MONTREAL;1-60; | THAMESPORT | SEA/ALLIANCE | CA | GB | MAIREAD BOYCE, DIRECTOR | 1 (1 row) The problem is seen in columns 28 and 29 (dimensions and terms). The data for terms is in dimensions, which ought to contain the empty string. Table definitions: Table "prod.export_invoice" Column | Type | Modifiers ---------------+-----------------------+-------------------------------------- invno | integer | not null customer | character varying(10) | not null account | character varying(8) | not null invdate | date | not null default ('now'::text)::date taxpoint | date | not null default ('now'::text)::date discount | numeric(5,3) | not null default 0.0 ordno | integer | not null custref | text | not null default 'NONE'::text currency | character(3) | not null carriage | numeric(10,2) | not null default 0.00 printed | boolean | not null default false assigned | boolean | not null default false customer_ean | character varying(13) | location_ean | character varying(13) | exchange_rate | numeric(10,4) | grpid | character varying(2) | postcode | character varying(10) | grn | character varying(20) | orderdate | date | packages | integer | not null 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 | character(2) | origin | character(2) | not null default 'GB'::bpchar auth | text | copies | integer | not null default 1 ...indexes and constraints omitted...
On Wed, 2005-10-12 at 09:09 +0100, Oliver Elphick wrote: > Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12 > > I am importing a table using COPY. The data is tab-delimited. COPY > seems to be putting the data for one field into the preceding field, > which should contain the empty string. To follow up on this, there seems to be a general problem that COPY did not use the same column order as a default display for this particular table. The table had just been created and had not been subjected to alterations. I got round the problem by specifying the column list to the COPY command. -- 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
Oliver Elphick <olly@bray-healthcare.com> writes: > Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12 > I am importing a table using COPY. The data is tab-delimited. COPY > seems to be putting the data for one field into the preceding field, > which should contain the empty string. Can't reproduce that here, under either 8.0 or CVS tip. Are you sure you aren't making some silly mistake, like copying from the wrong file? regards, tom lane
On Wed, 2005-10-12 at 13:52 -0400, Tom Lane wrote: > Oliver Elphick <olly@bray-healthcare.com> writes: > > Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12 > > I am importing a table using COPY. The data is tab-delimited. COPY > > seems to be putting the data for one field into the preceding field, > > which should contain the empty string. > > Can't reproduce that here, under either 8.0 or CVS tip. Are you sure > you aren't making some silly mistake, like copying from the wrong file? No. This is a regular (nightly) scripted dump, which drops and rebuilds the entire database, and the right data is going into the file (I only showed one line out of 300+). But in this particular case, the column order was wrong. 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. I've changed the script to fix it, so its not a big issue for me, but I first noticed it several days ago, so it's not a one off occurrence. Oliver -- 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
On Wed, Oct 12, 2005 at 09:09:05AM +0100, Oliver Elphick wrote: > Pg 8.0.3 (Debian package) on AMD64, linux 2.6.12 > > I am importing a table using COPY. The data is tab-delimited. COPY > seems to be putting the data for one field into the preceding field, > which should contain the empty string. I couldn't duplicate this in 8.0.4 on either FreeBSD or Solaris. I created a table using your definition and put the data into a file, converting ^I to tab and removing the trailing $. perl -lne 'if (/^67829/) {s/\^I/\t/g; s/\$$//; print}' msg.txt > data.txt I used COPY to load data.txt; SELECT then returned the following: \x SELECT * FROM export_invoice; ... kilos | 570.000 nett | 0.000 dimensions | terms | CIF-MONTREAL/ACT 30-DAYS bank | goods | Chemist's sundries ... I see a few COPY fixes in pgsql-committers, although I don't know if any would affect the behavior you're seeing. Have you tried 8.0.4? Have you done any tests with a freshly-created table? Have you executed any ALTER TABLE statements on export_invoice? I don't know if COPY could have problems with that; I'm just wondering what might be different between your environment and mine, aside from the PostgreSQL version and operating system. On another note, regarding the following: > invdate | date | not null default ('now'::text)::date > taxpoint | date | not null default ('now'::text)::date Are you sure you want 'now'::text as a default, considering the warning against it? http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT -- Michael Fuhr
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? -- Michael Fuhr
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. -- Michael Fuhr
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_rateIS 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
On Wed, Oct 12, 2005 at 08:23:15PM +0100, Oliver Elphick wrote: > On Wed, 2005-10-12 at 12:13 -0600, Michael Fuhr wrote: > > On another note, regarding the following: > > > > > invdate | date | not null default ('now'::text)::date > > > taxpoint | date | not null default ('now'::text)::date > > > > Are you sure you want 'now'::text as a default, considering the > > warning against it? > > I actually use CURRENT_DATE; that is what the system turns it into. Ah yes, I see that now. I generally use now(), so I hadn't noticed that CURRENT_DATE and CURRENT_TIMESTAMP become 'now', whereas only a literal 'now' is expanded at create time: CREATE TABLE foo ( d1 date NOT NULL DEFAULT now(), d2 date NOT NULL DEFAULT CURRENT_DATE, d3 date NOT NULL DEFAULT 'now' ); \d foo Table "public.foo" Column | Type | Modifiers --------+------+-------------------------------------- d1 | date | not null default now() d2 | date | not null default ('now'::text)::date d3 | date | not null default '2005-10-12'::date -- Michael Fuhr
On Wed, Oct 12, 2005 at 08:17:23PM +0100, Oliver Elphick wrote: > On Wed, Oct 12, 2005 at 12:19:41PM -0600, Michael Fuhr wrote: > > Could you post the table definitions? > > Here it is: I created the tables you posted (sans foreign key constraints because you didn't include the referenced tables) and loaded the data from your previous post into an 8.0.4 server on FreeBSD 4.11-STABLE. Here's what I got; it looks correct around the columns you report having trouble with: \x SELECT * FROM export_invoice; -[ RECORD 1 ]-+-------------------------------- invno | 67829 customer | 22058 account | X378 invdate | 2004-02-10 taxpoint | 2004-02-10 discount | 0.000 ordno | 5411 custref | 15498-00 currency | GBP carriage | 0.00 printed | t assigned | t customer_ean | location_ean | exchange_rate | grpid | postcode | grn | orderdate | 2004-02-10 packages | 60 packing | 0.00 other | 0.00 other_desc | 0 insurance | 0.00 pack_desc | Carton kilos | 570.000 nett | 0.000 dimensions | terms | CIF-MONTREAL/ACT 30-DAYS bank | goods | Chemist's sundries marks | AMG MEDICAL;5411;MONTREAL;1-60; port | THAMESPORT transport | SEA/ALLIANCE destination | CA origin | GB auth | MAIREAD BOYCE, DIRECTOR copies | 1 -- Michael Fuhr
Oliver Elphick <olly@lfix.co.uk> writes: > ... in this particular case, the column > order was wrong. 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. Hm. Could we see the actual pg_attribute data for both this table and its parent? I'm thinking of something like select * from pg_attribute where attrelid = 'foo'::regclass order by attnum; for each table. regards, tom lane
Michael Fuhr <mike@fuhr.org> writes: > On Wed, Oct 12, 2005 at 08:23:15PM +0100, Oliver Elphick wrote: >> I actually use CURRENT_DATE; that is what the system turns it into. > Ah yes, I see that now. I generally use now(), so I hadn't noticed > that CURRENT_DATE and CURRENT_TIMESTAMP become 'now', whereas only > a literal 'now' is expanded at create time: Yeah. That implementation of CURRENT_TIMESTAMP and friends is historical, and probably ought to be changed sometime. It works OK but it's ugly, especially for reverse-listing purposes. regards, tom lane
On Wed, 2005-10-12 at 17:45 -0400, Tom Lane wrote: > Oliver Elphick <olly@lfix.co.uk> writes: > > ... in this particular case, the column > > order was wrong. 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. > > Hm. Could we see the actual pg_attribute data for both this table and > its parent? I'm thinking of something like > > select * from pg_attribute where attrelid = 'foo'::regclass order by attnum; > > for each table. > > regards, tom lane Here you are: bray=# select * from pg_attribute where attrelid = 'invoice'::regclass order by attnum; attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval |attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount -----------+---------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+------------- 358570253 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570253 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570253 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570253 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570253 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570253 | oid | 26 | 0 | 4 | -2 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570253 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f |p | s | t | f | f | t | 0 358570253 | invno | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570253 | customer | 1043 | -1 | -1 | 2 | 0 | -1 | 14 | f |x | i | t | f | f | t | 0 358570253 | account | 1043 | -1 | -1 | 3 | 0 | -1 | 12 | f |x | i | t | f | f | t | 0 358570253 | invdate | 1082 | -1 | 4 | 4 | 0 | -1 | -1 | t |p | i | t | t | f | t | 0 358570253 | taxpoint | 1082 | -1 | 4 | 5 | 0 | -1 | -1 | t |p | i | t | t | f | t | 0 358570253 | discount | 1700 | -1 | -1 | 6 | 0 | -1 | 327687 | f |m | i | t | t | f | t | 0 358570253 | ordno | 23 | -1 | 4 | 7 | 0 | -1 | -1 | t |p | i | f | f | f | t | 0 358570253 | custref | 25 | -1 | -1 | 8 | 0 | -1 | -1 | f |x | i | t | t | f | t | 0 358570253 | currency | 1042 | -1 | -1 | 9 | 0 | -1 | 7 | f |x | i | t | f | f | t | 0 358570253 | carriage | 1700 | -1 | -1 | 10 | 0 | -1 | 655366 | f |m | i | t | t | f | t | 0 358570253 | printed | 16 | -1 | 1 | 11 | 0 | -1 | -1 | t |p | c | t | t | f | t | 0 358570253 | assigned | 16 | -1 | 1 | 12 | 0 | -1 | -1 | t |p | c | t | t | f | t | 0 358570253 | customer_ean | 1043 | -1 | -1 | 13 | 0 | -1 | 17 | f |x | i | f | f | f | t | 0 358570253 | location_ean | 1043 | -1 | -1 | 14 | 0 | -1 | 17 | f |x | i | f | f | f | t | 0 358570253 | exchange_rate | 1700 | -1 | -1 | 15 | 0 | -1 | 655368 | f |m | i | f | f | f | t | 0 358570253 | grpid | 1043 | -1 | -1 | 16 | 0 | -1 | 6 | f |x | i | f | f | f | t | 0 358570253 | postcode | 1043 | -1 | -1 | 17 | 0 | -1 | 14 | f |x | i | f | f | f | t | 0 358570253 | grn | 1043 | -1 | -1 | 18 | 0 | -1 | 24 | f |x | i | f | f | f | t | 0 358570253 | orderdate | 1082 | -1 | 4 | 19 | 0 | -1 | -1 | t |p | i | f | f | f | t | 0 (26 rows) bray=# select * from pg_attribute where attrelid = 'export_invoice'::regclass order by attnum; attrelid | attname | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval |attstorage | attalign | attnotnull | atthasdef | attisdropped | attislocal | attinhcount -----------+---------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+--------------+------------+------------- 358570504 | tableoid | 26 | 0 | 4 | -7 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570504 | cmax | 29 | 0 | 4 | -6 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570504 | xmax | 28 | 0 | 4 | -5 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570504 | cmin | 29 | 0 | 4 | -4 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570504 | xmin | 28 | 0 | 4 | -3 | 0 | -1 | -1 | t |p | i | t | f | f | t | 0 358570504 | oid | 26 | 0 | 4 | -2 | 0 | -1 | -1 | t |p | i | t | f | f | t | 1 358570504 | ctid | 27 | 0 | 6 | -1 | 0 | -1 | -1 | f |p | s | t | f | f | t | 0 358570504 | invno | 23 | -1 | 4 | 1 | 0 | -1 | -1 | t |p | i | t | f | f | f | 1 358570504 | customer | 1043 | -1 | -1 | 2 | 0 | -1 | 14 | f |x | i | t | f | f | f | 1 358570504 | account | 1043 | -1 | -1 | 3 | 0 | -1 | 12 | f |x | i | t | f | f | f | 1 358570504 | invdate | 1082 | -1 | 4 | 4 | 0 | -1 | -1 | t |p | i | t | t | f | f | 1 358570504 | taxpoint | 1082 | -1 | 4 | 5 | 0 | -1 | -1 | t |p | i | t | t | f | f | 1 358570504 | discount | 1700 | -1 | -1 | 6 | 0 | -1 | 327687 | f |m | i | t | t | f | f | 1 358570504 | ordno | 23 | -1 | 4 | 7 | 0 | -1 | -1 | t |p | i | t | f | f | f | 1 358570504 | custref | 25 | -1 | -1 | 8 | 0 | -1 | -1 | f |x | i | t | t | f | f | 1 358570504 | currency | 1042 | -1 | -1 | 9 | 0 | -1 | 7 | f |x | i | t | f | f | f | 1 358570504 | carriage | 1700 | -1 | -1 | 10 | 0 | -1 | 655366 | f |m | i | t | t | f | f | 1 358570504 | printed | 16 | -1 | 1 | 11 | 0 | -1 | -1 | t |p | c | t | t | f | f | 1 358570504 | assigned | 16 | -1 | 1 | 12 | 0 | -1 | -1 | t |p | c | t | t | f | f | 1 358570504 | customer_ean | 1043 | -1 | -1 | 13 | 0 | -1 | 17 | f |x | i | f | f | f | f | 1 358570504 | location_ean | 1043 | -1 | -1 | 14 | 0 | -1 | 17 | f |x | i | f | f | f | f | 1 358570504 | exchange_rate | 1700 | -1 | -1 | 15 | 0 | -1 | 655368 | f |m | i | f | f | f | f | 1 358570504 | grpid | 1043 | -1 | -1 | 16 | 0 | -1 | 6 | f |x | i | f | f | f | f | 1 358570504 | postcode | 1043 | -1 | -1 | 17 | 0 | -1 | 14 | f |x | i | f | f | f | f | 1 358570504 | grn | 1043 | -1 | -1 | 18 | 0 | -1 | 24 | f |x | i | f | f | f | f | 1 358570504 | orderdate | 1082 | -1 | 4 | 19 | 0 | -1 | -1 | t |p | i | f | f | f | f | 1 358570504 | packages | 23 | -1 | 4 | 20 | 0 | -1 | -1 | t |p | i | t | t | f | t | 0 358570504 | packing | 1700 | -1 | -1 | 21 | 0 | -1 | 786438 | f |m | i | f | f | f | t | 0 358570504 | other | 1700 | -1 | -1 | 22 | 0 | -1 | 786438 | f |m | i | f | f | f | t | 0 358570504 | other_desc | 25 | -1 | -1 | 23 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | insurance | 1700 | -1 | -1 | 24 | 0 | -1 | 786438 | f |m | i | f | f | f | t | 0 358570504 | pack_desc | 25 | -1 | -1 | 25 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | kilos | 1700 | -1 | -1 | 26 | 0 | -1 | 786439 | f |m | i | f | f | f | t | 0 358570504 | nett | 1700 | -1 | -1 | 27 | 0 | -1 | 786439 | f |m | i | f | f | f | t | 0 358570504 | dimensions | 25 | -1 | -1 | 28 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | terms | 25 | -1 | -1 | 29 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | bank | 25 | -1 | -1 | 30 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | goods | 25 | -1 | -1 | 31 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | marks | 25 | -1 | -1 | 32 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | port | 25 | -1 | -1 | 33 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | transport | 25 | -1 | -1 | 34 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | destination | 1042 | -1 | -1 | 35 | 0 | -1 | 6 | f |x | i | f | f | f | t | 0 358570504 | origin | 1042 | -1 | -1 | 36 | 0 | -1 | 6 | f |x | i | t | t | f | t | 0 358570504 | auth | 25 | -1 | -1 | 37 | 0 | -1 | -1 | f |x | i | f | f | f | t | 0 358570504 | copies | 23 | -1 | 4 | 38 | 0 | -1 | -1 | t |p | i | t | t | f | t | 0 (45 rows) -- 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
Oliver Elphick <olly@lfix.co.uk> writes: > On Wed, 2005-10-12 at 17:45 -0400, Tom Lane wrote: >> Hm. Could we see the actual pg_attribute data for both this table and >> its parent? > Here you are: Thanks. Nothing particularly strange-looking there though. Do you want to try tracing through COPY with a debugger to see where it's getting the bogus column order from? regards, tom lane
On Wed, Oct 12, 2005 at 07:28:37PM -0400, Tom Lane wrote: > Oliver Elphick <olly@lfix.co.uk> writes: > > On Wed, 2005-10-12 at 17:45 -0400, Tom Lane wrote: > >> Hm. Could we see the actual pg_attribute data for both this table and > >> its parent? > > > Here you are: > > Thanks. Nothing particularly strange-looking there though. Do you want > to try tracing through COPY with a debugger to see where it's getting > the bogus column order from? Do you get the same bogosity with the attached test case? It's based on the same table definition and data from your first message; about the only differences from what you're doing are that the table isn't inherited and it uses "COPY foo FROM stdin" instead of reading the data from an external file. If this test works correctly, what happens if you use "COPY FROM stdin" on the real table? -- Michael Fuhr
Вложения
On Wed, 2005-10-12 at 19:28 -0400, Tom Lane wrote: > Oliver Elphick <olly@lfix.co.uk> writes: > > On Wed, 2005-10-12 at 17:45 -0400, Tom Lane wrote: > >> Hm. Could we see the actual pg_attribute data for both this table and > >> its parent? > > > Here you are: > > Thanks. Nothing particularly strange-looking there though. Do you want > to try tracing through COPY with a debugger to see where it's getting > the bogus column order from? The problem has gone away after installation of 8.0.4, so now I can't :-( -- 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