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

Поиск
Список
Период
Сортировка
От bookman bookman
Тема How can i deal with "\n" when copy tables from sqlserver2005 to postgre?
Дата
Msg-id 28a0e9620712140129s34561e1dkc544682c0b9caba6@mail.gmail.com
обсуждение исходный текст
Ответы Re: How can i deal with "\n" when copy tables from sqlserver2005 to postgre?
Список 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!

В списке pgsql-general по дате отправления:

Предыдущее
От: Ashish Karalkar
Дата:
Сообщение: Re: Planner ignoring to use INDEX SCAN
Следующее
От: ptjm@interlog.com (Patrick TJ McPhee)
Дата:
Сообщение: Re: timestamp with time zone