Hello all,

I have to store 2 GB text in the database. Previously I was using Oracle
database with CLOB data type.

Could anyone please tell me how can I do it in Postgresql 8.0 or later?

Search Discussions

  • Craig Ringer at Jan 17, 2010 at 6:09 am

    On 17/01/2010 1:51 PM, AI Rumman wrote:
    Hello all,
    I have to store 2 GB text in the database. Previously I was using Oracle
    database with CLOB data type.
    Do you mean individual text objects that are 2GB or larger each?

    Or do you mean you have a bunch of smaller (how big?) text objects that
    add up to 2GB or so?

    Assuming you mean individual objects >= 2GB, you'll have to use
    PostgreSQL BLOBs or use external files.

    What do you need to do with this text? What operations are you
    performing on it? Full-text search?
    Could anyone please tell me how can I do it in Postgresql 8.0 or later?
    Do you need to actually support 8.0? For a new project I'd favour
    starting with 8.3 or 8.4 .

    --
    Craig Ringer
  • AI Rumman at Jan 17, 2010 at 6:13 am
    I have an individual text string of 2 GB.
    I may use Postgresql 8.4 also. But what data type should I use to store this
    column value of 2GB?

    On Sun, Jan 17, 2010 at 12:08 PM, Craig Ringer
    wrote:
    On 17/01/2010 1:51 PM, AI Rumman wrote:

    Hello all,
    I have to store 2 GB text in the database. Previously I was using Oracle
    database with CLOB data type.
    Do you mean individual text objects that are 2GB or larger each?

    Or do you mean you have a bunch of smaller (how big?) text objects that add
    up to 2GB or so?

    Assuming you mean individual objects >= 2GB, you'll have to use PostgreSQL
    BLOBs or use external files.

    What do you need to do with this text? What operations are you performing
    on it? Full-text search?


    Could anyone please tell me how can I do it in Postgresql 8.0 or later?
    Do you need to actually support 8.0? For a new project I'd favour starting
    with 8.3 or 8.4 .

    --
    Craig Ringer
  • Craig Ringer at Jan 17, 2010 at 6:28 am

    On 17/01/2010 2:13 PM, AI Rumman wrote:
    I have an individual text string of 2 GB.
    OK. And what sorts of things do you do with that text in your current
    database? What queries do you do on it?

    Do you just need to read it? Update it? Update only selected parts of
    it? Search for text strings based on keywords within the text? etc.

    What *is* the text? And is it really text, in the sense of
    human-readable text, or is it just binary data that happens to all be
    printable characters?


    In any case, for objects > 2GB you don't really have much choice except
    to use PostgreSQL's large object features. See:

    http://www.postgresql.org/docs/current/static/largeobjects.html

    These aren't really stored directly in the row. Rather, you store a `lo'
    object in the row that is really just the ID of the large object. You
    can then access the large object using the lo_ functions.

    You are likely to find the following contributed module useful:

    http://www.postgresql.org/docs/current/static/lo.html

    as the `lo' type it defines will give you behavior somewhat more similar
    to what you're used to from CLOB. It'll let you (appear to) store the
    data "in" the row.

    --
    Craig Ringer
  • John R Pierce at Jan 17, 2010 at 6:41 am

    Craig Ringer wrote:
    In any case, for objects > 2GB you don't really have much choice
    except to use PostgreSQL's large object features. See:
    I thought LO were limited to <= 2GB ? TOAST can handle up to 1GB. >
    2GB and you'll need to break it up into several LOs.
  • John R Pierce at Jan 17, 2010 at 6:34 am

    AI Rumman wrote:
    I have an individual text string of 2 GB.
    I may use Postgresql 8.4 also. But what data type should I use to
    store this column value of 2GB?
    GB data objects ni a database is going to make backups, maintenance, etc
    a real pain

    normal data in postgres can not be over 1GB, so you will need to use the
    Larg OBJect facility, where you store an object ID in your actual table,
    then store the data via a special Large Object api,
    http://www.postgresql.org/docs/current/static/lo-interfaces.html which
    can handle up to 2GB max.

    quite frankly, i'd as soon store stuff that large as files and and store
    their path in the database.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 17, '10 at 5:51a
activeJan 17, '10 at 6:41a
posts6
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase