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 );
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?
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, Pennsylvania 19026