find column OID types with information schema?

Whit Armstrong <armstrong.whit@gmail.com> writes:

Is it possible to find out the OID types of the columns of a table
using the information schema?

No. Type OIDs are a Postgres-ism so they are not reflected in the
standards-mandated contents of the information_schema. If you want
OIDs you'll need to look at the underlying catalogs (pg_attribute
in particular).

regards, tom lane

Whit Armstrong escribi�:

Is it possible to find out the OID types of the columns of a table
using the information schema?

No; information_schema is limited to stuff that's defined by the SQL
standard. If you want OIDs, you need to extract that stuff from the
pg_catalog.* catalogs.

Type OIDs for attributes can be found in pg_attribute.atttypid.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

(I had accidentally replied to Tom only on my reply)

the OID's can be found as follows:

SELECT * FROM pg_attribute
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');

from this page:
http://www.postgresql.org/docs/8.3/interactive/datatype-oid.html

However, there is no example that uses a schema + tablename.

-Whit

On Mon, Apr 27, 2009 at 11:53 AM, Whit Armstrong
<armstrong.whit@gmail.com> wrote:

Whit Armstrong <armstrong.whit@gmail.com> writes:

However, there is no example that uses a schema + tablename.

If you're into masochism you can do that with a join of pg_class and
pg_namespace. But what's usually easier for one-off queries is to
use the regclass converter:

select attname, atttypid from pg_attribute
where attrelid = 'myschema.mytable'::regclass;

Most likely you'll also want

... and attnum > 0 and not attisdropped

to keep down the clutter.

regards, tom lane

Thanks, Tom.

I guess the answer is, yes, but perhaps you can help me decide.

I'm just reading this part of the documentation from the link I
posted: "OIDs are not added to user-created tables, unless WITH OIDS
is specified when the table is created." and also:

The oid type is currently implemented as an unsigned four-byte
integer. Therefore, it is not large enough to provide database-wide
uniqueness in large databases, or even in large individual tables. So,
using a user-created table's OID column as a primary key is
discouraged. OIDs are best used only for references to system tables.

Am I misinterpreting this documentation? Are there cases in which the
OID's of two tables will collide? I don't see any uniqueness
constraints on the pg_class table. Or are there cases in which a
table does not have an OID in the pg_class table? I apologize for
the dumb questions, but I'm just a little confused about the
internals.

Thanks,
Whit

Whit Armstrong <armstrong.whit@gmail.com> writes:

Am I misinterpreting this documentation? Are there cases in which the
OID's of two tables will collide? I don't see any uniqueness
constraints on the pg_class table.

You didn't look too hard:

regression=# \d pg_class
...
Indexes:
"pg_class_oid_index" UNIQUE, btree (oid)
"pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)

All system catalogs that have OIDs at all effectively treat them as a
primary key. If they weren't unique identifiers they wouldn't be good
for much ...

What the documentation is trying to point out is that the uniqueness
guarantees don't extend across tables. So for example a table and a
datatype could by coincidence have the same OID, since they live in
different system catalogs.

regards, tom lane

ok, got it.

thanks for the clarification and the hand holding.

-Whit