Re: Can't copy empty value to datatype 'timestamp'

On 9 Jan 2003, Jon Thorson wrote:

> I'm running into a problem importing data into my database. What seems
> to be the issue is an empty value on a field defined as 'timestamp'. In
> psql I'm using the following command to import the data:
>
> copy devicem1 from '/data/tmp/scshadow/devicem1.out' delimiter '|';
>
> I get the following error:
>
> ERROR: copy: line 1, Bad timestamp external representation ''
>
> There are 109 fields in this table, four fields with the datatype of
> 'timestamp'. The data that I am trying to import has the first two of
> the timestamp fields populated, and the last two are for future use.
> The definition for each timestamp field is simply:
>
> "<field name>" timestamp,
>
> I've tried specifically allowing null values to the fields, but I still
> get the same error. I would include a sample row but it's rather long
> and it would get mangled with the word wrap of my email client. Suffice
> it to say that the data is pipe delimited and the fields that are empty
> look like "||" (minus the double quotes, of course).

By default empty string is not null for copying (since you might want to
insert an empty text string, etc). You can change the sequence that is
considered null into an empty string however,
copy ... delimiter '|' null '';