Problem with COPY in 8.0.3 - Mailing list pgsql-bugs
From | Oliver Elphick |
---|---|
Subject | Problem with COPY in 8.0.3 |
Date | |
Msg-id | 1129104545.6751.190.camel@linda.lfix.co.uk Whole thread Raw |
Responses |
Re: Problem with COPY in 8.0.3
Re: Problem with COPY in 8.0.3 Re: Problem with COPY in 8.0.3 |
List | pgsql-bugs |
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...
pgsql-bugs by date: