Thread: find column OID types with information schema?
Is it possible to find out the OID types of the columns of a table using the information schema? I see that I can get the character names of the types using this query: select * from information_schema.columns where table_name = 'my_table'; but I don't see a way to find the actual OID types of the columns. Is that possible? Thanks, Whit
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: > Thanks, Tom. > > So, it's more like this: > > select attname, atttypid from pg_attribute where attrelid = <attrelid > of my table>; > > hmm, so how do I find the attrelid of my table? I don't see it in pg_tables. > > -Whit > > > > On Mon, Apr 27, 2009 at 11:38 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 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 <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 On Mon, Apr 27, 2009 at 12:29 PM, Tom Lane <tgl@sss.pgh.pa.us> 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 >
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 On Mon, Apr 27, 2009 at 1:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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 >