Re: postgres metadata - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Is there something in Postgres that corresponds to the rowid
> pseudocolumn in Oracle, which represents the unique address of the
> row of data in the table?  If so, how would you access that
> in a query?
> ...
> so what do you have to do to create the table with oids?
> I am using v7.4

The "hidden" column oid is created by default in all current versions
of PostgreSQL, including 7.4. In the future, the default may
change, and you would need to add the words "WITH OIDS" to
the end of your CREATE TABLE command.

While oids are not guaranteed to be unique, they may suit your needs.
The problem is that the oid column has no "unique" constraint, and
that the oids will eventually wrap. The number of oids the system
uses is very, very large, but finite, so keep in mind that it may
someday wraparound. You can always create your own unique constraint
on the oid column, but this is not recommended as inserts may fail.

Another system column, "ctid", is also automatically created, and
cannot be turned off. However, it is generally only guaranteed to
be the same for the life of the transaction. If you are doing a
subselect or something else within a single query, the ctid should
work fine.

Your best bet may be to create your own column, and put a unique
constraint on it. See the documentation on "SERIAL" for a good way
of doing this. When you add such a column, you may want to append
"WITHOUT OIDS" to the end of your CREATE TABLE command, as you will
not be using them (oids), and it will save you some space.

Finally, you can simulate a sequential row number by using a
temporary sequence. Keep in mind this is not a column of the
table at all, but merely a way of numbering the rows returned.
This number cannot be used to access the table in any way.

CREATE TEMPORARY SEQUENCE row_number;
SELECT nextval('row_number'), foo, bar, baz FROM mytable
  WHERE foo>200 ORDER BY baz DESC;

Without knowing exactly what you are trying to do, it is hard to say
which approach would be best, but in general, use ctid if you can,
use a sequence (or the application) if you just need numbering,
use a SERIAL for everything else, and use oids for quick unimportant
queries.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200311262036
-----BEGIN PGP SIGNATURE-----

iD8DBQE/xVeVvJuQZxSWSsgRAgX5AKDbLqN65UEekaQbnwDRh1mFtLov/wCgsT8x
MpowtcfvoAYaycOti2DIQIM=
=h3jL
-----END PGP SIGNATURE-----