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:

Previous
From: "Eduard Wulff"
Date:
Subject: BUG #1957: Error for createlang
Next
From: "Karl O. Pinc"
Date:
Subject: BUG #1956: Plpgsql top-level DECLARE does not share scope with CREATE FUNCTION