Re: Table with no primary key

Dmitri,

> It's possible to create a table without setting primary key. So, I
> suppose, that Postgresql maintains a kind of internal primary key for
> that table.
>
> Questions:
> 1. Am I right? :)

Yes.  By default, Postgres creates an "Object ID" for each row.

> 2. Can I (how?) to access this key?

It is called the OID field, of type OID.

HOWEVER, there are quite a few problems with using the OID as your
 primary key:

1. The OIDs are not backed up by default.  You must always remember to
 use special backup options, or you lose your key.
2. In a large database (over 100 million rows over time), OIDs can
 "wrap-around" and start over at the beginning of the sequence.  While
 Postgres can handle this "wrap-around", it would have grave
 consequences for any key value based on OID.
3. Postgres 7.2 now allows OID-less tables for tables (such as
 historical journal tables) whose level of inserts and deletes is very
 high (as defined in the CREATE TABLE statement).  Thus, you cannot
 count on all tables having an OID column.
4. The OID column is particular to Postgres, so you canot port any
 solution developed using this column as a key to other RDBMSs.

Overally, it is a FAR better thing for you to create your own primary
 key and not count on default behavior of Postgres.

> 3. Is this "internal key" maintained for every table or only for
>  those
> with no primary key set?

All tables, except per (3) above.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco