Thread: Copy command to load data into a PostgreSQL DB
We are having trouble coming up with a column separator for the Oracle data that is being imported into our PostgreSQL DB. We are using Fastreader to dump Oracle tables. We then use PostgreSQL copy to load the table data into PostgreSQL. Unfortunately, some of the Oracle data contains every ASCII printable character so we have to dump it with a non-printing character as the column separator We can not figure out how to specify a non-printing character for the delimiter in the PostgreSQL copy command. If we use '\x05' as suggested by the Fastreader User guide. PostgreSQL complains "ERROR: COPY delimiter must be a single character". Any help or suggestions would be appreciated. -=bill stafford
Hi, Le vendredi 17 novembre 2006 21:46, Wm.A.Stafford a écrit : > We can not figure out > how to specify a non-printing character for the delimiter in the > PostgreSQL copy command. If we use '\x05' as suggested by the > Fastreader User guide. PostgreSQL complains "ERROR: COPY delimiter > must be a single character". > > Any help or suggestions would be appreciated. A recent migration from Informix made me write next version of pgloader, which has several interesting features. The one of interest here is that it's able to replace input data file separator when producing COPY data sent to PostgreSQL. One ascii character rarely found into data is §, and the code may allow you to use non printable character (provided your editor allows you to type it into the configuration file). I've had some problems with my cvs pgfoundry credentials, hence the code is temporally located into pgimport module: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgimport/pgimport/ I've made up a debian package, available at debian.dalibo.org repository: http://debian.dalibo.org/unstable/ http://debian.dalibo.org/unstable/pgloader_2.0.2_all.deb deb http://debian.dalibo.org/ unstable/ Hope this helps, -- Dimitri Fontaine http://www.dalibo.com/
Attachment
On Fri, 2006-11-17 at 15:46 -0500, Wm.A.Stafford wrote:
\x05, aka control-e, ctl-e, ^e, C-e, etc, is merely one way to represent the *single* ASCII character 5. When a manual says type \x05, it almost surely means that some program will interpret those four printable chars as a single non-printable character rather than the 4-char string literally. Different programs use different representations of control characters and \x is very common, but the problem is that postgres's copy command doesn't understand this syntax for non-printable characters. No matter, copy will work for you, and in fact you can use TWO ways to represent control-e.
Option 1: Instead of '\x05', type E'\x05', that is:
- letter E
- single quote
- the 4-char string \x05
- single quote
E is postgresql's way of indicating that the string will be interpreted in way that does not conform to SQL spec. This results from the pg developers being pedantic about conformance and refusing to extend the standard carelessly (I love 'em for this attitude).
Option 2: Instead of '\x05', type 'C-vC-e' . By this I mean:
- single quote
- control-v
- control-e
- single quote.
The magic here is that control-v means "take the next character verbatim". In effect, you're typing ASCII char 5 (a single character) literally into the single quotes rather than \x05 or any other representation of it (e.g., \x05). I'm pretty sure that readline is responsible for this interaction, and therefore this probably doesn't work on readline-less installations.
-Reece
If we use '\x05' as suggested by the
Fastreader User guide. PostgreSQL complains "ERROR: COPY delimiter
must be a single character".
Any help or suggestions would be appreciated.
\x05, aka control-e, ctl-e, ^e, C-e, etc, is merely one way to represent the *single* ASCII character 5. When a manual says type \x05, it almost surely means that some program will interpret those four printable chars as a single non-printable character rather than the 4-char string literally. Different programs use different representations of control characters and \x is very common, but the problem is that postgres's copy command doesn't understand this syntax for non-printable characters. No matter, copy will work for you, and in fact you can use TWO ways to represent control-e.
Option 1: Instead of '\x05', type E'\x05', that is:
- letter E
- single quote
- the 4-char string \x05
- single quote
E is postgresql's way of indicating that the string will be interpreted in way that does not conform to SQL spec. This results from the pg developers being pedantic about conformance and refusing to extend the standard carelessly (I love 'em for this attitude).
Option 2: Instead of '\x05', type 'C-vC-e' . By this I mean:
- single quote
- control-v
- control-e
- single quote.
The magic here is that control-v means "take the next character verbatim". In effect, you're typing ASCII char 5 (a single character) literally into the single quotes rather than \x05 or any other representation of it (e.g., \x05). I'm pretty sure that readline is responsible for this interaction, and therefore this probably doesn't work on readline-less installations.
-Reece
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 |