agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedBYTEA vs BLOB
8+ messages / 3 participants
[nested] [flat]
* BYTEA vs BLOB
@ 2016-01-13 01:45 Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Eugene Yin @ 2016-01-13 01:45 UTC (permalink / raw)
To: pgsql-sql
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 (Oracle) table looks like:
test_tab (photo BLOB)LOB ("photo") store as BASICFILE (tablespace "MYLOB" disable storage in row...
That disable storage in row will only allow a reference 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 computer's file system). When issue the delete from test_tab where id= 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 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?
Or
2) Only the reference to the data is stored inside the table, the data itself will be stored outside the table (but still within the database) for efficiency purpose?
Thanks to help.
Eugene
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BYTEA vs BLOB
@ 2016-01-14 16:00 John DeSoi <desoi@pgedit.com>
parent: Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 8+ messages in thread
From: John DeSoi @ 2016-01-14 16:00 UTC (permalink / raw)
To: Eugene Yin <eugeneymail@ymail.com>; +Cc: pgsql-sql
> On Jan 12, 2016, at 7:45 PM, Eugene Yin <eugeneymail@ymail.com> wrote:
>
> Now if I migrate 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?
>
> Or
>
> 2) Only the reference to the data is stored inside the table, the data itself will be stored outside the table (but still within the database) for efficiency purpose?
>
This is a good summary of your options:
https://www.microolap.com/products/connectivity/postgresdac/help/tipsandtricks_byteavsoid.htm
John DeSoi, Ph.D.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BYTEA vs BLOB
@ 2016-01-16 16:08 Eugene Yin <eugeneymail@ymail.com>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 1 reply; 8+ messages in thread
From: Eugene Yin @ 2016-01-16 16:08 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; pgsql-sql
When use Ora2Pg to migrate the Oracle to Pg, the BLOB data type in Oracle will supposedly be converted into BYTEA in Pg. Is this achievable?
If so, after the data become BYTEA, can I further convert the BYTEA into OID data type, and how to?
Thanks
Eugene
On Thursday, January 14, 2016 8:05 AM, John DeSoi <desoi@pgedit.com> wrote:
> On Jan 12, 2016, at 7:45 PM, Eugene Yin <eugeneymail@ymail.com> wrote:
>
> Now if I migrate 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?
>
> Or
>
> 2) Only the reference to the data is stored inside the table, the data itself will be stored outside the table (but still within the database) for efficiency purpose?
>
This is a good summary of your options:
https://www.microolap.com/products/connectivity/postgresdac/help/tipsandtricks_byteavsoid.htm
John DeSoi, Ph.D.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BYTEA vs BLOB
@ 2016-01-17 15:01 Andreas Joseph Krogh <andreas@visena.com>
parent: Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-17 15:01 UTC (permalink / raw)
To: pgsql-sql
På lørdag 16. januar 2016 kl. 17:08:05, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
When use Ora2Pg to migrate the Oracle to Pg, the BLOB data type in Oracle will
supposedly be converted into BYTEA in Pg. Is this achievable?
If so, after the data become BYTEA, can I further convert the BYTEA into OID
data type, and how to?
Here's how I converted a BYTEA-column to OID:
The table origo_file_rawdata contains a column named 'data' of type BYTEA. The
trick is to add a new column, 'lo_data' of type=OID, populate it, then drop the
old column 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 := lo_create(0); lfd :=
lo_open(loid,131072); lsize := lowrite(lfd, d.data); perform lo_close(lfd);
update origo_file_rawdata set lo_data = loid where entity_id = d.entity_id; end
loop;end; $$; alter table origo_file_rawdata alter column lo_data set not null;
alter tableorigo_file_rawdata drop column data; alter table origo_file_rawdata
renamelo_data to data; commit;
Hope this helps.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BYTEA vs BLOB
@ 2016-01-17 16:56 Eugene Yin <eugeneymail@ymail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 8+ messages in thread
From: Eugene Yin @ 2016-01-17 16:56 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
lfd := lo_open(loid,131072);
Why use the file size 131072, instead 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å lørdag 16. januar 2016 kl. 17:08:05, skrev Eugene Yin <eugeneymail@ymail.com>:
When use Ora2Pg to migrate the Oracle to Pg, the BLOB data type in Oracle will supposedly be converted into BYTEA in Pg. Is this achievable? If so, after the data become BYTEA, can I further convert the BYTEA into OID data type, and how to?
Here's how I converted a BYTEA-column to OID: The table origo_file_rawdata contains a column named 'data' of type BYTEA. The trick is to add a new column, 'lo_data' of type=OID, populate it, then drop the old column 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 := lo_create(0);
lfd := lo_open(loid,131072);
lsize := lowrite(lfd, d.data);
perform lo_close(lfd);
update origo_file_rawdata set lo_data = loid where entity_id = d.entity_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;
Hope this helps. --Andreas Joseph KroghCTO / Partner - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com
=
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BYTEA vs BLOB
@ 2016-01-17 17:01 Andreas Joseph Krogh <andreas@visena.com>
parent: Eugene Yin <eugeneymail@ymail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-17 17:01 UTC (permalink / raw)
To: pgsql-sql
På søndag 17. januar 2016 kl. 17:56:33, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
lfd := lo_open(loid,131072); Why use the file size 131072, instead of other
number? Are there other options? I mean, under what circumstance, use 131072,
or use other size? Thanks Eugene
No particular reason.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BYTEA vs BLOB
@ 2016-01-17 17:37 Eugene Yin <eugeneymail@ymail.com>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 8+ messages in thread
From: Eugene Yin @ 2016-01-17 17:37 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; +Cc: pgsql-sql
What does 131072 mean? Why not use a bigger or smaller number?
On Sunday, January 17, 2016 9:02 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På søndag 17. januar 2016 kl. 17:56:33, skrev Eugene Yin <eugeneymail@ymail.com>:
lfd := lo_open(loid,131072); Why use the file size 131072, instead of other number?Are there other options? I mean, under what circumstance, use 131072, or use other size? Thanks Eugene
No particular reason. --Andreas Joseph KroghCTO / Partner - Visena ASMobile: +47 909 56 963andreas@visena.comwww.visena.com
=
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BYTEA vs BLOB
@ 2016-01-17 17:42 Andreas Joseph Krogh <andreas@visena.com>
parent: Eugene Yin <eugeneymail@ymail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Andreas Joseph Krogh @ 2016-01-17 17:42 UTC (permalink / raw)
To: pgsql-sql
På søndag 17. januar 2016 kl. 18:37:34, skrev Eugene Yin <eugeneymail@ymail.com
<mailto:eugeneymail@ymail.com>>:
W <mailto:pgsql-sql@postgresql.org>hat does 131072 mean? Why not use a bigger
or smaller number?
I don't know, found it in an example and it works.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2016-01-17 17:42 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-01-13 01:45 BYTEA vs BLOB Eugene Yin <eugeneymail@ymail.com>
2016-01-14 16:00 ` John DeSoi <desoi@pgedit.com>
2016-01-16 16:08 ` Eugene Yin <eugeneymail@ymail.com>
2016-01-17 15:01 ` Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 16:56 ` Eugene Yin <eugeneymail@ymail.com>
2016-01-17 17:01 ` Andreas Joseph Krogh <andreas@visena.com>
2016-01-17 17:37 ` Eugene Yin <eugeneymail@ymail.com>
2016-01-17 17:42 ` Andreas Joseph Krogh <andreas@visena.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox