Thread: Matching the MYSQL "Describe " command
I am rather new to Postgres (running 7.4) but I am trying to port some PHP code that has been built to run with mysql. I have got stuck trying to find the equivalent of the Mysql "DESCRIBE <tablename>;" SQL statement that lists the columns and type identifiers of a table. I think I am going to do a SELECT on the "pg_attribute" table, but I don't understand what the "attrelid" column of that table refers to. I need to get the table name of the column so that I can restrict the select with a WHERE clause so I assumed it was a key into the pg_class table, but I can't see a column in this table that could potentially correspond and therefore I could join to it. Help -- Alan Chandler http://www.chandlerfamily.org.uk
On Sun, Apr 24, 2005 at 07:02:16PM +0100, Alan Chandler wrote: > I am rather new to Postgres (running 7.4) but I am trying to port some PHP > code that has been built to run with mysql. > > I have got stuck trying to find the equivalent of the Mysql "DESCRIBE > <tablename>;" SQL statement that lists the columns and type identifiers of a > table. > > I think I am going to do a SELECT on the "pg_attribute" table, but I don't > understand what the "attrelid" column of that table refers to. I need to get > the table name of the column so that I can restrict the select with a WHERE > clause so I assumed it was a key into the pg_class table, but I can't see a > column in this table that could potentially correspond and therefore I could > join to it. select attrelid::regclass, format_type(atttypid, atttypmod) from pg_attribute I think it does more or less what you want. The easiest way to know this sort of info is start psql with -E, and then describe a table using \d. It will show you what queries it issued. attrelid is a FK to pg_class.oid, which is a system (therefore hidden) column. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Lo esencial es invisible para los ojos" (A. de Saint Exúpery)
* Alan Chandler (alan@chandlerfamily.org.uk) wrote: > I am rather new to Postgres (running 7.4) but I am trying to port some PHP > code that has been built to run with mysql. > > I have got stuck trying to find the equivalent of the Mysql "DESCRIBE > <tablename>;" SQL statement that lists the columns and type identifiers of a > table. You might want to take a look at the schema called 'information_schema' and see if you can find what you want there. The only problem I have with that, currently anyway, is that it seems to only show things the current user owns as opposted to what the current user has access to, which seems a bit silly to me. > I think I am going to do a SELECT on the "pg_attribute" table, but I don't > understand what the "attrelid" column of that table refers to. I need to get > the table name of the column so that I can restrict the select with a WHERE > clause so I assumed it was a key into the pg_class table, but I can't see a > column in this table that could potentially correspond and therefore I could > join to it. In psql you can do: \set ECHO_HIDDEN 'yes' Then just run whatever psql \d or other command you want and it'll give you the queiries it used. Using information_schema should technically be more portable though, I believe. Stephen
On 2005-04-24, Alan Chandler <alan@chandlerfamily.org.uk> wrote: > I am rather new to Postgres (running 7.4) but I am trying to port some PHP > code that has been built to run with mysql. > > I have got stuck trying to find the equivalent of the Mysql "DESCRIBE ><tablename>;" SQL statement that lists the columns and type identifiers of a > table. > > I think I am going to do a SELECT on the "pg_attribute" table, Steer clear of reading the system catalogs directly; getting the right answers from them can be much harder than it appears, since they are organized purely for the convenience of the backend. For this specific task, information_schema.columns should be pretty close to what you need. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Stephen Frost <sfrost@snowman.net> writes:
> You might want to take a look at the schema called 'information_schema'
> and see if you can find what you want there. The only problem I have
> with that, currently anyway, is that it seems to only show things the
> current user owns as opposted to what the current user has access to,
> which seems a bit silly to me.
The SQL99 spec defines some of the views as showing only what you own,
and others as showing whatever you have access to. I think we track
the spec, but feel free to point out discrepancies.
regards, tom lane