Unanswered: whats the field 'oid' which is giving sequence numbers when included in select..table
i came across a interesting situation that i pose select * from [some table] i got 4 fields. then select *,oid from [same table] giving some sequence numbers in place of oid field. whats this concept.
whats this oid. its working for any table in my DB
Can anybodyexplain it plz.
Yes 'oids' are hidden indentifiers for every row in a table. I personally don't like this concept, because a truly relational DBMS should not rely on information for querying purposes that is not part of the working data set (a violation of Codd's Information Principle). Depending on oids for business logic is, IMHO, not too wise. In fact, the only reason I can see for using them is if your table does not have a primary or candidate key of any sort, and thus does not guarantee uniqueness of rows (another bad DB design decision, IMHO).
Also, you need to be careful with database dumps and restores if you depend on oids, also, because it is possible for oids to change during a dump/restore. (There are options in pg_dump to prevent that)
OIDs are necessary if you want to use PostgreSQL's [url=http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=ddl-inherit.html]table inheritance features.
But, I don't use the table inheritance features, preferring to stick with the straight-ahead relational model. Fortunately for me, PostgreSQL also allows you to disable the use of oids in your CREATE TABLE statement, simply by putting WITHOUT OIDS before the final semicolon at the end of the statement:
CREATE TABLE foobar (
foo int4 NOT NULL,
) WITHOUT OIDS;
This also can be useful for extremely large tables. See this from the CREATE TABLE documentation:
WITH OIDS or WITHOUT OIDS
This optional clause specifies whether rows of the new table should have OIDs (object identifiers) assigned to them. The default is to have OIDs. (If the new table inherits from any tables that have OIDs, then WITH OIDS is forced even if the command says WITHOUT OIDS.)
Specifying WITHOUT OIDS allows the user to suppress generation of OIDs for rows of a table. This may be worthwhile for large tables, since it will reduce OID consumption and thereby postpone wraparound of the 32-bit OID counter. Once the counter wraps around, uniqueness of OIDs can no longer be assumed, which considerably reduces their usefulness.