Thread: Escaping in COPY

How exactly does one escape tabs, newlines etc. when using COPY?  

I remember reading somewhere that one just prefixes a backslash to them
before passing them to PQputline().  And indeed, that seems to work fine.

But when the data is retrieved using another COPY and PQgetline(), I
find the tabs are escaped as '\t'.  I looked around the website and the
mailing list archives, but found nothing on this subject.

Can anyone tell me the definitive place to look?


TIA,

Jeroen

Jeroen T. Vermeulen wrote:

> How exactly does one escape tabs, newlines etc. when using COPY?  
> 
> I remember reading somewhere that one just prefixes a backslash to them
> before passing them to PQputline().  And indeed, that seems to work fine.
> 
> But when the data is retrieved using another COPY and PQgetline(), I
> find the tabs are escaped as '\t'.  I looked around the website and the
> mailing list archives, but found nothing on this subject.
> 
> Can anyone tell me the definitive place to look?


I'd be interested too. From the manual I gather that it is extremely
simple. Only the field delimiter and the row delimiter (alias newline)
and the escape character itself needs escaping. I have just finished
uploading a 15 GB medical record database from Oracle and it actually
worked. I am also very pleased with PostgreSQL's performance so far.
Ah, this is getting good! I can pump data in at basically the same
rate that I read it from Oracle. That is, 600 to 2000 records per
second, and it is with all indexes and foreign keys turned on (FKs
are initially deferred, but eventually they are checked.)

This is coool,
-Gunther

-- 
Gunther Schadow, M.D., Ph.D.                    gschadow@regenstrief.org
Medical Information Scientist      Regenstrief Institute for Health Care
Adjunct Assistant Professor        Indiana University School of Medicine
tel:1(317)630-7960                         http://aurora.regenstrief.org

On Wed, Jun 05, 2002 at 04:54:58PM -0500, Gunther Schadow wrote:
> 
> I'd be interested too. From the manual I gather that it is extremely
> simple. Only the field delimiter and the row delimiter (alias newline)
> and the escape character itself needs escaping. I have just finished
> uploading a 15 GB medical record database from Oracle and it actually
> worked. I am also very pleased with PostgreSQL's performance so far.
> Ah, this is getting good! I can pump data in at basically the same
> rate that I read it from Oracle. That is, 600 to 2000 records per
> second, and it is with all indexes and foreign keys turned on (FKs
> are initially deferred, but eventually they are checked.)

Ah.  Perhaps I'd better do some more optimization work on COPY support
in libpqxx then.  :-)


Jeroen