Re: find column OID types with information schema?

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

On Mon, Apr 27, 2009 at 12:29 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Whit Armstrong <armstrong(dot)whit(at)gmail(dot)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
>