Re: Store base64 in database. Use bytea or text?

2011/1/26 Andre Lopes <lopes80andre(at)gmail(dot)com>

> Thanks for the reply.
>
> I will mainly store files with 100kb to 250kb not bigger than this.
>
> PQescapeByteaConn is not available in a default installation of
> PostgreSQL? My hosting account hava a standard installation of
> PostgreSQL. There are other options for escaping binary files?
>
> Best Regards,
>
PQescapeByteConn is a function of libpq - native C client library.
In you case (PHP) you should use its functions to encode binary
data before including it into a query (e.g., in base64) and use
built-in decode() function of Postgres:
-- Pseudo-code
INSERT INTO img (dat) VALUES (decode(BASE64_INPUT_FROM_PHP, 'base64'));

where dat column of table img of type bytea.

Or you can use PHP's function which escapes binary data for bytea
textual representation (now hex by default) and omit decode(). In this
case you query will like that:
-- Pseudo-code
INSERT INTO img (dat) VALUES (BYTEA_ESCAPED_FROM_PHP);

>
>
> On Tue, Jan 25, 2011 at 10:58 PM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> >
> >
> > 2011/1/26 Andre Lopes <lopes80andre(at)gmail(dot)com>
> >>
> >> Thanks for all the reply's.
> >>
> >> I will be using PHP for now to insert data.
> >>
> >> So I shouldn't use base64 to store images or any other kind of files.
> >> I'm new to storing files in the database. This will be my first
> >> experience.
> >
> > You may want to use large objects to store files instead of using
> > bytea data type. With large objects you can achieve streaming
> > data transfer and as of PostgreSQL 9.0 it is possible to control
> > access permissions on large objects via GRANT.
> >
> > But I don't know about support of large objects in PHP.
> >>
> >> I will research about PQescapeByteaConn.
> >>
> >> Thanks for the help.
> >>
> >> Best Regards,
> >>
> >>
> >> On Tue, Jan 25, 2011 at 10:46 PM, Bill Moran <wmoran(at)potentialtech(dot)com>
> >> wrote:
> >> > In response to Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>:
> >> >
> >> >> On 25 January 2011 22:27, Andre Lopes <lopes80andre(at)gmail(dot)com>
> wrote:
> >> >> > Hi,
> >> >> >
> >> >> > I need to put some images on Base64 in a PostgreSQL database. Wich
> >> >> > type should I use and what is the difference between using bytea or
> >> >> > text to store Base64?
> >> >>
> >> >> I really don't think you want to do that. Base64 is used to make
> >> >> binary data 7-bit safe for compatibility with legacy systems (i.e. to
> >> >> embed arbitrary binary data within ASCII). Sometimes people escape
> >> >> binary data as base64 to store it in their DB, but they typically
> >> >> store it as bytea. Base64 probably isn't even a particularly good
> >> >> choice for escaping binary, let alone storing it.
> >> >>
> >> >> You should just use a generic escaping function. libpq has
> >> >> PQescapeByteaConn(), for example.
> >> >
> >> > A warning: last I checked, PHP's pg_escape_bytea() was broken, so be
> >> > cautious if you're using PHP.
> >> >
> >> > --
> >> > Bill Moran
> >> > http://www.potentialtech.com
> >> > http://people.collaborativefusion.com/~wmoran/<http://people.collaborativefusion.com/%7Ewmoran/>
> >> >
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> >
> >
> > --
> > // Dmitriy.
> >
> >
> >
>

--
// Dmitriy.