Re: [GENERAL] Copy command and import - MS SQL Server to Postgres
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"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED]... 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 NoticeThe 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.
Re: [GENERAL] Copy command and import - MS SQL Server to Postgres
Robert Fitzpatrick <[EMAIL PROTECTED]> 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
Re: [GENERAL] Copy command and import - MS SQL Server to Postgres
Title: Re: [GENERAL] Copy command and import - MS SQL Server to Postgres I tried by taking the .000 still the same issue. WITH NULL AS '' works fine. WITH NULL AS NULL gives an error as well. - Goutam From: Robert Fitzpatrick [mailto:[EMAIL PROTECTED]Sent: Fri 11/5/2004 5:33 PMTo: Allen LandsidelCc: Goutam Paruchuri; [EMAIL PROTECTED]Subject: Re: [GENERAL] Copy command and import - MS SQL Server to Postgres On Fri, 2004-11-05 at 16:48, Allen Landsidel wrote:> On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri> <[EMAIL PROTECTED]> 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 ina timestamp field in 7.4.5, it is invalid.--Robert 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.
Re: [GENERAL] Copy command and import - MS SQL Server to Postgres
On Fri, 2004-11-05 at 16:48, Allen Landsidel wrote: > On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri > <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Copy command and import - MS SQL Server to Postgres
On Fri, 5 Nov 2004 16:31:21 -0500, Goutam Paruchuri <[EMAIL PROTECTED]> 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
