BYTEA vs BLOB - Mailing list pgsql-sql

<div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida
Grande,sans-serif;font-size:13px"><div id="yui_3_16_0_1_1452584894329_20555">Try to migrate from Oracle to Postgres
(9.4.5)on Linux OS.</div><div id="yui_3_16_0_1_1452584894329_20555"><br /></div><div
id="yui_3_16_0_1_1452584894329_20555">Ihave some photos stored in a table, to make it simple, the current (Oracle)
tablelooks like:</div><div id="yui_3_16_0_1_1452584894329_20555"><br /></div><div class=""
id="yui_3_16_0_1_1452584894329_20613"style="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;"><b>test_tab</b> <spanid="yui_3_16_0_1_1452584894329_20620" style="line-height:
17.7272720336914px;">(photoBLOB)</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="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;">LOB ("photo")
storeas BASICFILE (tablespace "MYLOB" <strong class="" id="yui_3_16_0_1_1452584894329_20619" style="margin: 0px;
padding:0px; border: 0px; font-style: inherit; font-family: inherit; vertical-align: baseline;">disable storage in
row</strong>...</div><divclass="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617" style="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;"><br /></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;">That <spanclass="" id="yui_3_16_0_1_1452584894329_20660" style="font-family: inherit; font-style:
inherit;font-weight: 700; line-height: 17.7272720336914px;">disable storage in row </span><span class=""
id="yui_3_16_0_1_1452584894329_20691"style="font-family: inherit; font-style: inherit; line-height:
17.7272720336914px;">willonly allow a reference to be stored in the table while the actual BLOB data is stored outside
thetable, still inside the Oracle database, though (just NOT in the computer's file system).  </span><span class=""
id="yui_3_16_0_1_1452584894329_20654"style="line-height: 17.7272720336914px;">When issue the </span><strong class=""
id="yui_3_16_0_1_1452584894329_20656"style="line-height: 17.7272720336914px; margin: 0px; padding: 0px; border: 0px;
vertical-align:baseline;">delete from test_tab where id= 12345 </strong><span class="" style="line-height:
17.7272720336914px;margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">lateron</span><span class=""
id="yui_3_16_0_1_1452584894329_20658"style="line-height: 17.7272720336914px;">, the BLOB data will also get deleted,
evenif the BLOB value was stored out of the row at the first place.</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" id="yui_3_16_0_1_1452584894329_20768" style="line-height: 17.7272720336914px;">Now
ifI migrate the </span><span class="" id="yui_3_16_0_1_1452584894329_20806" style="line-height:
17.7272720336914px;">Oracle </span><spanclass="" id="yui_3_16_0_1_1452584894329_20808" style="line-height:
17.7272720336914px;">tableto Postgres and change the data type to <b id="yui_3_16_0_1_1452584894329_20906">BYTEA</b>,
howwill the photo file (</span><b class="" id="yui_3_16_0_1_1452584894329_20906" style="line-height:
17.7272720336914px;">BYTEA) </b><spanclass="" id="yui_3_16_0_1_1452584894329_20913" style="line-height:
17.7272720336914px;">bestored?</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="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;"><span class=""
style="line-height:17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" id="yui_3_16_0_1_1452584894329_20853" style="line-height: 17.7272720336914px;">1)
Thewhole photo data will be stored inside the table?</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;">Or</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" id="yui_3_16_0_1_1452584894329_20854" style="line-height: 17.7272720336914px;">2)
Onlythe reference to the data is stored inside the table, the data itself will be stored outside the table (but still
withinthe database) for efficiency purpose?</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="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;"><span class=""
style="line-height:17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" id="yui_3_16_0_1_1452584894329_20922" style="line-height:
17.7272720336914px;">Thanksto help.</span></div><div class="" dir="ltr" id="yui_3_16_0_1_1452584894329_20617"
style="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;"><span class=""
style="line-height:17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;">Eugene</span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div><div class="" dir="ltr"
id="yui_3_16_0_1_1452584894329_20617"style="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;"><spanclass="" style="line-height: 17.7272720336914px;"><br /></span></div></div>