Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aJAW9-0004KI-JO for pgsql-sql@arkaria.postgresql.org; Wed, 13 Jan 2016 01:46:34 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aJAW9-0000D6-5E for pgsql-sql@arkaria.postgresql.org; Wed, 13 Jan 2016 01:46:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1aJAV9-0007Ce-Fs for pgsql-sql@postgresql.org; Wed, 13 Jan 2016 01:45:31 +0000 Received: from nm27-vm1.bullet.mail.bf1.yahoo.com ([98.139.213.148]) by magus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aJAUp-0007dO-74 for pgsql-sql@postgresql.org; Wed, 13 Jan 2016 01:45:30 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s2048; t=1452649508; bh=ZAPbBTuwH3k775mnak3O48yanKfeBcO+o2lecngULx8=; h=Date:From:Reply-To:To:Subject:References:From:Subject; b=RrTq/1BzFt1Qo0qUyUiE3YyG+YVQbXqtb5mQ1jpEq9fi4sSnTF7Pc1GnFlu9c0O8Y1E9kMw6RzjGp20HEp5IK7pKQMei+8yOsrTlQBBbTDj5KYPCrYlU5sKJNkI/88RNnwTMHA9IXlLVpvlX+QCsR1Gif3v9zPRiByQPCgF0DqIFxfuJ/OdcBoTKwVMQtIV4p0TQh9rVQl8nQzXM9NZLdlCaT4Quupa84j5pWbDkkzMjpK1i83N4MdjWt2q7R6IG9j9pmJEizslT0WBh3SxZnvQYAO1Fm/QBIvxZS1+UTPXoSh8eFB2CkrHLFRndHuc2mRK+bvk9xv1Ex1r9ZPwGCw== Received: from [66.196.81.174] by nm27.bullet.mail.bf1.yahoo.com with NNFMP; 13 Jan 2016 01:45:08 -0000 Received: from [98.139.212.243] by tm20.bullet.mail.bf1.yahoo.com with NNFMP; 13 Jan 2016 01:45:08 -0000 Received: from [127.0.0.1] by omp1052.mail.bf1.yahoo.com with NNFMP; 13 Jan 2016 01:45:08 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 722778.66909.bm@omp1052.mail.bf1.yahoo.com X-YMail-OSG: LHKPiy8VM1kPKvTBj7.JnksRJSWh5roj0qPuBczb34FTFvofL9QrcDSszOEm3pn l3Ff6qqsQCHkLled3VR8hdJd3zj1EHxpErCKTGjLgEY2zg_UxRkd3y_eCSMRFHDgnF3wWuBgnbnE jVbOic9z8l61e7udQnu0.xmmQFq34m8FPvj0Vt7mKgpcXLIukMn4ipyGeTLPlnvySFJBW.BzQsQS ON3BQnXIboKY4_7ef3KuSExXy1x3JYu.vkyE.yma5hh19NaLNKZZEbsWrMKkSgZZTCDFDs7_TiWc OgriAlIPuhkyhf_9fcQJXyCjD_1IjXxywNK.Mdzg5PZJJitDWZ9R2kHZEBznlc7GBX5C_LV3PT8P cbK9GC6fXODd88MmKTCNduakXWnTEWRFM86rLJjbYpUb.y3Fj2z2EQXUBaxdv4nP13AlBwqppjKa PJMhRoi88rqwc53Hs82d6JV3htpYXuw9dNzyYPbtlF9it5JpoprCixS2U42zoLPNraG7kc5VTvm7 yAyGvxVew_bSIcCONQdHUTZuNuvs- Received: by 66.196.81.118; Wed, 13 Jan 2016 01:45:08 +0000 Date: Wed, 13 Jan 2016 01:45:06 +0000 (UTC) From: Eugene Yin Reply-To: Eugene Yin To: "pgsql-sql@postgresql.org" Message-ID: <1001118704.4351476.1452649506915.JavaMail.yahoo@mail.yahoo.com> Subject: BYTEA vs BLOB MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_4351475_948742195.1452649506910" References: <1001118704.4351476.1452649506915.JavaMail.yahoo.ref@mail.yahoo.com> Content-Length: 12266 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org ------=_Part_4351475_948742195.1452649506910 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Try to migrate from Oracle to Postgres (9.4.5) on Linux OS. I have some photos stored in a table, to make it simple, the current (Oracl= e) table looks like: test_tab=C2=A0(photo BLOB)LOB ("photo") store as BASICFILE (tablespace "MYL= OB"=C2=A0disable storage in row... That=C2=A0disable storage in row will only allow a reference to be stored i= n the table while the actual BLOB data is stored outside the table, still i= nside the Oracle database, though (just NOT in the computer's file system).= =C2=A0When issue the=C2=A0delete from test_tab where id=3D 12345 lateron, = the BLOB data will also get deleted, even if the BLOB value was stored out = of the row at the first place. Now if I migrate the=C2=A0Oracle=C2=A0table to Postgres and change the data= type to BYTEA, how will the photo file (BYTEA)=C2=A0be stored? 1) The whole photo data will be stored inside the table? Or 2) Only the reference to the data is stored inside the table, the data itse= lf will be stored outside the table (but still within the database) for eff= iciency purpose? Thanks to help. Eugene ------=_Part_4351475_948742195.1452649506910 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Try= to migrate from Oracle to Postgres (9.4.5) on Linux OS.

I have some photos stored in a table, to make it simple, the cu= rrent (Oracle) table looks like:

te= st_tab (photo BLOB)
LOB ("photo") store as BASICFIL= E (tablespace "MYLOB" disable storage in r= ow...

That disable storage in row will only allow a refer= ence to be stored in the table while the actual BLOB data is stored outside= the table, still inside the Oracle database, though (just NOT in the compu= ter's file system).  When issue the&nb= sp;delete from test_tab where id=3D 12345 lateron, the BLOB data will also get deleted, even if the BLOB va= lue was stored out of the row at the first place.


Now if I migrat= e the Oracle table to Postgres and change the data type to BYTEA, how will the photo file (BYTEA) be stored?

1) The whole photo data will be stored inside the table?

<= div style=3D"margin-top: 0px; margin-bottom: 0px; border: 0px; font-family:= 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-= align: baseline; color: rgb(61, 61, 61); line-height: 17.7272720336914px;" = dir=3D"ltr" id=3D"yui_3_16_0_1_1452584894329_20617" class=3D"">Or

2) On= ly the reference to the data is stored inside the table, the data itself wi= ll be stored outside the table (but still within the database) for efficien= cy purpose?


<= div style=3D"margin-top: 0px; margin-bottom: 0px; border: 0px; font-family:= 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; vertical-= align: baseline; color: rgb(61, 61, 61); line-height: 17.7272720336914px;" = dir=3D"ltr" id=3D"yui_3_16_0_1_1452584894329_20617" class=3D"">Thanks to help.

Eugene
=

------=_Part_4351475_948742195.1452649506910--