Thread: Copy command and import - MS SQL Server to Postgres

On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri
<gparuchuri@oneil.com> wrote:
>
> Iam trying to import data from ms-sql server to postgres. I export the data
> which has datetime columns in sql server using BCP. I use the following to
> import back into postgres.
>
> copy tablename from 'c:\\bcpdata\\mcfa\\tablename.txt' with delimiter as
> '\t'
>
> I get the following error !!
> invalid input syntax for type timestamp: ""
>
> My input file has the timestamp value like
>
> 2004-09-30 11:31:00.000
>
> Any clues ???

I recently did the same thing, I left DELIMITER alone since \t is the
default, but I did have to do "WITH NULL as ''" since some of the
datetimes in MSSQL were empty.

By default the copy will bomb out on NULL fields even if you don't
have a NOT NULL constraint on the column, for one reason or another.

I suppose "WITH NULL as NULL" would've worked just as well, in hindsight.

-Allen

On Fri, 2004-11-05 at 16:48, Allen Landsidel wrote:
> On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri
> <gparuchuri@oneil.com> wrote:
> >
> > Iam trying to import data from ms-sql server to postgres. I export the data
> > which has datetime columns in sql server using BCP. I use the following to
> > import back into postgres.
> >
> > copy tablename from 'c:\\bcpdata\\mcfa\\tablename.txt' with delimiter as
> > '\t'
> >
> > I get the following error !!
> > invalid input syntax for type timestamp: ""
> >
> > My input file has the timestamp value like
> >
> > 2004-09-30 11:31:00.000
> >

What about the ".000" on the end? I am not able to enter that format in
a timestamp field in 7.4.5, it is invalid.

--
Robert

Robert Fitzpatrick <robert@webtent.com> writes:
>>> My input file has the timestamp value like
>>> 2004-09-30 11:31:00.000

> What about the ".000" on the end? I am not able to enter that format in
> a timestamp field in 7.4.5, it is invalid.

Nonsense.

regression=# select '2004-09-30 11:31:00.000'::timestamp;
      timestamp
---------------------
 2004-09-30 11:31:00
(1 row)

regression=# select '2004-09-30 11:31:00.001'::timestamp;
        timestamp
-------------------------
 2004-09-30 11:31:00.001
(1 row)

regression=# select '2004-09-30 11:31:00.000'::timestamptz;
      timestamptz
------------------------
 2004-09-30 11:31:00-04
(1 row)

            regards, tom lane

""Goutam Paruchuri"" <gparuchuri@oneil.com> wrote in message news:B2C547DF42419645804F05B54290755ADC7C89@DAYTONEX.oneilinc.net...

Iam trying to import data from ms-sql server to postgres. I export the data which has datetime columns in sql server using BCP. I use the following to import back into postgres.

 

copy tablename from 'c:\\bcpdata\\mcfa\\tablename.txt' with delimiter as '\t'

 

I get the following error !!

invalid input syntax for type timestamp: ""

 

My input file has the timestamp value like

 

2004-09-30 11:31:00.000

 

Any clues ???

 

 

Thanks !
Goutam

 

Confidentiality Notice
The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited.