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

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

Thanks.

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

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:

> 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.

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.

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
--------------------------------------------------------





> -----Ursprüngliche Nachricht-----
> Von: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von
> Aaron Bratcher
> Gesendet: Freitag, 6. Februar 2004 15:10
> An: pgsql-general@postgresql.org
> Betreff: Re: [GENERAL] newbie question... how do I get table
> structure?
>
>
> 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:
>
> > 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.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

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:

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:

> 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.


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

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:

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