Re: Copy command to load data into a PostgreSQL DB

On Fri, 2006-11-17 at 15:46 -0500, Wm.A.Stafford wrote:

> 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