Re: newbie question on database structure - Mailing list pgsql-general

From David Helgason
Subject Re: newbie question on database structure
Date
Msg-id 436163C2-4A5F-11D8-BD46-000A9566DA8A@uti.is
Whole thread Raw
In response to newbie question on database structure  ("JustSomeGuy" <nope@nottelling.com>)
List pgsql-general
On 15. jan 2004, at 18:51, JustSomeGuy wrote:

> I want to design a data base that can hold the following structure....
>
> struct {
>    unsigned short a;
>    unsigned short b;
>    unsigned int len;
>    unsigned char *data;
> } myObject;
>
> a and b describe the type of object, len is the number of bytes that
> are required to hold the object.
> and of course data points to the object....
>
> Of course the size of the objects is not a constant so this is where I
> get confused...
> How do I define a database to hold these in postgres?

You want a data-type which can hold variable amount of data.

create table myObject (
    a integer,
    b integer,
    len integer,
    data bytea
);

Now, I assume that your unsigned short is a 2-byte integer. However
postgresql doesn't have an unsigned short (afaik), but it has a signed
version. Unless you're going to be storing *a lot* of these records,
you'll probably be fine with just using a 4-byte integer. You might of
course consider dropping the unsigned, or doing your own conversion
(to/from signed).

The bytea (byte-array) type will store up to 1 or 2 gigabyte of data
(never remember which one). Only caveat is that when you are inserting
you may have to escape the null character, backslash and single-quote.
See the docs for how to
(http://www.postgresql.org/docs/current/static/datatype-binary.html).

If you are using libpq (the C interface), it has functions that will
help you... check the docs for libpq for that too.

If you are actually going to store multi-megabyte data buffers in
there, there are considerations of memory allocation (which may get
pretty extreme if you try to transfer huge buffers at once). Consider
using the Large Object interface instead.


Hope this helps.

David Helgason
Over the Edge Entertainments



pgsql-general by date:

Previous
From: Erwan Arzur
Date:
Subject: Re: Very long time to commit or close connections
Next
From: Thierry Missimilly
Date:
Subject: Re: Pass data of database Oracle to PostgreSQL