Thread: binary data
Is there any method to insert binary data in a PostgreSQL row? What data type must I use to insert an image? Thanks in advance.
> Is there any method to insert binary data in a PostgreSQL row? i'm curious too. Chapter 3. of the PostgreSQL 7.1 User's Guide has a description of the data types. http://www.postgresql.org/idocs/index.php?datatype.html i couldn't find any official binary data type listed. you might try using the text data type and uuencode the data on the way in and uudecode it on the way out. the doc note section does mention the "bytea" data type, which works, but i don't know if it is supported or its limitations.
Yes, there is a binary datatype, called 'bytea', which can handle anything you can throw at it, including nulls. The rules for escaping things you want to throw at it are tricky though. (and same for unescaping things you get back from database). If you are using perl, use DBD::Pg version 1.00 or higher, it encapsulates it all transparently. On 13 Jun 2001, Hugh Mandeville wrote: > > Is there any method to insert binary data in a PostgreSQL row? > > i'm curious too. > > Chapter 3. of the PostgreSQL 7.1 User's Guide has a description of the > data types. > > http://www.postgresql.org/idocs/index.php?datatype.html > > i couldn't find any official binary data type listed. you might try > using the text data type and uuencode the data on the way in and > uudecode it on the way out. > > the doc note section does mention the "bytea" data type, which works, > but i don't know if it is supported or its limitations. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >
postgresql's large object functions can be used to store and retrieve binary data. http://www.ca.postgresql.org/devel-corner/docs/postgres/lo-interfaces.html here is some sample code: inv_oid = lo_creat(dbconn, INV_READ | INV_WRITE); if (inv_oid == InvalidOid) { fprintf (stderr, "lo_creat failed:%s\n", PQerrorMessage(dbconn)); return (1); } res = PQexec(dbconn, "BEGIN WORK"); if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) { fprintf(stderr, "PQexe(\"BEGINWORK\") failed: %s\n", PQresultErrorMessage(res)); return (1); } PQclear(res); obj_fd = lo_open(dbconn, inv_oid, INV_READ | INV_WRITE); if (obj_fd == -1) { fprintf (stderr, "lo_open failed:%s\n", PQerrorMessage(dbconn)); return (1); } ret = lo_write (dbconn, obj_fd, buf, buflen); if (ret == -1) { fprintf (stderr, "lo_write failed: %s\n", PQerrorMessage(dbconn)); return (1); } ret = lo_lseek(dbconn, obj_fd, 0, SEEK_SET); if (ret == -1) { fprintf (stderr, "lo_seek failed: %s\n", PQerrorMessage(dbconn)); return (1); } ret = lo_read (dbconn, obj_fd, buf, buflen); if (ret == -1) { fprintf (stderr, "lo_read failed: %s\n", PQerrorMessage(dbconn)); return (1); } lo_close(dbconn, obj_fd); res = PQexec(dbconn, "COMMIT"); PQclear(res);
"Alex Pilosov" <alex@pilosoft.com> wrote in message
news:Pine.BSO.4.10.10106201342060.20262-100000@spider.pilosoft.com...
> Yes, there is a binary datatype, called 'bytea', which can handle anything
> you can throw at it, including nulls.
>
> The rules for escaping things you want to throw at it are tricky though.
> (and same for unescaping things you get back from database).
i ended up having to use the C large object functions to write binary data.
i wasn't able to insert nulls using the INSERT command with psql, C or Perl.
what is the correct way to escape 0? \000 doesn't work for me.
i created a table with a 'bytea' column. i inserted data escaping out
special characters with their 3 digit octet.
everything inserts correctly except null (\000). data with null characters
inserted with no errors but doing a select for the value or for its size
came back incorrectly. it returns everything before the null character.
i tried inserting and selecting the data with psql and the C interface
(PQexec, PQgetvalue, PQgetlength).
here is a log from my psql session. the last insert i did 'null \000 null'
comes back as octet length 5 which is wrong, it should be 11.
test=# CREATE TABLE log (data bytea);
CREATE
test=# INSERT INTO log (data) VALUES ('plain text');
INSERT 61650 1
test=# INSERT INTO log (data) VALUES ('special chars \n \001 \002');
INSERT 61651 1
test=# INSERT INTO log (data) VALUES ('null \000 null');
INSERT 61652 1
test=# SELECT octet_length(data), data FROM log;octet_length | data
--------------+------------------------------ 10 | plain text 19 | special chars \012 \001 \002
5 | null
(3 rows)
> If you are using perl, use DBD::Pg version 1.00 or higher, it encapsulates
> it all transparently.
i also tried doing an insert and using quote() with perl (DBD::Pg v1.0)
$sql = "INSERT INTO log (data) VALUES (" . $dbh->quote($bindata) . ")";
it has the same problem when the data contains a null, perl returns
"DBD::Pg::st execute failed: ERROR: Unterminated quoted string at ..."
this works "Alex Pilosov" <alex@pilosoft.com> wrote in message >> what is the correct way to escape 0? \000 doesn't work for me. >The correct way is \\\\000 (yes really). > >If you are using DBD::Pg after 0.98, it will happen for you automatically >if you bind the variable as SQL_BINARY. >> i also tried doing an insert and using quote() with perl (DBD::Pg v1.0) >You must use $dbh->quote($bindata,DBI::SQL_BINARY) (and DBD::Pg after 1.0)
"Hugh Mandeville" <hughmandeville@hotmail.com> writes:
> "Alex Pilosov" <alex@pilosoft.com> wrote in message
>> The rules for escaping things you want to throw at it are tricky though.
>> (and same for unescaping things you get back from database).
> test=# INSERT INTO log (data) VALUES ('special chars \n \001 \002');
> INSERT 61651 1
> test=# INSERT INTO log (data) VALUES ('null \000 null');
> INSERT 61652 1
> test=# SELECT octet_length(data), data FROM log;
> octet_length | data
> --------------+------------------------------
> 10 | plain text
> 19 | special chars \012 \001 \002
> 5 | null
> (3 rows)
He did say the rules for escaping things are tricky ;-). You need to
double the backslashes, because interpretation of the string literal
takes off one level of backslashing before bytea ever sees it:
regression=# INSERT INTO log (data) VALUES ('null \\000 null');
INSERT 273181 1
regression=# SELECT octet_length(data), data FROM log;octet_length | data
--------------+------------------------------ 10 | plain text 19 | special chars \012 \001 \002
5 | null 11 | null \000 null
(4 rows)
regards, tom lane
On Fri, Jun 22, 2001 at 10:22:32AM -0400, Tom Lane wrote:
> He did say the rules for escaping things are tricky ;-). You need to
> double the backslashes, because interpretation of the string literal
> takes off one level of backslashing before bytea ever sees it:
>
> regression=# INSERT INTO log (data) VALUES ('null \\000 null');
> INSERT 273181 1
> regression=# SELECT octet_length(data), data FROM log;
> octet_length | data
> --------------+------------------------------
> 10 | plain text
> 19 | special chars \012 \001 \002
> 5 | null
> 11 | null \000 null
> (4 rows)
And what use some better encoding if you have a lot of binary chars
in data. For example base64, that code 2 chars to 3 instead \\000
that encode 1 char to 4.
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz
Karel Zak <zakkr@zf.jcu.cz> writes:
> On Fri, Jun 22, 2001 at 10:22:32AM -0400, Tom Lane wrote:
>> He did say the rules for escaping things are tricky ;-).
> And what use some better encoding if you have a lot of binary chars
> in data. For example base64, that code 2 chars to 3 instead \\000
> that encode 1 char to 4.
Yeah, it's pretty messy. Perhaps we could offer a couple of conversion
functions that convert bytea to or from base64 or other popular
encodings. bytea is pretty impoverished --- it hasn't received the
attention it deserves.
regards, tom lane
> Karel Zak <zakkr@zf.jcu.cz> writes: > > On Fri, Jun 22, 2001 at 10:22:32AM -0400, Tom Lane wrote: > >> He did say the rules for escaping things are tricky ;-). > > > And what use some better encoding if you have a lot of binary chars > > in data. For example base64, that code 2 chars to 3 instead \\000 > > that encode 1 char to 4. > > Yeah, it's pretty messy. Perhaps we could offer a couple of conversion > functions that convert bytea to or from base64 or other popular > encodings. bytea is pretty impoverished --- it hasn't received the > attention it deserves. bytea is binary compatible with text/varchar()/char(), right? It stores the same format. -- 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
how binary data is stored, escaped and unescaped seems to vary slightly
between the text and bytea datatypes.
are the following observations correct?
1. Escaping the special characters in the binary data
bytea: the bytea data type needs the backslash character '\' escaped to
'\\\\'
text: the text data type needs '\' escaped to '\\'.
bytea and text handle all escaping all other special characters the same.
test=# insert into bintest (col_varchar) values ('\\'); INSERT 69443 1 test=# insert into bintest (col_bytea) values
('\\');ERROR: Bad input string for type bytea test=# insert into bintest (col_bytea) values ('\\\\');
2. How the data is actually stored in the database.
bytea: stores the data as binary
text: stores all characters as binary expect 0 which it stores as \000
test=# SELECT octet_length(col_bytea) AS col, col_bytea,
octet_length(col_text) AS tol, char_length(col_text) AS tcl, col_text FROM
bintest WHERE oid = 69458;col | col_bytea | tol | tcl | col_text
-----+-----------------------+-----+-----+-------------- 9 | \000\001\002\003hello | 12 | 12 | \000^A^B^Chello
3. Unescaping the special characters
bytea: PQgetvalue() returns a string with all the special characters escaped
out.
text: PQgetvalue returns a string with only the 0 character escaped out.
PQgetlength() on bytea column returns 21 PQgetvalue() returns 00000000: 5c30 3030 5c30 3031 5c30 3032 5c30 3033
\000\001\002\00300000010: 6865 6c6c 6f hello
PQgetlength() on char column returns 12 PQgetvlaue() on char column returns 00000000: 5c30 3030 0102 0368 656c 6c6f
\000...hello
are there any functions for escaping and unescaping binary data?
thanks
On Wed, 27 Jun 2001, Hugh Mandeville wrote:
> how binary data is stored, escaped and unescaped seems to vary slightly
> between the text and bytea datatypes.
>
> are the following observations correct?
>
> 1. Escaping the special characters in the binary data
> bytea: the bytea data type needs the backslash character '\' escaped to
> '\\\\'
Correct.
> text: the text data type needs '\' escaped to '\\'.
Correct.
> bytea and text handle all escaping all other special characters the same.
text cannot handle null character, for bytea you can use it, with '\\000'
> test=# insert into bintest (col_varchar) values ('\\');
> INSERT 69443 1
> test=# insert into bintest (col_bytea) values ('\\');
> ERROR: Bad input string for type bytea
> test=# insert into bintest (col_bytea) values ('\\\\');
>
> 2. How the data is actually stored in the database.
> bytea: stores the data as binary
> text: stores all characters as binary expect 0 which it stores as \000
No, text does not and cannot store a null byte. What you see as \000 is
actually a backslash followed by three zeroes, hence you see 12 tol/tcl.
> test=# SELECT octet_length(col_bytea) AS col, col_bytea,
> octet_length(col_text) AS tol, char_length(col_text) AS tcl, col_text FROM
> bintest WHERE oid = 69458;
> col | col_bytea | tol | tcl | col_text
> -----+-----------------------+-----+-----+--------------
> 9 | \000\001\002\003hello | 12 | 12 | \000^A^B^Chello
>
> 3. Unescaping the special characters
> bytea: PQgetvalue() returns a string with all the special characters escaped
> out.
> text: PQgetvalue returns a string with only the 0 character escaped out.
>
> PQgetlength() on bytea column returns 21
> PQgetvalue() returns
> 00000000: 5c30 3030 5c30 3031 5c30 3032 5c30 3033 \000\001\002\003
> 00000010: 6865 6c6c 6f hello
Correct
>
> PQgetlength() on char column returns 12
> PQgetvlaue() on char column returns
> 00000000: 5c30 3030 0102 0368 656c 6c6f \000...hello
Incorrect because you didn't insert a null character there, it never
escapes anything. You have a backslash followed by zeros.
> are there any functions for escaping and unescaping binary data?
If you are using Perl DBD::Pg, its done for you automatically.
If you are using libpq, currently there are none.
-alex