Thread: using copy to load odd characters
Howdy:
Running PostgreSQL 7.3.3 on RedHat Linux 7.2
I'm trying to load data from a text file that
has an odd character in it (^@). From what I
could tell, it's a NULL character. How can I
load that into the database via the command line?
I've done the following:
[snip]
bcn2=> \copy db2_cn1pmemb from '/raid/backups/science/cn1pmemb.out' with delimiter as '|' NULL as '\000'
\.
ERROR: copy: line 151676, Missing data for column "c_state"
lost synchronization with server, resetting connection
bcn2=> \copy db2_cn1pmemb from '/raid/backups/science/cn1pmemb.out' with delimiter as '|' NULL as 'NULL'
\.
ERROR: copy: line 151676, Missing data for column "c_state"
lost synchronization with server, resetting connection
bcn2=> \copy db2_cn1pmemb from '/raid/backups/science/cn1pmemb.out' with delimiter as '|' NULL as NULL
\.
ERROR: copy: line 151676, Missing data for column "c_state"
lost synchronization with server, resetting connection
bcn2=> \q
[/snip]
As well as '^@' and '\^@', but I haven't found anything that
works. Can someone tell me what I am doing wrong?
Thanks!
-X
Johnson, Shaunn writes: > I'm trying to load data from a text file that > has an odd character in it (^@). From what I > could tell, it's a NULL character. How can I > load that into the database via the command line? Depends on what data type the column is supposed to have. Character data types cannot contain null bytes. The type bytea can store arbitrary binary data. It requires you to escape null bytes so '\\000'. -- Peter Eisentraut peter_e@gmx.net
--thanks for the reply!
--i've tried your example and it
--doesn't work for me.
--i'm also interested in why it's doing
--that (the problem). it seems that if the
--field is a null and the column size is
--char(1) or so, the load will fail. in my
--case, the field is a char(2) - would that have
--an impact on the load?
--maybe this is a red herring ...
-X
-----Original Message-----
From: Peter Eisentraut
To: Johnson, Shaunn
Cc: 'pgsql-general@postgresql.org '
Sent: 10/7/03 6:17 AM
Subject: Re: [GENERAL] using copy to load odd characters
Johnson, Shaunn writes:
> I'm trying to load data from a text file that
> has an odd character in it (^@). From what I
> could tell, it's a NULL character. How can I
> load that into the database via the command line?
Depends on what data type the column is supposed to have. Character
data
types cannot contain null bytes. The type bytea can store arbitrary
binary data. It requires you to escape null bytes so '\\000'.
--
Peter Eisentraut peter_e@gmx.net
> Depends on what data type the column is supposed to have. Character > data > types cannot contain null bytes. The type bytea can store arbitrary > binary data. It requires you to escape null bytes so '\\000'. When you dump some data using pg_dump, null fields are represented as \N. Maybe you can try that. Hope that helps. --------------- Francois Home page: http://www.monpetitcoin.com/ "Would Descartes have programmed in Pascal?" - Umberto Eco
Johnson, Shaunn writes: > --i've tried your example and it > --doesn't work for me. I did not see myself giving an example. > --i'm also interested in why it's doing > --that (the problem). it seems that if the > --field is a null and the column size is > --char(1) or so, the load will fail. in my > --case, the field is a char(2) - would that have > --an impact on the load? Totally impossible to tell unless you tell us exactly what you are doing and what you expected out of it. -- Peter Eisentraut peter_e@gmx.net
> Depends on what data type the column is supposed to have. Character > data > types cannot contain null bytes. The type bytea can store arbitrary > binary data. It requires you to escape null bytes so '\\000'. When you dump some data using pg_dump, null fields are represented as \N. Maybe you can try that. Hope that helps. --------------- Francois Home page: http://www.monpetitcoin.com/ "Would Descartes have programmed in Pascal?" - Umberto Eco
--howdy:
--to reiterate the problem, i am simply doing
--a copy of a text file into a table:
[snip example]
testdb> \copy t_test1 from '/var/tmp/results.txt' with delimiter as '|'
[/snip example]
--the problem was that there were ^@ characters in the file
--and PostgreSQL didn't / couldn't load the data and terminated
--the process.
--my solution was to split the large files and, with VI,
--removed the ^@ characters by hand. the copy went
--as expected.
--i expected PostgreSQL to load the data, regardless
--of what was in the file. i'm only saying i 'expected'
--this because i've loaded the same file into the same
--table structure as DB2 (also my source for this file)
--and Oracle 9 with no problem. my apologies for assuming.
--i suppose my next question could be 'why couldn't
--PostgreSQL load characters like this into a table?'
--but it seems like the best thing for future events
--is to write a perl script to scan the text files and
--remove them before loading the data.
--thanks!
>Totally impossible to tell unless you tell us
>exactly what you are doing
>and what you expected out of it.
-X