newbie question... how do I get table structure?

Aaron Bratcher wrote:

What command can I use to get the structure of a given table?

If psql is client

\d tablename

Thanks.

--
Aaron Bratcher
ab DataTools
http://www.abDataTools.com

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

In article <4022FB80.6090205@commandprompt.com>,
"Joshua D. Drake" <jd@commandprompt.com> writes:

Aaron Bratcher wrote:

What command can I use to get the structure of a given table?

If psql is client

\d tablename

Without psql you can use

pg_dump -s DBNAME -t TBLNAME

from your shell prompt.

Is there no way I can do it with a standard select command in a
different client? I don't need the indexes, just the column
names/types.
--
Aaron Bratcher
ab DataTools
http://www.abDataTools.com

On Feb 6, 2004, at 8:12 AM, Harald Fuchs wrote:

Aaron Bratcher wrote:

Is there no way I can do it with a standard select command in a
different client? I don't need the indexes, just the column names/types.

For PostgreSQL 7.3 and above:

SELECT
a.attname,
format_type(a.atttypid, a.atttypmod)
FROM
pg_catalog.pg_class c INNER JOIN
pg_catalog.pg_namespace n ON (c.relnamespace = n.oid) INNER JOIN
pg_catalog.pg_attribute a ON (a.attrelid = c.oid)
WHERE
n.nspname = '{schema_name}' AND
c.relname = '{table_name}' AND
a.attisdropped = false AND
a.attnum > 0

Replace {schema_name} and {table_name}.

--
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

this should work (don't forget to replace <TABLE NAME>!!!):

SELECT
A.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS typeName
FROM
pg_class C,
pg_attribute A,
pg_type T
WHERE
C.relname ILIKE '<TABLE NAME>' AND
(C.oid=A.attrelid) AND
(T.oid=A.atttypid) AND
(A.attnum>0) AND
(NOT A.attisdropped)
ORDER BY
A.attnum;

Does anyone know if the ansi sql standard defines any way to do this?
I've seen the DESCRIBE TABLE/INDEX/... or SHOW TABLE/INDEX/... commands
in other databases, but I don't really know if they are extensions or
not.

On Fri, 2004-02-06 at 11:10, Aaron Bratcher wrote:

try something like this:

select attname from pg_class, pg_attribute where
relname='your_tablename' and attrelid=relfilenode;

--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

On Friday 06 February 2004 15:00, Franco Bruno Borghesi wrote:

Does anyone know if the ansi sql standard defines any way to do this?
I've seen the DESCRIBE TABLE/INDEX/... or SHOW TABLE/INDEX/... commands
in other databases, but I don't really know if they are extensions or
not.

There's the new information schema, which displays standard info in 7.4 -
support elsewhere is variable I believe.

--
Richard Huxton
Archonet Ltd

Franco Bruno Borghesi <franco@akyasociados.com.ar> writes:

Does anyone know if the ansi sql standard defines any way to do this?
I've seen the DESCRIBE TABLE/INDEX/... or SHOW TABLE/INDEX/... commands
in other databases, but I don't really know if they are extensions or
not.

They are extensions (and very nonstandard ones at that). What the SQL
standard provides are standardized views of the system catalogs located
in the INFORMATION_SCHEMA schema. The per-spec way to do this would
be something like

select column_name, data_type
from information_schema.columns
where table_name = 'foo'
order by ordinal_position;

Now Postgres only got around to supporting the INFORMATION_SCHEMA views
in 7.4 (although in principle you could have defined most of these views
earlier, certainly in 7.3). I'm not real sure how many other DBs
support INFORMATION_SCHEMA either ... it may not be all that "standard".

regards, tom lane

that's great, I didn't know about the information schema... guess I
never read the 'what's new' document :)

On Fri, 2004-02-06 at 13:15, Tom Lane wrote: