How can i deal with "\n" when copy tables from sqlserver2005 to postgre?

From: "bookman bookman" <bookmanb(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How can i deal with "\n" when copy tables from sqlserver2005 to postgre?
Date: 2007-12-14 09:29:21
Message-ID: 28a0e9620712140129s34561e1dkc544682c0b9caba6@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

H i ,

I have solved the "insert NULL" problem,but another problem is feazing
me now.I have a table t_book in sqlserver,I exported it to t_book.txt
,the column content has some special show( I made it purposely).

--t_book.txt
bookid(int) bookname(varchar(50))
content
1 JavaScript: The Definitive Guide
This

is a very

good book!

I wish you will enjoy it!
2 performancePgsql

I created the same table in postgre:

CREATE TABLE t_book(
bookid serial not null primary key,
bookname varchar(50) not null,
content text null
)

Then I copied it to t_book in postgre,

--command
copy t_book from '/home/postgres/data/t_book.txt' WITH NULL AS ''

and came across the error:invailid input syntax for integer:"is"

I inserted the table t_book in postgre in command line client and
coped it to a file t_book1.txt:

--command
copy t_book from '/home/postgres/data/t_book1.txt' WITH NULL AS ''

This is t_book1.txt

--t_book1.txt
1 JavaScript: The Definitive Guide This\n is
a very\good book!\n I wish you will enjoy it!
2 performancePgsql

I also used CSV mode and the the file changed:

--t_book2.txt
1 JavaScript: The Definitive Guide
" This

is a very

good book!

I wish you will enjoy it!"
2 performancePgsql

So I found when i copied t_book.txt into table t_book ,postgresql
treated "is a very good book!" as a new line and
inserted "is" into bookid.

This means the content of a table column must be include in a pair of
quote if it not in a line .The problem is I cannot export the table
with some columns in quote(like content column) and the others
not(like bookid,bookname).If I export a table with quote,all of its
columns will be include in quote,like this:

--t_book5.txt
"1" " JavaScript: The Definitive
Guide " " This

is a very

good book!

I wish you will enjoy it!"
"2" " performancePgsql "
""

If I copy it into t_book in postgre,the error must be:
invalid input syntax for integer:"1"
because the type of "1" is varchar but integer.

So how can i copy tables in sqlserver to postgre when content of a
column not in a line?
Can I copy the file of t_book5.txt into postgre?

Thank you very much!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ow Mun Heng 2007-12-14 09:36:40 Re: HouseKeeping and vacuum Questions
Previous Message Harald Armin Massa 2007-12-14 08:35:53 Re: HouseKeeping and vacuum Questions