Thread: bytea
Hello, I have a table containing a field of type bytea: CREATE TABLE a_table ( a_field bytea ); How can I import a file in a SQL script? What function I can use? Thank you very much. Adrian
> > I have a table containing a field of type bytea: > > CREATE TABLE a_table ( > a_field bytea > ); > > How can I import a file in a SQL script? What function I can use? > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed significantly since) Octets of certain values must be escaped (but all octet values may be escaped) when used as part of a string literal in an SQL statement. In general, to escape an octet, it is converted into the three-digit octal number equivalent of its decimal octet value, and preceded by two backslashes. In general it goes like this INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ; to load the first four ASCII characters. You did not mention how your file looks like. There is also a C function available called PQescapeBytea which does all the required escaping to store memory areas in bytea columns. Refer to Command Execution Functions within libpq - C Library for details. Regards, Christoph
Hi Christoph, Thanks for your reply. But what I want to do is loading a file of a particular path with a sql statement in psql. Why I need to care about how the file looks like? Thanks. Adrian ----- Original Message ----- From: "Christoph Haller" <ch@rodos.fzk.de> To: <pgsql-sql@postgresql.org> Cc: <achong@cecid.hku.hk> Sent: Wednesday, February 05, 2003 6:15 PM Subject: Re: [SQL] bytea > > > > I have a table containing a field of type bytea: > > > > CREATE TABLE a_table ( > > a_field bytea > > ); > > > > How can I import a file in a SQL script? What function I can use? > > > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed > significantly since) > > Octets of certain values must be escaped (but all octet values may be > escaped) when used as part of a string literal in an SQL > statement. In general, to escape an octet, it is converted into the > three-digit octal number equivalent of its decimal octet value, and > preceded by two backslashes. > > In general it goes like this > INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ; > to load the first four ASCII characters. > You did not mention how your file looks like. > There is also a C function available called PQescapeBytea > which does all the required escaping to store memory areas in bytea > columns. > Refer to Command Execution Functions within libpq - C Library for > details. > > Regards, Christoph > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
> > Thanks for your reply. But what I want to do is loading a file of a particular path with a sql > statement in psql. Why I need to care about how the file looks like? Thanks. > Because "non-printables" might not be properly escaped. If they are, just use the SQL COPY command as described in the doc. Regards, Christoph
That would be convenient but there are some difficulties. Say you have a
function that worked something like:
insert.....values (...., filetobytea("/home/me/myfile"),...);
It would be a nice ability to have but the server may not be on the same
machine (or even the same type/os/filesystem) as the client so sending a
pathname to the server is of little use. Such an ability must be handled by
the client which can read/translate the local file into the appropriate byte
sequence to send to the server.
I'm toying with a client program in C program that would include the ability
to take a file on the client machine and store it in a bytea field but
haven't come up with a good way to handle the necessary buffer space (ie. I
don't see any way to "stream" data to the server - it appears I need to
allocate sufficient buffer space for the entire file plus the entire escaped
version of the file before I can generate the query which causes me some
other problems.) Any suggestions would be appreciated.
Cheers,
Steve
On Wednesday 05 February 2003 2:25 am, Adrian Chong wrote:
> Hi Christoph,
>
> Thanks for your reply. But what I want to do is loading a file of a
> particular path with a sql statement in psql. Why I need to care about how
> the file looks like? Thanks.
>
> Adrian
>
> ----- Original Message -----
> From: "Christoph Haller" <ch@rodos.fzk.de>
> To: <pgsql-sql@postgresql.org>
> Cc: <achong@cecid.hku.hk>
> Sent: Wednesday, February 05, 2003 6:15 PM
> Subject: Re: [SQL] bytea
>
> > > I have a table containing a field of type bytea:
> > >
> > > CREATE TABLE a_table (
> > > a_field bytea
> > > );
> > >
> > > How can I import a file in a SQL script? What function I can use?
> >
> > The documentation says as in PostgreSQL 7.2.1 (I doubt this changed
> > significantly since)
> >
> > Octets of certain values must be escaped (but all octet values may be
> > escaped) when used as part of a string literal in an SQL
> > statement. In general, to escape an octet, it is converted into the
> > three-digit octal number equivalent of its decimal octet value, and
> > preceded by two backslashes.
> >
> > In general it goes like this
> > INSERT INTO a_table ( a_field ) VALUES ( '\\000\\001\\002\\003' ) ;
> > to load the first four ASCII characters.
> > You did not mention how your file looks like.
> > There is also a C function available called PQescapeBytea
> > which does all the required escaping to store memory areas in bytea
> > columns.
> > Refer to Command Execution Functions within libpq - C Library for
> > details.
> >
> > Regards, Christoph
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster