Inline BLOB / BINARY data types in SQL / JDBC
I'd like to add some PostgreSQL specific stuff to Lukas' answer:
The shortest and most easiest solution would be (since PostgreSQL 9.0 at least):
insert into lob_table (data) values( E'\\x0102030405FF' )
without any cast (if the column is already a bytea
one) and only one \\x
mark right at the beginning. This is the "hex format" documented in the section Binary Data Types.
Regarding the X'01FF'
syntax: According to the string constant documentation PostgreSQL does support it - for bit strings. And it seems, that there is no standard conversion from bit to bytea.
There probably isn't a JDBC escape syntax, so I searched around a bit and found and successfully tested the following:
SQL Server, Sybase ASE, Sybase SQL Anywhere
INSERT INTO lob_table VALUES (0x01FF);
DB2
-- Use a blob constructor. This is not needed for VARCHAR FOR BIT DATA types INSERT INTO lob_table VALUES (blob(X'01FF'));
Derby, H2, HSQLDB, Ingres, MySQL, SQLite
INSERT INTO lob_table VALUES (X'01FF');
Oracle
-- As mentioned by a_horse_with_no_name, keep in mind the relatively low -- limitation of Oracle's VARCHAR types to hold only 4000 bytes! INSERT INTO lob_table VALUES (hextoraw('01FF'));
Postgres
-- There is also hex encoding as of Postgres 9.0 -- The explicit cast is important, though INSERT INTO lob_table VALUES (E'\\001\\377'::bytea);
See A.H.'s answer for more details about Postgres' hex encoding
SQL Standard
-- SQL actually defines binary literals as such -- (as implemented by DB2, Derby, H2, HSQLDB, Ingres, MySQL, SQLite): <binary string literal> ::= X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote> <hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f