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

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).

The verison of psql is:

  PostgreSQL 7.3.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3

Any help is greatly appreciated.

Thanks.

-j

On 9 Jan 2003 at 0:50, 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:

Is that last field in the row? Is the text dump file in unix \n format or dos
one.

Copy will choke on dos mode files. There are plenty of ways to convert the
files from one format to another.

HTH

Bye
 Shridhar

--
Not one hundred percent efficient, of course ... but nothing ever is.        -- Kirk,
"Metamorphosis", stardate 3219.8

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 '';

On Thu, 2003-01-09 at 00:59, Stephan Szabo wrote:
> 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 '';

That did it.  I didn't know I could define null as something else when
copying data.  I'll have to examine the docs more carefully next time.

Thanks for the help!

-j