Thread: how do i store \0 inside a text attribute?
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 );CREATEvixie=> insert into foo values ( 'a\033b' );INSERT 728084 1vixie=> 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 1vixie=> insert into foo values ( 'a\0b' );INSERT 728085 1vixie=> select length(bar) fromfoo;length------ 1(1 row) vixie=> drop table foo;DROPvixie=> \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?
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
> 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.
> > 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, Pennsylvania19026