pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMatching the MYSQL "Describe <table-name>" command
5+ messages / 5 participants
[nested] [flat]
* Matching the MYSQL "Describe <table-name>" command
@ 2005-04-24 18:02 Alan Chandler <alan@chandlerfamily.org.uk>
2005-04-24 18:31 ` Re: Matching the MYSQL "Describe <table-name>" command Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-04-24 18:53 ` Re: Matching the MYSQL "Describe <table-name>" command Stephen Frost <sfrost@snowman.net>
2005-04-24 20:24 ` Re: Matching the MYSQL "Describe <table-name>" command Andrew - Supernews <andrew+nonews@supernews.com>
0 siblings, 3 replies; 5+ messages in thread
From: Alan Chandler @ 2005-04-24 18:02 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Matching the MYSQL "Describe <table-name>" command
2005-04-24 18:02 Matching the MYSQL "Describe <table-name>" command Alan Chandler <alan@chandlerfamily.org.uk>
@ 2005-04-24 18:31 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2005-04-24 18:31 UTC (permalink / raw)
To: Alan Chandler <alan@chandlerfamily.org.uk>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Matching the MYSQL "Describe <table-name>" command
2005-04-24 18:02 Matching the MYSQL "Describe <table-name>" command Alan Chandler <alan@chandlerfamily.org.uk>
@ 2005-04-24 18:53 ` Stephen Frost <sfrost@snowman.net>
2005-04-25 04:53 ` Re: Matching the MYSQL "Describe <table-name>" command Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 5+ messages in thread
From: Stephen Frost @ 2005-04-24 18:53 UTC (permalink / raw)
To: Alan Chandler <alan@chandlerfamily.org.uk>; +Cc: pgsql-sql
* 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
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Matching the MYSQL "Describe <table-name>" command
2005-04-24 18:02 Matching the MYSQL "Describe <table-name>" command Alan Chandler <alan@chandlerfamily.org.uk>
2005-04-24 18:53 ` Re: Matching the MYSQL "Describe <table-name>" command Stephen Frost <sfrost@snowman.net>
@ 2005-04-25 04:53 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-04-25 04:53 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Alan Chandler <alan@chandlerfamily.org.uk>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Matching the MYSQL "Describe <table-name>" command
2005-04-24 18:02 Matching the MYSQL "Describe <table-name>" command Alan Chandler <alan@chandlerfamily.org.uk>
@ 2005-04-24 20:24 ` Andrew - Supernews <andrew+nonews@supernews.com>
2 siblings, 0 replies; 5+ messages in thread
From: Andrew - Supernews @ 2005-04-24 20:24 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-25 04:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-24 18:02 Matching the MYSQL "Describe <table-name>" command Alan Chandler <alan@chandlerfamily.org.uk>
2005-04-24 18:31 ` Alvaro Herrera <alvherre@dcc.uchile.cl>
2005-04-24 18:53 ` Stephen Frost <sfrost@snowman.net>
2005-04-25 04:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-04-24 20:24 ` Andrew - Supernews <andrew+nonews@supernews.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox