Thread: Fw: OID

If you need a unique, arbitrary ID for each record in your tables -- which is almost always a good thing to have, the
OIDdoes the job automatically. It has the additional benefit of being unique in the entire database. The only problem I
haveis if records are imported from other databases, in which case the OID is changed or at risk of being duplicated.
Ifyou assign your own unique ID you can ensure that merged records preserve their ID. I advise using the unique ID only
internallyand never having a user enter or reference it.<br /><br /> Jodi Kanter wrote:<br /><blockquote
cite="mid01e701c24563$00413ef0$de138f80@virginia.edu"type="cite"><style></style><div><font face="Times New Roman">Does
anyonehave a comment on the email below? Is there a general consensus on whether or not to use OIDs with version
7.2?</font></div><div><fontface="Times New Roman">Thanks</font></div><div><font face="Times New
Roman">Jodi</font></div><div> </div><divstyle="font-family: arial; font-style: normal; font-variant: normal;
font-weight:normal; font-size: 10pt; line-height: normal; font-stretch: normal; font-size-adjust: none;">----- Original
Message----- <div style="background: rgb(228,228,228) none repeat scroll 0%;"><b>From:</b> <a
href="mailto:jkanter@virginia.edu"title="jkanter@virginia.edu">Jodi Kanter</a></div><div><b>To:</b> <a
href="mailto:pgsql-admin@postgresql.org"title="pgsql-admin@postgresql.org">Postgres Admin
List</a></div><div><b>Sent:</b>Friday, August 16, 2002 10:19 AM</div><div><b>Subject:</b> OID</div></div><div><br
/></div><div><fontface="Times New Roman">Can anyone point me to some documentation on the use of OIDs? Why might I need
them?How to remove them in version 7.2? ..etc...</font></div><div><font face="Times New
Roman">Thanks</font></div><div><fontface="Times New Roman">Jodi Kanter</font></div><div><div class="Section1"><p
class="MsoNormal"><i><spanstyle="font-family: Arial; font-size: 9pt;">_______________________________<br
/></span></i><i><spanstyle="font-size: 10pt;">Jodi L Kanter<br /> BioInformatics Database Administrator<br />
Universityof Virginia<br /> (434) 924-2846<br /><a
href="mailto:jkanter@virginia.edu">jkanter@virginia.edu</a></span></i><spanstyle="font-family: Arial; font-size:
11pt;"><brstyle="" /><br style="" /></span><p class="MsoNormal"><span style="font-family: Arial; font-size:
11pt;"> </span><pclass="MsoNormal"><i><span style="font-family: Arial; font-size: 9pt;"> </span></i><p
class="MsoNormal"><i><spanstyle="font-family: Arial; font-size: 9pt;"> </span></i></div></div></blockquote><br /> 

Jodi Kanter wrote:
> Does anyone have a comment on the email below? Is there a general
> consensus on whether or not to use OIDs with version 7.2?

<snip>

>
> Can anyone point me to some documentation on the use of OIDs? Why might
> I need them? How to remove them in version 7.2? ..etc...

I think you're best to avoid using them. They are primarily for internal
system use. If you need an incrementing integer field, create your own
with SERIAL, or integer field and a sequence.

To create a table without oids, see:
   http://www.postgresql.org/idocs/index.php?sql-createtable.html

Joe

Jodi,

> How to remove them in version 7.2?

Create the table using WITHOUT OIDS.
Example.

CREATE TABLE users (
    user_id serial NOT NULL,
    user_name varchar(20) NOT NULL,
    passwd varchar(32) NOT NULL,
    CONSTRAINT pk_user_id PRIMARY KEY(user_id)
) WITHOUT OIDS;

Take Care.

Dan

----- Original Message -----
From: "Joe Conway" <mail@joeconway.com>
To: "Jodi Kanter" <jkanter@virginia.edu>
Cc: "Postgres Admin List" <pgsql-admin@postgresql.org>
Sent: Friday, August 16, 2002 16.40
Subject: Re: [ADMIN] Fw: OID


> Jodi Kanter wrote:
> > Does anyone have a comment on the email below? Is there a general
> > consensus on whether or not to use OIDs with version 7.2?
>
> <snip>
>
> >
> > Can anyone point me to some documentation on the use of OIDs? Why might
> > I need them? How to remove them in version 7.2? ..etc...
>
> I think you're best to avoid using them. They are primarily for internal
> system use. If you need an incrementing integer field, create your own
> with SERIAL, or integer field and a sequence.
>
> To create a table without oids, see:
>    http://www.postgresql.org/idocs/index.php?sql-createtable.html
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

On 16 Aug 2002 at 13:40, Joe Conway wrote:
> Jodi Kanter wrote:
> > Does anyone have a comment on the email below? Is there a general
> > consensus on whether or not to use OIDs with version 7.2?
>
> <snip>
>
> >
> > Can anyone point me to some documentation on the use of OIDs? Why
> > might I need them? How to remove them in version 7.2? ..etc...
>
> I think you're best to avoid using them. They are primarily for internal
> system use. If you need an incrementing integer field, create your own
> with SERIAL, or integer field and a sequence.

Is there any consensus aboit avoiding OIDs? I'm running a small test
system right now using OIDs as a means to refer to BLOBs. Should I
expect any trouble using OIDs in our future production system?

TIA!

On Mon, 19 Aug 2002, Jules Alberts wrote:

> Is there any consensus aboit avoiding OIDs? I'm running a small test
> system right now using OIDs as a means to refer to BLOBs. Should I
> expect any trouble using OIDs in our future production system?

I don't know if there's a consensus, but I certainly avoid using OIDs
completely in my own tables. They're can wrap, for a start, so in a
really busy, large database you might end up getting one that you
already have. Also, I don't like "hidden" fields; if I'm going to refer
to soemething, I like it to be nice and obvious what's being referred
to. And of course they're not portable.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Curt Sampson <cjs@cynic.net> writes:
> On Mon, 19 Aug 2002, Jules Alberts wrote:
>> Is there any consensus aboit avoiding OIDs? I'm running a small test
>> system right now using OIDs as a means to refer to BLOBs. Should I
>> expect any trouble using OIDs in our future production system?

> I don't know if there's a consensus, but I certainly avoid using OIDs
> completely in my own tables. They're can wrap, for a start, so in a
> really busy, large database you might end up getting one that you
> already have. Also, I don't like "hidden" fields; if I'm going to refer
> to soemething, I like it to be nice and obvious what's being referred
> to. And of course they're not portable.

As far as using BLOBs goes, you don't have a lot of choice: the lo_xxx
family of functions take and return OID, end of story.  Of course
Postgres' notion of a BLOB isn't very portable anyway.

I do agree that for a primary key in a user table, there's no very good
reason to use OIDs rather than using a SERIAL field (ie, a sequence).

            regards, tom lane