postgres metadata

Re: postgres metadata


I think oid is what you want.

select oid,* from table;

Take Care,
James

On Wed, 26 Nov 2003, Barbara Lindsey wrote:
[color=blue]
> 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
>[/color]

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

Re: postgres metadata

On Wed, 26 Nov 2003, James Thompson wrote:
[color=blue]
>
> I think oid is what you want.
>
> select oid,* from table;
>[/color]
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

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

Re: postgres metadata

so what do you have to do to create the table with oids?
I am using v7.4

Larry Rosenman wrote:[color=blue]
> On Wed, 26 Nov 2003, James Thompson wrote:
>
>[color=green]
>>I think oid is what you want.
>>
>>select oid,* from table;
>>[/color]
>
> Not if the table is created without OID's. Which may become default
> in 7.5.
>[/color]




--
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

Re: postgres metadata

On Wed, 26 Nov 2003, Barbara Lindsey wrote:
[color=blue]
> so what do you have to do to create the table with oids?
> I am using v7.4[/color]
It's still default in 7.4. (to create with OID's).

You can optionally create a table WITHOUT OIDs in 7.3+.


[color=blue]
> Take Care,
> James
>
> On Wed, 26 Nov 2003, Barbara Lindsey wrote:
>[color=green]
> > 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
> >[/color]
>
> ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
> 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@postg resql.org so that your
> message can get through to the mailing list cleanly
>
>
>
> Larry Rosenman wrote:[color=green]
> > On Wed, 26 Nov 2003, James Thompson wrote:
> >
> >[color=darkred]
> >>I think oid is what you want.
> >>
> >>select oid,* from table;
> >>[/color]
> >
> > Not if the table is created without OID's. Which may become default
> > in 7.5.
> >[/color]
>
>
>
>
>[/color]

--
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

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

Re: postgres metadata

On Wed, Nov 26, 2003 at 11:20:54AM -0500, Barbara Lindsey wrote:[color=blue]
> 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?[/color]

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?

Re: postgres metadata

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:[color=blue]
>
> I think oid is what you want.
>
> select oid,* from table;
>
> Take Care,
> James
>
> On Wed, 26 Nov 2003, Barbara Lindsey wrote:
> [color=green]
> > 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
> >[/color]
>
> ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
> 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@postg resql.org so that your
> message can get through to the mailing list cleanly[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> "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[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/xS8ZY5Twig3Ge+Y RAoKTAJ9yAXy29U TYJ3vlAgoEExvDo GMeBwCgy9lO
DB8HOHV9HOYYA9f ASRhYPvs=
=Y6ir
-----END PGP SIGNATURE-----

Re: postgres metadata


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

[color=blue]
> 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[/color]

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_nu mber'), 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.c om
PGP Key: 0x14964AC8 200311262036
-----BEGIN PGP SIGNATURE-----

iD8DBQE/xVeVvJuQZxSWSsg RAgX5AKDbLqN65U EekaQbnwDRh1mFt Lov/wCgsT8x
MpowtcfvoAYaycO ti2DIQIM=
=h3jL
-----END PGP SIGNATURE-----



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

Re: postgres metadata

greg@turnstep.c om writes:[color=blue]
> The problem is that the oid column has no "unique" constraint ...[/color]

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 majordomo@postg resql.org so that your
message can get through to the mailing list cleanly