bytea

[Dennis Gearon]

when bytea, text, and varchar(no limit entered) columns are used, do
they ALWAYS use an extra table/file? Or do they only do it after a
certain size of input?

Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Jonathan Bartlett]

> Also, if I wanted to put a *.pdf file in a bytea column, what functions

do I use to escape any characters in it?
What programming language are you using?

In Perl, you do something like:

$sth->bind_param(1, $file_data, DBI::SQL_BINARY); #DBI::SQL_BINARY is
deprecated, but it works

In php you do:

$file_data = pg_escape_bytea($file_data);

$db->query("insert into blah(blahh) values ('${file_data}'::bytea);

To retrieve the info in Perl, it's just a regular fetchrow()

my ($file_data) = $sth->fetchrow();

In php, you have to run stripcslashes() on the data.

list($file_data) = $query->fetchrow();
$file_data = stripcslashes($file_data);

Jon


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dennis Gearon]

I forgot, please CC me, I am on digest.
Dennis Gearon wrote:

when bytea, text, and varchar(no limit entered) columns are used, do
they ALWAYS use an extra table/file? Or do they only do it after a
certain size of input?

Also, if I wanted to put a *.pdf file in a bytea column, what
functions do I use to escape any characters in it?

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Alvaro Herrera]

On Tue, May 11, 2004 at 09:30:15AM -0700, Jonathan Bartlett wrote:

Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?

What programming language are you using?

Apparently if you are using C and libpq, you can use the version 3
protocol functions to send binary data without having to escape it. I
haven't done it though so I may be wrong, but if I'm right please
somebody tell me because I usually recommend this :-)

Also if it works it would be useful to update the Perl/PHP/etc
interfaces to use it.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El sudor es la mejor cura para un pensamiento enfermo" (Bardia)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Dennis Gearon]

Thanks for all the answers everybody, but I need to know also an answer
to the other question:

Does the bytea make its own files automatically for large objects?

Also, how about backups with tables having bytea columns.?

Jonathan Bartlett wrote:

Also, if I wanted to put a *.pdf file in a bytea column, what functions
do I use to escape any characters in it?

What programming language are you using?

In Perl, you do something like:

$sth->bind_param(1, $file_data, DBI::SQL_BINARY); #DBI::SQL_BINARY is
deprecated, but it works

In php you do:

$file_data = pg_escape_bytea($file_data);

$db->query("insert into blah(blahh) values ('${file_data}'::bytea);

To retrieve the info in Perl, it's just a regular fetchrow()

my ($file_data) = $sth->fetchrow();

In php, you have to run stripcslashes() on the data.

list($file_data) = $query->fetchrow();
$file_data = stripcslashes($file_data);

Jon

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[scott.marlowe]

On Tue, 11 May 2004, Dennis Gearon wrote:

Thanks for all the answers everybody, but I need to know also an answer
to the other question:

Does the bytea make its own files automatically for large objects?
Bytea doesn't use large objects, which are kind of an artifact left over
from the days of the 8k row limit. They use what are called "toast"
tables. Toast tables allow for text/varchar/bytea types to overflow from
the base table as needed to occupy up to ~2 gigabytes of drive space per
field max. Note that I'm pretty sure no one's really tried to put 2 gig
in one field, as that would probably take quite some time, and I'm not
sure how well most clients are gonna handle getting back a row with a 2
gig field in it. :-)

And yes, toasting is fully automatic. Just insert a large
text/varchar/bytea field and the database does the rest. which is why
they are generally recommended over using large objects, which require
specialized handling.
Also, how about backups with tables having bytea columns.?

Just like any other field. pg_dump will escape them as needed to make a
usable backup.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alvaro Herrera]

On Tue, May 11, 2004 at 11:50:06AM -0700, Dennis Gearon wrote:

Thanks for all the answers everybody, but I need to know also an answer
to the other question:

Does the bytea make its own files automatically for large objects?
No, they are stored in tables.
Also, how about backups with tables having bytea columns.?

What about them? They should work just fine.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La vida es para el que se aventura"

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Jonathan Bartlett]

> Does the bytea make its own files automatically for large objects?

I do not believe so. You'd have to check with someone else for sure,
though.

Also, how about backups with tables having bytea columns.?

Regular pg_dump handles bytea columns just fine.

Jon
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend