Обсуждение: postgres metadata
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? -- Barbara E. Lindsey, COG RDC Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162 ----
I think oid is what you want. select oid,* from table; Take Care, James On Wed, 26 Nov 2003, Barbara Lindsey wrote: > 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? > > > -- > Barbara E. Lindsey, > COG RDC > Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162 > > ---- > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 Kansas State University Department of Mathematics ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
On Wed, 26 Nov 2003, James Thompson wrote: > > I think oid is what you want. > > select oid,* from table; > Not if the table is created without OID's. Which may become default in 7.5. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
so what do you have to do to create the table with oids? I am using v7.4 Larry Rosenman wrote: > On Wed, 26 Nov 2003, James Thompson wrote: > > >>I think oid is what you want. >> >>select oid,* from table; >> > > Not if the table is created without OID's. Which may become default > in 7.5. > -- Barbara E. Lindsey, COG RDC Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162 ---- CONFIDENTIALITY NOTICE: The information contained in this electronic message is legally privileged and confidential and intended only for the use of the individual(s) or entity(ies) named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this email or any of it's components is strictly prohibited. If you have received this email in error, please contact the sender. ----
On Wed, 26 Nov 2003, Barbara Lindsey wrote: > so what do you have to do to create the table with oids? > I am using v7.4 It's still default in 7.4. (to create with OID's). You can optionally create a table WITHOUT OIDs in 7.3+. > Take Care, > James > > On Wed, 26 Nov 2003, Barbara Lindsey wrote: > > > 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? > > > > > > -- > > Barbara E. Lindsey, > > COG RDC > > Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162 > > > > ---- > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< > James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 > Kansas State University Department of Mathematics > ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > > > Larry Rosenman wrote: > > On Wed, 26 Nov 2003, James Thompson wrote: > > > > > >>I think oid is what you want. > >> > >>select oid,* from table; > >> > > > > Not if the table is created without OID's. Which may become default > > in 7.5. > > > > > > > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
On Wed, Nov 26, 2003 at 11:20:54AM -0500, Barbara Lindsey wrote: > 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? Maybe ctid? It's the physical column position, so it changes as soon as the row is UPDATEd, or even during VACUUM. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "La espina, desde que nace, ya pincha" (Proverbio africano)
Please remember: - OIDs are NOT unique - CTIDs are unique but not constant - SERIALs are unique and forever On Wed, Nov 26, 2003 at 10:39:43AM -0600, James Thompson wrote: > > I think oid is what you want. > > select oid,* from table; > > Take Care, > James > > On Wed, 26 Nov 2003, Barbara Lindsey wrote: > > > 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? > > > > > > -- > > Barbara E. Lindsey, > > COG RDC > > Phone: (352) 392-5198 ext. 314 Fax: (352) 392-8162 > > > > ---- > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< > James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 > Kansas State University Department of Mathematics > ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato
Вложения
-----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-----
greg@turnstep.com writes:
> The problem is that the oid column has no "unique" constraint ...
unless you add one, viz:
create unique index mytable_oids on mytable (oid);
which is de rigueur for any table you intend to rely on OID as an
identifier for. The index is needed not only to ensure uniqueness
but as a mechanism for fast access to a particular row by OID.
You should be aware though that once the OID counter wraps around (every
4 billion OIDs) there is a small chance that a newly-created OID will
duplicate a prior entry, resulting in a "duplicate key" failure in a
transaction that really didn't do anything wrong. If you have a moral
aversion to writing retry loops in your client code then this will
disgust you. My own take on it is that there are enough reasons why you
will need retry loops that one more shouldn't bug you.
These comments generally apply to SERIAL and the other alternatives
Greg mentioned, as well. The only difference is how fast do the
identifiers get eaten and how far is it to the wraparound point ...
regards, tom lane