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: