473,327 Members | 2,103 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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

----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
8 8682

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2
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: le*@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3
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.
----
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4
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 ma*******@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: le*@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #5
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)

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #6
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly


--
Martijn van Oosterhout <kl*****@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 SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/xS8ZY5Twig3Ge+YRAoKTAJ9yAXy29UTYJ3vlAgoEExvDoGMeBw Cgy9lO
DB8HOHV9HOYYA9fASRhYPvs=
=Y6ir
-----END PGP SIGNATURE-----

Nov 12 '05 #7

-----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 gr**@turnstep.com
PGP Key: 0x14964AC8 200311262036
-----BEGIN PGP SIGNATURE-----

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #8
gr**@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Brett Selleck | last post by:
I would like to start collecting Metadata on my XML schemas. I have searched the web for a method of automatically extracting Metadata from an XSD file and have come up empty handed. Basically what...
1
by: Santhu | last post by:
What is the difference between METADATA and MANIFEST and where do they get stored? Thank you
7
by: Abdul-Wahid Paterson | last post by:
Hi, I have had a site working for the last 2 years and have had no problems until at the weekend I replace my database server with a newer one. The database migration went like a dream and I had...
3
by: warwick.poole | last post by:
I am interested in finding out about Enterprise scale Postgres installations and clustering, especially on Linux. Essentially I would like to know the possibility that Postgres can store the...
16
by: Michal Hlavac | last post by:
Hello, I am working on web portal. There are some ads. We have about 200 000 ads. Every ad have own directory called ID, where is 5 subdirectories with various sizes of 5 images. Filesystem...
18
by: Joe Lester | last post by:
This thread was renamed. It used to be: "shared_buffers Question". The old thread kind of died out. I'm hoping to get some more direction by rephrasing the problem, along with some extra...
2
by: EP | last post by:
I'm looking for a method by which to access Windows files metadata and have not been able to find anything in the standard modules or via Google - what is the standard approach? Shamefully I...
1
by: Jack Orenstein | last post by:
I'm trying to configure PHP 5.2.0 with support for Postgres 8.1.3. Postgres was installed with FC5 without source. PHP's configure needs source. When I run configure: configure: error: Cannot...
0
by: NM | last post by:
Hello, I've got a problem inserting binary objects into the postgres database. I have binary objects (e.g. images or smth else) of any size which I want to insert into the database. Funny is it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.