471,092 Members | 1,539 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Copy command and import - MS SQL Server to Postgres

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 havereceived 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.
Nov 23 '05 #1
4 12352
On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri
<gp********@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2
On Fri, 2004-11-05 at 16:48, Allen Landsidel wrote:
On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri
<gp********@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #3
Robert Fitzpatrick <ro****@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4
I know this doesn't answer your question, but have you considered doing it with DTS instead of BCP?
I used it recently to migrate an Access database to PostGreSQL and it worked great. One of the big advantages is the ability to transform the data as it is being converted.
It is also built in to MSSQL Server. I have used it numerous times for data transformations within SQL Server and have always enjoyed working with it.
""Goutam Paruchuri"" <gp********@oneil.com> wrote in message news:B2************************************@DAYTON EX.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.

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by cbb | last post: by
9 posts views Thread by Sarah | last post: by
2 posts views Thread by Don Isgitt | last post: by
2 posts views Thread by J M | last post: by
1 post views Thread by edwilli | last post: by
1 post views Thread by Bob Powell | last post: by
4 posts views Thread by Jon Asher | last post: by
reply views Thread by Robert Fitzpatrick | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.