Hi, I'd like to get more insight of how to handle (binary) Large Object Types (LOB/BLOB/CLOB) in Postgres and I think we should clarify the situation since to me there are some inconsistencies at least in the docs (or my understanding). I could try to summarize e.g. in the Postgres Wiki. In current docs the "BLOB data type" is mentioned as unsupported (chapter "D.2. Unsupported Features", item "T041-01, Basic object support" [1]) But we have 1. type bytea, 2. oid/pg_largeobjects (at least in JDBC) and now 3. type DATALINK [5]. => Why not taking one of those as BLOB data type? The Wiki page about ORACLE and BLOBs [2] seems to me little bit outdated. It says "Binary large object support in Postgres is very poor and unsuitable for use in a 24/7 environment, because you can't dump them with pg_dump." => Still true? Do we want to dump them anyway? The JDBC docs [3] says "PostgreSQL provides two distinct ways to store binary data. Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table." => I assume the approach with a separate table (pg_largeobjects) is also feasible in plain PostgreSQL schema/usage without JDBC involed? => Should'nt be there some reference in the JDBC docs to the solution with module lo [3]? => Should one disable TOAST compression here by doing an "ALTER column SET STORAGE EXTERNAL"? There's the new data type DATALINK [5]. A DATALINK allows to reference files from tables. Thanks to this, big files (like images) can be externally saved while maintaining support of access control and integrity mechanisms. Currently I only find something in the wiki [5] but can't find a reference to DATALINK in the PostgreSQL docs (except as reserved keyword). => Is this an omission in the docs? So to make an initial summary, in PostgreSQL AFAIK there are at least three common ways to manage Binary Large Objects (LOB/BLOB): 1. Column of type BYTEA which stores the LOB within the table (respectively using TOAST). 2. Column of type OID in the user table and an internal separate table which separates the LOB away (called pg_largeobjects with a foreign key to oid and bytea ) Hint to module 'lo' [4]. 3. DATALINK type which maintains a link to a specific file in external storage (Example: "% CREATE TABLE mydata (myid Integer, myimage DATALINK (40) FILE LINK CONTROL INTEGRITY ALL);" ). I'm interested in any comments. Yours, Stefan [1] http://www.postgresql.org/docs/current/static/unsupported-features-sql-standard.html [2] http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion#BLOBs [3] http://jdbc.postgresql.org/documentation/head/binary-data.html [4] http://www.postgresql.org/docs/current/interactive/lo.html [5] http://wiki.postgresql.org/wiki/DATALINK -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
