On Fri, 9 Mar 2001, Mary Christie Generalao wrote:
..
> hi pluggers,
> I've seen several postgres-related posts before, so I
> presume there are some postgres users here. Can you
> possibly give me an idea how to store binary data in a
> postgres table? I found out postgres doesnt have the
> BLOB data type of oracle. Any hints? As this is
> off-topic, I'd appreciate responses sent privately.
PostgreSQL sucks for BLOB data -- there is a VARCHAR which functions
similarly to a CLOB in Oracle, but as of Pg 7.0 and down, there is a 32K
row limit *make a face* anyway, Great Bridge is releasing 7.1 is a few
weeks and allegedly it has mucho larger row support.
Even so, Pg stores each table as a file, +1 file per indexed column. That
means, your maximum table size is 2GB (due to 2GB file size limit on
Linux). Yes, 2.4 and glibc2.2 have Large File Summit support, but I don't
know if Pg supports it.
Finally, I have discovered that Pg has VERY sucky concurrency on
UPDATES. Yes, inserts are fast (faster than Oracle) and straight selects
are fast too. But do a bulk UPDATE.. it's about 2 orders of magnitude
slower than Oracle.
I don't want to sound like a spoiled-brat Oracle zealot: of all the
open-source databases I've used, Pg has the best feature set. But it still
falls short in several areas. I would use Pg if I needed adequate
concurrency, transactional commit, and fairly complex queries, but
couldn't afford Oracle. I would use MySQL if I really need BLOB support
but ACID be damned.
I would use Oracle for things that the open-source products can't do.
Christie, if I were you, unless you have a compelling reason to use a BLOB
(e.g. you want to index it, something Pg can't do either..) I would
suggest you store the binary data in the FILESYSTEM. Something like the
Oracle BFILE data type. It will save you some headaches.
Or, use MySQL. The latest MySQL can do full-text indexes of BLOBs (daw,
sabi ni NuSphere). But it doesn't support word stemming, so don't expect
InterMedia functionality just yet.. :)
---------------------------------------------------------------------
Orlando Andico <[EMAIL PROTECTED]> POTS Phone: +63 (2) 848-2893
Mosaic Communications, Inc. GSM Mobile: +63 (917) 531-5893
I'm not suffering from insanity -- I'm enjoying every minute of it!!
_
Philippine Linux Users Group. Web site and archives at http://plug.linux.org.ph
To leave: send "unsubscribe" in the body to [EMAIL PROTECTED]
To subscribe to the Linux Newbies' List: send "subscribe" in the body to
[EMAIL PROTECTED]
