for my pgcat utility i now know i have to use \nnn octal quoting for
nonprintables in the generated INSERT commands.  but in testing, i
found the following oddity.  this is in 7.1-b1 (cvs-current).

	vixie=> create table foo ( bar text );
	CREATE
	vixie=> insert into foo values ( 'a\033b' );
	INSERT 728084 1
	vixie=> select length(bar) from foo;
	length
	------
	     3
	(1 row)

great!  it stored the escape.  and since SELECT's front/back end protocol
is counted-string rather than quoted text, it comes back reliably (though
i still intend to try a binary cursor at some point, just to do it.)  BUT:

	vixie=> delete from foo;
	DELETE 1
	vixie=> insert into foo values ( 'a\0b' );
	INSERT 728085 1
	vixie=> select length(bar) from foo;
	length
	------
	     1
	(1 row)

	vixie=> drop table foo;
	DROP
	vixie=> \q

this is not what i was hoping for at ALL.  evidently the implementation of
text assumes NUL-termination in other places than the parser.  ultimately
this means that pgsql will need a "blob" type whose presentation format is
uuencode or some such.  but is there a workaround for this using "text"?

how would someone be expected to store, say, a GIF image in a TOAST text?

Sat, 23 Dec 2000 10:59:11 -0800
Paul A Vixie <vixie@mfnx.net>
how do i store \0 inside a text attribute?
Paul A Vixie <vixie@mfnx.net> writes:
> this is not what i was hoping for at ALL.  evidently the implementation of
> text assumes NUL-termination in other places than the parser.

Yes.  The entire datatype I/O system is based on null-terminated
strings, so there's no easy way to fix this.  If it were just an
internal problem then maybe we'd bite the bullet and do it, but
breaking every user-defined datatype in existence seems too high
a price to pay for this problem.

> ultimately
> this means that pgsql will need a "blob" type whose presentation format is
> uuencode or some such.

See bytea, though its presentation format leaves something to be desired
IMHO.

> how would someone be expected to store, say, a GIF image in a TOAST text?

One would not.  A TOASTed bytea is the appropriate column type.

			regards, tom lane

Fri, 29 Dec 2000 18:59:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: how do i store \0 inside a text attribute? 
> See bytea, though its presentation format leaves something to be desired IMHO
> 
> > how would someone be expected to store, say, a GIF image in a TOAST text?
> 
> One would not.  A TOASTed bytea is the appropriate column type.

thanks -- that's EXACTLY what i needed.

Fri, 29 Dec 2000 17:30:19 -0800
Paul A Vixie <vixie@mfnx.net>
Re: how do i store \0 inside a text attribute? 
> > See bytea, though its presentation format leaves something to be desired IMHO
> > 
> > > how would someone be expected to store, say, a GIF image in a TOAST text?
> > 
> > One would not.  A TOASTed bytea is the appropriate column type.
> 
> thanks -- that's EXACTLY what i needed.
> 

bytea was not really used very much until people started asking to do
this kind of think.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 1 Jan 2001 22:45:41 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: how do i store \0 inside a text attribute?