Problem with COPY in 8.0.3

From: Oliver Elphick <olly(at)bray-healthcare(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with COPY in 8.0.3
Date: 2005-10-12 08:09:05
Message-ID: 1129104545.6751.190.camel@linda.lfix.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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/ACT 30-DAYS^I^IChemist\'s sundries^IAMG MEDICAL;5411;MONTREAL;1-60;^ITHAMESPORT^ISEA/ALLIANCE^ICA^IGB^IMAIREAD BOYCE, 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 | AMG MEDICAL;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...

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Eduard Wulff 2005-10-12 10:02:26 BUG #1957: Error for createlang
Previous Message Jim C. Nasby 2005-10-11 23:10:29 Re: My investigations of the postmaster Bus error