Re: Re: [GENERAL] Using BLOBs with PostgreSQL

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: kientzle(at)acm(dot)org, "Martin A(dot) Marques" <martin(at)math(dot)unl(dot)edu(dot)ar>, PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: Re: [GENERAL] Using BLOBs with PostgreSQL
Date: 2000-10-10 02:40:45
Message-ID: 17172.971145645@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> "bytea for binary data with escapes"??
>>
>> What would those escapes look like? I could not find any escape
>> that would put a NULL character into a bytea field.

> 'a\\0b' put a, NULL, b. You need double-backslash to get one backslash
> to the backend.

Looking at byteain, it's clear that you must write 3 octal digits,
so the correct incantation is
a\000b
in COPY input data, or
a\\000b
in a quoted literal in an SQL INSERT statement (since the parser will
eat one level of backslash before the type-specific converter gets it).

This strikes me as not too unreasonable for ASCII conversion of bytea.

I believe that it should be possible to retrieve an unconverted form
of a bytea value via a BINARY CURSOR, though I haven't tried it myself.

You could also do I/O without conversion using COPY BINARY, but only if
you are superuser, and even then the surrounding file syntax is weird
and undocumented. COPY BINARY across stdin/stdout (which'd be needed
for non-superuser usage) doesn't work.

I don't think there's anything particularly wrong with toasted bytea
as a storage mechanism for non-textual BLOBs, but we'd certainly need
to do something about the COPY situation to make storing random binary
data in a bytea anything except an exercise in masochism. Also it'd
be nice if bytea wasn't quite so impoverished operator-wise.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Fabrice Scemama 2000-10-10 02:57:49 Re: ODBC driver under the Windows 2000
Previous Message bmccoy 2000-10-10 01:58:17 Re: Question

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2000-10-10 02:46:56 Re: Small patch to replace 'idle' by 'trans' if transaction is still open
Previous Message Horst Herb 2000-10-10 00:32:40 generic triggers - implementation request