pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnderstanding Binary Data Type
3+ messages / 3 participants
[nested] [flat]
* Understanding Binary Data Type
@ 2012-05-22 17:13 Carlos Mennens <carlos.mennens@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Carlos Mennens @ 2012-05-22 17:13 UTC (permalink / raw)
To: pgsql-sql
Hello everyone! I wanted to ask the list a question about the 'bytea'
data type & how I can picture this in my head. I've been reading SQL
for about a few months now and since then, I've only been working with
textual data. Basically I'm familiar with storing text and numerical
characters into tables but my friend told me that databases can hold
much more than just ASCI text. In so I've read up on some pages that
describe the bytea data type:
http://en.wikipedia.org/wiki/Binary_large_object
http://www.postgresql.org/docs/9.1/static/datatype-binary.html
So my question is can and in fact does PostgreSQL and most other RDBMS
have the ability to store large binary files like photos, music, etc
etc into an actual table? I'm guessing the data is dumped into the
table but rather linked or parsed through the file system store path
into the database itself, right? I would just like to know in a basic
round about way how databases store and handle large files like .jpg
or .png files & regardless how relative this term is, how common is it
to use these files or 'bytea' data in tables?
Thanks for any info!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Understanding Binary Data Type
@ 2012-05-24 06:09 Jasen Betts <jasen@xnet.co.nz>
parent: Carlos Mennens <carlos.mennens@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jasen Betts @ 2012-05-24 06:09 UTC (permalink / raw)
To: pgsql-sql
On 2012-05-22, Carlos Mennens <carlos.mennens@gmail.com> wrote:
> Hello everyone! I wanted to ask the list a question about the 'bytea'
> data type & how I can picture this in my head. I've been reading SQL
> for about a few months now and since then, I've only been working with
> textual data. Basically I'm familiar with storing text and numerical
> characters into tables but my friend told me that databases can hold
> much more than just ASCI text. In so I've read up on some pages that
> describe the bytea data type:
>
> http://en.wikipedia.org/wiki/Binary_large_object
>
> http://www.postgresql.org/docs/9.1/static/datatype-binary.html
>
> So my question is can and in fact does PostgreSQL and most other RDBMS
> have the ability to store large binary files like photos, music, etc
> etc into an actual table? I'm guessing the data is dumped into the
> table but rather linked or parsed through the file system store path
> into the database itself, right? I would just like to know in a basic
> round about way how databases store and handle large files like .jpg
> or .png files & regardless how relative this term is, how common is it
> to use these files or 'bytea' data in tables?
>
> Thanks for any info!
postgres has a maximum logical row size just under 2GiB so there's plenty of
room there for photos etc (DVD images might not fit).
postgres uses a strategy called TOAST to move "wide" values out of the
file that holds the main table.
implementation detals:
http://www.postgresql.org/docs/9.1/static/storage-toast.html
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Understanding Binary Data Type
@ 2012-05-24 16:36 F. BROUARD / SQLpro <sqlpro@club-internet.fr>
parent: Carlos Mennens <carlos.mennens@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: F. BROUARD / SQLpro @ 2012-05-24 16:36 UTC (permalink / raw)
To: pgsql-sql
Hi
Le 22/05/2012 19:13, Carlos Mennens a écrit :
> Hello everyone! I wanted to ask the list a question about the 'bytea'
> data type& how I can picture this in my head. I've been reading SQL
> for about a few months now and since then, I've only been working with
> textual data. Basically I'm familiar with storing text and numerical
> characters into tables but my friend told me that databases can hold
> much more than just ASCI text. In so I've read up on some pages that
> describe the bytea data type:
>
> http://en.wikipedia.org/wiki/Binary_large_object
>
> http://www.postgresql.org/docs/9.1/static/datatype-binary.html
>
> So my question is can and in fact does PostgreSQL and most other RDBMS
> have the ability to store large binary files like photos, music, etc
> etc into an actual table? I'm guessing the data is dumped into the
> table but rather linked or parsed through the file system store path
> into the database itself, right? I would just like to know in a basic
> round about way how databases store and handle large files like .jpg
> or .png files& regardless how relative this term is, how common is it
> to use these files or 'bytea' data in tables?
Actually SQL standard offer the ability to store large datafile directly
on the filesystem, but under the control of the RDBMS (the OS cannot
read, write or remove the file directly). This concept is based on the
DATALINK SQL type.
The main advantage is that the file stay exactly as a file and can be
transactionned and backuped like all other dataobjects of the database.
Some RDBMS like IBM DB2 or MS SQL Server does it (For SQL Server it is
called FILESTREAM due to some main differences, but the concept is the
same).
Actually PG does not offer this feature.
A +
>
> Thanks for any info!
>
--
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-05-24 16:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-05-22 17:13 Understanding Binary Data Type Carlos Mennens <carlos.mennens@gmail.com>
2012-05-24 06:09 ` Jasen Betts <jasen@xnet.co.nz>
2012-05-24 16:36 ` F. BROUARD / SQLpro <sqlpro@club-internet.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox