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!
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 |