Re: Problem with COPY in 8.0.3

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