Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1aKqe5-0001id-Tm for pgsql-sql@arkaria.postgresql.org; Sun, 17 Jan 2016 16:57:42 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1aKqe5-0006wi-Ai for pgsql-sql@arkaria.postgresql.org; Sun, 17 Jan 2016 16:57:41 +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 1aKqd6-0005gd-Vo for pgsql-sql@postgresql.org; Sun, 17 Jan 2016 16:56:41 +0000 Received: from nm11-vm1.bullet.mail.bf1.yahoo.com ([98.139.213.152]) by magus.postgresql.org with esmtps (TLS1.0:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1aKqd2-0007fi-DK for pgsql-sql@postgresql.org; Sun, 17 Jan 2016 16:56:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s2048; t=1453049794; bh=jFC5GBtVXuoAzh6lz5F747rC/bl1od+GE/FIOH9ZOLY=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=JTwHJVwcJMq5c6ecXJXwIjZJ4qsaBnICL+cOU//JL9oUOHNGw4a3+bxwpU3t5zr1YQreiCfHk+221Nl6kjHwdNKP2VFexrI24GYoLDKeYY6ZltfGb1eshXx4dlewbSLzJMddBmxJwamb9y5PpKBzXH0nUvYMrs3awo0NmN1vExeOOfTMetXWK1gzvYdqya5fAYH+oIek74mungTUw6PBmxi27IY2+zC87eIB0uyqoT+9wPVuh9Zz/IScrsLrpHZPCapOExw+tZScZwmUVkw1ZBJx1q1arW6HoDaF0qkejrlWJSTiet9L86Ev55CiDO+TqJd58a1SXjH635xRqS3HcA== Received: from [98.139.170.181] by nm11.bullet.mail.bf1.yahoo.com with NNFMP; 17 Jan 2016 16:56:34 -0000 Received: from [98.139.212.238] by tm24.bullet.mail.bf1.yahoo.com with NNFMP; 17 Jan 2016 16:56:34 -0000 Received: from [127.0.0.1] by omp1047.mail.bf1.yahoo.com with NNFMP; 17 Jan 2016 16:56:34 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 18210.84539.bm@omp1047.mail.bf1.yahoo.com X-YMail-OSG: Fk.cDp0VM1nZnpcLMt4sCt.B_wDKYh9cA6Rttc0iI1r3eBtrQAySNiMg2JsaSWV pmcn1GhJoHrYSYGoTh6bTjxWBlChqw9GwwmT0w9TWbLOGp3P0fgJl42zyKbEmmO5Ds3nKdNk.t4f ICoa3GXvwLBIraS8QUxL2Vv6G1QTfMsZiT36o6AdIOMBxq0R5xikCy1MjYraDAIfMmJ9MEm3u8CT Yjpc6hjjdoAaXwIRdQR1z8J9Mv4Qw7avby3ruswRZeiqqiUfOuXc_618rc3ghOuZGpnYswxiliRK AqzAbvki37j8u6wOtV0YEDa6oneLpYzHwSqo8.r2rvhxJblGvTZvZERnwfnh8str.G8Eab_pA93U .t20PZKI8erMGb2STggdAJQAfPDZCoaklAvCKVlCvjUtLZ0nfLslDXvY_dy.Q0yqeqBEYlWAgu.L 6mhBnB3s3bASbnNDP10QmPm3donz7I21ni6sEM5A3MetrvjUzQ0OovSqHoOCTmeVSQH_fpMTrTT7 qOZeJkh6Vff.c6XQICrrt.PtMnKfznGxZUJIvZSOlTvgwR9UtCjCHAmMe2pX_O4U- Received: by 76.13.26.107; Sun, 17 Jan 2016 16:56:33 +0000 Date: Sun, 17 Jan 2016 16:56:33 +0000 (UTC) From: Eugene Yin Reply-To: Eugene Yin To: Andreas Joseph Krogh Cc: Postgres List Message-ID: <1487961919.5778438.1453049793112.JavaMail.yahoo@mail.yahoo.com> In-Reply-To: References: Subject: Re: BYTEA vs BLOB MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_5778437_724600590.1453049793112" Content-Length: 13489 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_5778437_724600590.1453049793112 Content-Type: multipart/alternative; boundary="----=_Part_5778436_161722806.1453049793101" ------=_Part_5778436_161722806.1453049793101 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable lfd :=3D lo_open(loid,131072); Why use the file size 131072, instead of other number?Are there other optio= ns? I mean, under what circumstance, use 131072, or use other size? Thanks Eugene=20 On Sunday, January 17, 2016 7:02 AM, Andreas Joseph Krogh wrote: =20 P=C3=A5 l=C3=B8rdag 16. januar 2016 kl. 17:08:05, skrev Eugene Yin : When use Ora2Pg to migrate the Oracle to Pg, the BLOB data type in Oracle w= ill supposedly be converted into BYTEA in Pg.=C2=A0 Is this achievable?=C2= =A0=C2=A0=C2=A0If so, after the data become BYTEA, can I further convert th= e BYTEA into OID data type, and how to? =C2=A0Here's how I converted a BYTEA-column to OID:=C2=A0The table origo_fi= le_rawdata contains a column named 'data' of type BYTEA. The trick is to ad= d a new column, 'lo_data' of type=3DOID, populate it, then drop the old col= umn and rename 'lo_data' to 'data':begin; alter table origo_file_rawdata add column lo_data oid; do $$ declare loid oid; lfd integer; lsize integer; d origo_file_rawdata; begin for d IN (select * from origo_file_rawdata) loop loid :=3D lo_create(0); lfd :=3D lo_open(loid,131072); lsize :=3D lowrite(lfd, d.data); perform lo_close(lfd); update origo_file_rawdata set lo_data =3D loid where entity_id =3D d.en= tity_id; end loop; end; $$; alter table origo_file_rawdata alter column lo_data set not null; alter table origo_file_rawdata drop column data; alter table origo_file_rawdata rename lo_data to data; commit; =C2=A0Hope this helps.=C2=A0--Andreas Joseph KroghCTO / Partner - Visena AS= Mobile: +47 909 56 963andreas@visena.comwww.visena.com=C2=A0 =20=20= ------=_Part_5778436_161722806.1453049793101 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 
lfd :=
=3D lo_open(loid,131072);

Why use the f=
ile size 131072, instea=
d of other number?
Are there other options?  I mean, under what circumstance, use 131072, or use other size?
= 

Thanks

Eugene


On Sunday, January 17, 2016 7:02 = AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
=

P=C3=A5 l=C3=B8rdag 16. januar 2016 kl. 17:08:05, skrev Eugene Yin <<= a rel=3D"nofollow" shape=3D"rect" ymailto=3D"mailto:eugeneymail@ymail.com" = target=3D"_blank" href=3D"mailto:eugeneymail@ymail.com">eugeneymail@ymail.c= om>:
When = use Ora2Pg to migrate the Oracle to Pg, the BLOB data type in Oracle will s= upposedly be converted into BYTEA in Pg.  Is this achievable? &nb= sp;
 = ;
If so= , after the data become BYTEA, can I further convert the BYTEA into OID dat= a type, and how to?
 
Here's how I converted a BYTEA-column to OID:
 
The table origo_file_rawdata co= ntains a column named 'data' of type BYTEA. The trick is to add a new colum= n, 'lo_data' of type=3DOID, populate it, then drop the old column and renam= e 'lo_data' to 'data':
begin;

alter table o=
rigo_file_rawdata ad=
d column lo_data oid;

do $$
declare
    loid oid;
    lfd integ=
er;
    lsize int=
eger;
    d origo_f=
ile_rawdata;
begin
    for d IN =
(select * from origo_file_rawdata) loop
        loid =
:=3D lo_create(0);
        lfd :=
=3D lo_open(loid,131072);
        lsize=
 :=3D lowrite(lfd, d.data);
        perfo=
rm lo_close(lfd);
    update or=
igo_file_rawdata set lo_data =3D loid where entity_id =3D d.entity_id;
    end loop;
end;
$$;

alter table o=
rigo_file_rawdata al=
ter column lo_data set not null;
alter table o=
rigo_file_rawdata dr=
op column data;
alter table o=
rigo_file_rawdata re=
name lo_data =
to data;
commit;
 
Hope this helps.
 
--
Andr= eas Joseph Krogh
CTO / Partne= r - Visena AS
Mobile: +47 = 909 56 963
3D""
 


= ------=_Part_5778436_161722806.1453049793101-- ------=_Part_5778437_724600590.1453049793112 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgI fAhkiAAABzBJREFUaEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrA UgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/HADs43q6kROeJNbOYgQACIAgCWJKn g4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfdt5mBx1NhqSEo 8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X9 49OnaS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8Wf Ci80Xgf9vVlrhg9yRONe/f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl 94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd8M58lxPCvvD6aihfvexbEQoP YO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe+JXkyGgW INq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZ lN16xygIgl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1v CvvqKXIW8mZYW+F3Tg4r8HvZkQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6 beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMHQPwReg2wW8umEe/+L8T/ LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aYhdiC nRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZ TDUEj8u6IVhuEhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQa v4vrmp7cpMp2qbHnBxWJbisbho2QXI6C1sIHDUFhH4Hij4UbIev66eANeiwb kA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8UBIYhBd6oSUgYMijO kecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdS PAvxHYin6YmN9YBXQmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvyko BEc9CoCGWJisOLOGoCORs1FvIMbYA8z3kwM59oemy6LlWrLxFLmWgiQAfEGd 8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNjv/gANOdQxPfc iP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcP c4rVNjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5 pqCuK01k40kSNtnKiKIlUfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/b db9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gNJ/aYDhk+hhjExwaPcz5PObA5 Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82Nr4LeS2j 1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3 ZEJXi/GRa6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqp HLu9jBrhUuhNwVGbpyZrzrl0HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1 t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ppTsuRCbvUD9fwquksG5 GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQGoX0 ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9k TXoluzeS0fB99CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOY Nt9pwxSZtYDsX/GE3xTkrXgwAsXmfud08FiTeC+m2/o7ppo+PTS/NBK5ARpD G5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofssxBA1gj5NXGvfJu/ Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsY KyZxvxZSZBAgjhIzYDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+ IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuuhuC4pCGGx+YD2fcc5tfIAA0h/Et4 /jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3CHU2mTMwZ+B/ y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_5778437_724600590.1453049793112 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ------=_Part_5778437_724600590.1453049793112--