Horton
Menu

Thread

  1. describe table query?

    Andrew Bulmer <toastafari@yahoo.com> — 2002-09-09T21:30:02Z

    I'm trying to write an Access clone in java that will
use PostGres as a backend. Problem is, I need to be
able to list all the fields (and data types) in a
table. I know about "\d" but that only seems to work
on the command line client (doesn't work if I pass it
in as a query). I know in mysql DESCRIBE <table> will
do it... is there an equivalent in postgres? I tried
google but all I could find were references to the \d command.

__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com

  2. Re: describe table query?

    Darren Ferguson <darren@crystalballinc.com> — 2002-09-09T22:15:57Z

    Start psql with -E and then run the \d tablename. This will give you the 
query that is run to get the fields from the table.

Darren
On Mon, 9 Sep 2002, Andrew Bulmer wrote:

> I'm trying to write an Access clone in java that will
> use PostGres as a backend. Problem is, I need to be
> able to list all the fields (and data types) in a
> table. I know about "\d" but that only seems to work
> on the command line client (doesn't work if I pass it
> in as a query). I know in mysql DESCRIBE <table> will
> do it... is there an equivalent in postgres? I tried
> google but all I could find were references to the \d command.
> 
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Finance - Get real-time stock quotes
> http://finance.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
Darren Ferguson

  3. Re: describe table query?

    Alex Krohn <alex@gossamer-threads.com> — 2002-09-10T01:03:55Z

    Hi Andrew,

> I'm trying to write an Access clone in java that will
> use PostGres as a backend. Problem is, I need to be
> able to list all the fields (and data types) in a
> table. I know about "\d" but that only seems to work
> on the command line client (doesn't work if I pass it
> in as a query). I know in mysql DESCRIBE <table> will
> do it... is there an equivalent in postgres? I tried
> google but all I could find were references to the \d command.

We use:

SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef 
FROM pg_class c, pg_attribute a, pg_type t 
WHERE c.relname = 'YOURTABLE'
    and a.attnum > 0 
    and a.attrelid = c.oid 
    and a.atttypid = t.oid 
ORDER BY attnum 

But I'd be interested to hear if there is a better way. =)

Hope that helps,

Alex

--
Alex Krohn <alex@gossamer-threads.com>

  4. Re: describe table query?

    Haris Peco <snpe@snpe.co.yu> — 2002-09-10T01:07:46Z

    If You use java then that is DatabaseMetaData.getColumns etc

regards
Haris Peco
On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> I'm trying to write an Access clone in java that will
> use PostGres as a backend. Problem is, I need to be
> able to list all the fields (and data types) in a
> table. I know about "\d" but that only seems to work
> on the command line client (doesn't work if I pass it
> in as a query). I know in mysql DESCRIBE <table> will
> do it... is there an equivalent in postgres? I tried
> google but all I could find were references to the \d command.
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Finance - Get real-time stock quotes
> http://finance.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

  5. Re: describe table query?

    Dan Ostrowski <dan@triad-dev.com> — 2002-09-10T01:55:40Z

    I also would love to know how you do this,  because I am REALLY missing the "DESCRIBE <table>" calls...

I work with mostly PHP4...

please help!

regards,
dan

On Tue, 10 Sep 2002 03:07:46 +0200
snpe <snpe@snpe.co.yu> wrote:

> If You use java then that is DatabaseMetaData.getColumns etc
> 
> regards
> Haris Peco
> On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> > I'm trying to write an Access clone in java that will
> > use PostGres as a backend. Problem is, I need to be
> > able to list all the fields (and data types) in a
> > table. I know about "\d" but that only seems to work
> > on the command line client (doesn't work if I pass it
> > in as a query). I know in mysql DESCRIBE <table> will
> > do it... is there an equivalent in postgres? I tried
> > google but all I could find were references to the \d command.
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! Finance - Get real-time stock quotes
> > http://finance.yahoo.com
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html

  6. Re: describe table query?

    Haris Peco <snpe@snpe.co.yu> — 2002-09-10T02:21:03Z

    Hello,
	This is example.You see JDBC specification on sun.java.com
regards
import java.io.*;
import java.sql.*;
import java.text.*;

public class poruka
{
	Connection db;	

	public poruka(String driver,String url,String user,String passwd) throws 
ClassNotFoundException, FileNotFoundException, IOException, SQLException
	{
		Class.forName(driver);
		db = DriverManager.getConnection(url,user,passwd);
		DatabaseMetaData dbmd = db.getMetaData();
		
		
		ResultSet rs=dbmd.getTables(null,null,null,new String[] {"TABLE"});
		while (rs.next()) {
			String ime=rs.getString(1);
			System.out.print("1 " + ime + "  ");
			ime=rs.getString(2);
			System.out.print("2 " + ime + "  ");
			ime=rs.getString(3);
			System.out.print("3 " + ime + "  ");
			ime=rs.getString(4);
			System.out.print("4 " + ime + "  ");
			ime=rs.getString(5);
			System.out.print("5 " + ime + "  ");
			System.out.println();
		}


		db.close();
	}

	public static void main(String args[])
	{
		System.out.println("JDBC test \n");

		try
		{
			poruka test = new poruka(args[0],args[1],args[2],args[3]);
		}
		catch (Exception ex)
		{
			System.err.println("Exception caught.\n" + ex);
			ex.printStackTrace();
		}
	}
}

On Tuesday 10 September 2002 03:55 am, Dan Ostrowski wrote:
> I also would love to know how you do this,  because I am REALLY missing the
> "DESCRIBE <table>" calls...
>
> I work with mostly PHP4...
>
> please help!
>
> regards,
> dan
>
> On Tue, 10 Sep 2002 03:07:46 +0200
>
> snpe <snpe@snpe.co.yu> wrote:
> > If You use java then that is DatabaseMetaData.getColumns etc
> >
> > regards
> > Haris Peco
> >
> > On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> > > I'm trying to write an Access clone in java that will
> > > use PostGres as a backend. Problem is, I need to be
> > > able to list all the fields (and data types) in a
> > > table. I know about "\d" but that only seems to work
> > > on the command line client (doesn't work if I pass it
> > > in as a query). I know in mysql DESCRIBE <table> will
> > > do it... is there an equivalent in postgres? I tried
> > > google but all I could find were references to the \d command.
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Yahoo! Finance - Get real-time stock quotes
> > > http://finance.yahoo.com
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 2: you can get off all lists
> > > at once with the unregister command (send "unregister
> > > YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

  7. Re: describe table query?

    scott.marlowe <scott.marlowe@ihs.com> — 2002-09-10T16:09:13Z

    There are two ways to do this.  One is the postgresql specific way, which 
is to crank up psql with the -E switch, then issue a \d for a table, and 
copy out the sql query that goes by.  On my 7.2.1 box, that gives me a 
set of queries like so for a table named 'bubba':
smarlowe=# \d bubba

********* QUERY **********
SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='bubba'
**************************

This NEXT one describes the table for us:

********* QUERY **********
SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, 
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a
WHERE c.relname = 'bubba'
  AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum
**************************

This one tells us what indexes it has:

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND NOT i.indisunique ORDER BY c2.relname
**************************

I'm not sure what the next two do, I think they have to do with foreign 
keys.
********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

********* QUERY **********
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname
**************************

This one gives us our constraints:
********* QUERY **********
SELECT rcsrc, rcname
FROM pg_relcheck r, pg_class c
WHERE c.relname='bubba' AND c.oid = r.rcrelid
**************************


The other way to do it is to issue a single query of the form "Select * 
from table limit 1" and use pg_num_fields, pg_field_name and 
pg_field_type commands to walk the returned fields to find their name and 
type.  The advantage of this method is that it is somewhat more 
transportable to other dbmses.


On Mon, 9 Sep 2002, Dan Ostrowski wrote:

> I also would love to know how you do this,  because I am REALLY missing the "DESCRIBE <table>" calls...
> 
> I work with mostly PHP4...
> 
> please help!
> 
> regards,
> dan
> 
> On Tue, 10 Sep 2002 03:07:46 +0200
> snpe <snpe@snpe.co.yu> wrote:
> 
> > If You use java then that is DatabaseMetaData.getColumns etc
> > 
> > regards
> > Haris Peco
> > On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> > > I'm trying to write an Access clone in java that will
> > > use PostGres as a backend. Problem is, I need to be
> > > able to list all the fields (and data types) in a
> > > table. I know about "\d" but that only seems to work
> > > on the command line client (doesn't work if I pass it
> > > in as a query). I know in mysql DESCRIBE <table> will
> > > do it... is there an equivalent in postgres? I tried
> > > google but all I could find were references to the \d command.
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Yahoo! Finance - Get real-time stock quotes
> > > http://finance.yahoo.com
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> > 
> > http://www.postgresql.org/users-lounge/docs/faq.html
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

  8. Re: describe table query?

    Haris Peco <snpe@snpe.co.yu> — 2002-09-10T20:53:41Z

    \d bubba
and select format_type ... is fine, but I want column name,column type,column 
size,column precision with any select command (type form \d exchange with
column_type,column_size and column_precision)
Is it possible ?

regards
Haris Peco
On Tuesday 10 September 2002 06:09 pm, scott.marlowe wrote:
> There are two ways to do this.  One is the postgresql specific way, which
> is to crank up psql with the -E switch, then issue a \d for a table, and
> copy out the sql query that goes by.  On my 7.2.1 box, that gives me a
> set of queries like so for a table named 'bubba':
> smarlowe=# \d bubba
>
> ********* QUERY **********
> SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
> FROM pg_class WHERE relname='bubba'
> **************************
>
> This NEXT one describes the table for us:
>
> ********* QUERY **********
> SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
> a.atthasdef, a.attnum
> FROM pg_class c, pg_attribute a
> WHERE c.relname = 'bubba'
>   AND a.attnum > 0 AND a.attrelid = c.oid
> ORDER BY a.attnum
> **************************
>
> This one tells us what indexes it has:
>
> ********* QUERY **********
> SELECT c2.relname
> FROM pg_class c, pg_class c2, pg_index i
> WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
> AND NOT i.indisunique ORDER BY c2.relname
> **************************
>
> I'm not sure what the next two do, I think they have to do with foreign
> keys.
> ********* QUERY **********
> SELECT c2.relname
> FROM pg_class c, pg_class c2, pg_index i
> WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
> AND i.indisprimary AND i.indisunique ORDER BY c2.relname
> **************************
>
> ********* QUERY **********
> SELECT c2.relname
> FROM pg_class c, pg_class c2, pg_index i
> WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
> AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname
> **************************
>
> This one gives us our constraints:
> ********* QUERY **********
> SELECT rcsrc, rcname
> FROM pg_relcheck r, pg_class c
> WHERE c.relname='bubba' AND c.oid = r.rcrelid
> **************************
>
>
> The other way to do it is to issue a single query of the form "Select *
> from table limit 1" and use pg_num_fields, pg_field_name and
> pg_field_type commands to walk the returned fields to find their name and
> type.  The advantage of this method is that it is somewhat more
> transportable to other dbmses.
>
> On Mon, 9 Sep 2002, Dan Ostrowski wrote:
> > I also would love to know how you do this,  because I am REALLY missing
> > the "DESCRIBE <table>" calls...
> >
> > I work with mostly PHP4...
> >
> > please help!
> >
> > regards,
> > dan
> >
> > On Tue, 10 Sep 2002 03:07:46 +0200
> >
> > snpe <snpe@snpe.co.yu> wrote:
> > > If You use java then that is DatabaseMetaData.getColumns etc
> > >
> > > regards
> > > Haris Peco
> > >
> > > On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> > > > I'm trying to write an Access clone in java that will
> > > > use PostGres as a backend. Problem is, I need to be
> > > > able to list all the fields (and data types) in a
> > > > table. I know about "\d" but that only seems to work
> > > > on the command line client (doesn't work if I pass it
> > > > in as a query). I know in mysql DESCRIBE <table> will
> > > > do it... is there an equivalent in postgres? I tried
> > > > google but all I could find were references to the \d command.
> > > >
> > > > __________________________________________________
> > > > Do You Yahoo!?
> > > > Yahoo! Finance - Get real-time stock quotes
> > > > http://finance.yahoo.com
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 2: you can get off all
> > > > lists at once with the unregister command (send "unregister
> > > > YourEmailAddressHere" to majordomo@postgresql.org)
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 5: Have you checked our
> > > extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly

  9. Re: describe table query?

    scott.marlowe <scott.marlowe@ihs.com> — 2002-09-10T22:24:37Z

    The second query here does that.  Or did you want it broken out in a 
seperate column (one for name, one for precision, etc...)

On Tue, 10 Sep 2002, snpe wrote:

> \d bubba
> and select format_type ... is fine, but I want column name,column type,column 
> size,column precision with any select command (type form \d exchange with
> column_type,column_size and column_precision)
> Is it possible ?
> 
> regards
> Haris Peco
> On Tuesday 10 September 2002 06:09 pm, scott.marlowe wrote:
> > There are two ways to do this.  One is the postgresql specific way, which
> > is to crank up psql with the -E switch, then issue a \d for a table, and
> > copy out the sql query that goes by.  On my 7.2.1 box, that gives me a
> > set of queries like so for a table named 'bubba':
> > smarlowe=# \d bubba
> >
> > ********* QUERY **********
> > SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
> > FROM pg_class WHERE relname='bubba'
> > **************************
> >
> > This NEXT one describes the table for us:
> >
> > ********* QUERY **********
> > SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
> > a.atthasdef, a.attnum
> > FROM pg_class c, pg_attribute a
> > WHERE c.relname = 'bubba'
> >   AND a.attnum > 0 AND a.attrelid = c.oid
> > ORDER BY a.attnum
> > **************************
> >
> > This one tells us what indexes it has:
> >
> > ********* QUERY **********
> > SELECT c2.relname
> > FROM pg_class c, pg_class c2, pg_index i
> > WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
> > AND NOT i.indisunique ORDER BY c2.relname
> > **************************
> >
> > I'm not sure what the next two do, I think they have to do with foreign
> > keys.
> > ********* QUERY **********
> > SELECT c2.relname
> > FROM pg_class c, pg_class c2, pg_index i
> > WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
> > AND i.indisprimary AND i.indisunique ORDER BY c2.relname
> > **************************
> >
> > ********* QUERY **********
> > SELECT c2.relname
> > FROM pg_class c, pg_class c2, pg_index i
> > WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
> > AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname
> > **************************
> >
> > This one gives us our constraints:
> > ********* QUERY **********
> > SELECT rcsrc, rcname
> > FROM pg_relcheck r, pg_class c
> > WHERE c.relname='bubba' AND c.oid = r.rcrelid
> > **************************
> >
> >
> > The other way to do it is to issue a single query of the form "Select *
> > from table limit 1" and use pg_num_fields, pg_field_name and
> > pg_field_type commands to walk the returned fields to find their name and
> > type.  The advantage of this method is that it is somewhat more
> > transportable to other dbmses.
> >
> > On Mon, 9 Sep 2002, Dan Ostrowski wrote:
> > > I also would love to know how you do this,  because I am REALLY missing
> > > the "DESCRIBE <table>" calls...
> > >
> > > I work with mostly PHP4...
> > >
> > > please help!
> > >
> > > regards,
> > > dan
> > >
> > > On Tue, 10 Sep 2002 03:07:46 +0200
> > >
> > > snpe <snpe@snpe.co.yu> wrote:
> > > > If You use java then that is DatabaseMetaData.getColumns etc
> > > >
> > > > regards
> > > > Haris Peco
> > > >
> > > > On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> > > > > I'm trying to write an Access clone in java that will
> > > > > use PostGres as a backend. Problem is, I need to be
> > > > > able to list all the fields (and data types) in a
> > > > > table. I know about "\d" but that only seems to work
> > > > > on the command line client (doesn't work if I pass it
> > > > > in as a query). I know in mysql DESCRIBE <table> will
> > > > > do it... is there an equivalent in postgres? I tried
> > > > > google but all I could find were references to the \d command.
> > > > >
> > > > > __________________________________________________
> > > > > Do You Yahoo!?
> > > > > Yahoo! Finance - Get real-time stock quotes
> > > > > http://finance.yahoo.com
> > > > >
> > > > > ---------------------------(end of
> > > > > broadcast)--------------------------- TIP 2: you can get off all
> > > > > lists at once with the unregister command (send "unregister
> > > > > YourEmailAddressHere" to majordomo@postgresql.org)
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 5: Have you checked our
> > > > extensive FAQ?
> > > >
> > > > http://www.postgresql.org/users-lounge/docs/faq.html
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

  10. Re: describe table query?

    Haris Peco <snpe@snpe.co.yu> — 2002-09-10T23:31:51Z

    Yes.I want that.
regards
Haris Peco
On Wednesday 11 September 2002 12:24 am, scott.marlowe wrote:
> The second query here does that.  Or did you want it broken out in a
> seperate column (one for name, one for precision, etc...)
>
> On Tue, 10 Sep 2002, snpe wrote:
> > \d bubba
> > and select format_type ... is fine, but I want column name,column
> > type,column size,column precision with any select command (type form \d
> > exchange with column_type,column_size and column_precision)
> > Is it possible ?
> >
> > regards
> > Haris Peco
> >
> > On Tuesday 10 September 2002 06:09 pm, scott.marlowe wrote:
> > > There are two ways to do this.  One is the postgresql specific way,
> > > which is to crank up psql with the -E switch, then issue a \d for a
> > > table, and copy out the sql query that goes by.  On my 7.2.1 box, that
> > > gives me a set of queries like so for a table named 'bubba':
> > > smarlowe=# \d bubba
> > >
> > > ********* QUERY **********
> > > SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
> > > FROM pg_class WHERE relname='bubba'
> > > **************************
> > >
> > > This NEXT one describes the table for us:
> > >
> > > ********* QUERY **********
> > > SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull,
> > > a.atthasdef, a.attnum
> > > FROM pg_class c, pg_attribute a
> > > WHERE c.relname = 'bubba'
> > >   AND a.attnum > 0 AND a.attrelid = c.oid
> > > ORDER BY a.attnum
> > > **************************
> > >
> > > This one tells us what indexes it has:
> > >
> > > ********* QUERY **********
> > > SELECT c2.relname
> > > FROM pg_class c, pg_class c2, pg_index i
> > > WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid =
> > > c2.oid AND NOT i.indisunique ORDER BY c2.relname
> > > **************************
> > >
> > > I'm not sure what the next two do, I think they have to do with foreign
> > > keys.
> > > ********* QUERY **********
> > > SELECT c2.relname
> > > FROM pg_class c, pg_class c2, pg_index i
> > > WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid =
> > > c2.oid AND i.indisprimary AND i.indisunique ORDER BY c2.relname
> > > **************************
> > >
> > > ********* QUERY **********
> > > SELECT c2.relname
> > > FROM pg_class c, pg_class c2, pg_index i
> > > WHERE c.relname = 'bubba' AND c.oid = i.indrelid AND i.indexrelid =
> > > c2.oid AND NOT i.indisprimary AND i.indisunique ORDER BY c2.relname
> > > **************************
> > >
> > > This one gives us our constraints:
> > > ********* QUERY **********
> > > SELECT rcsrc, rcname
> > > FROM pg_relcheck r, pg_class c
> > > WHERE c.relname='bubba' AND c.oid = r.rcrelid
> > > **************************
> > >
> > >
> > > The other way to do it is to issue a single query of the form "Select *
> > > from table limit 1" and use pg_num_fields, pg_field_name and
> > > pg_field_type commands to walk the returned fields to find their name
> > > and type.  The advantage of this method is that it is somewhat more
> > > transportable to other dbmses.
> > >
> > > On Mon, 9 Sep 2002, Dan Ostrowski wrote:
> > > > I also would love to know how you do this,  because I am REALLY
> > > > missing the "DESCRIBE <table>" calls...
> > > >
> > > > I work with mostly PHP4...
> > > >
> > > > please help!
> > > >
> > > > regards,
> > > > dan
> > > >
> > > > On Tue, 10 Sep 2002 03:07:46 +0200
> > > >
> > > > snpe <snpe@snpe.co.yu> wrote:
> > > > > If You use java then that is DatabaseMetaData.getColumns etc
> > > > >
> > > > > regards
> > > > > Haris Peco
> > > > >
> > > > > On Monday 09 September 2002 11:30 pm, Andrew Bulmer wrote:
> > > > > > I'm trying to write an Access clone in java that will
> > > > > > use PostGres as a backend. Problem is, I need to be
> > > > > > able to list all the fields (and data types) in a
> > > > > > table. I know about "\d" but that only seems to work
> > > > > > on the command line client (doesn't work if I pass it
> > > > > > in as a query). I know in mysql DESCRIBE <table> will
> > > > > > do it... is there an equivalent in postgres? I tried
> > > > > > google but all I could find were references to the \d command.
> > > > > >
> > > > > > __________________________________________________
> > > > > > Do You Yahoo!?
> > > > > > Yahoo! Finance - Get real-time stock quotes
> > > > > > http://finance.yahoo.com
> > > > > >
> > > > > > ---------------------------(end of
> > > > > > broadcast)--------------------------- TIP 2: you can get off all
> > > > > > lists at once with the unregister command (send "unregister
> > > > > > YourEmailAddressHere" to majordomo@postgresql.org)
> > > > >
> > > > > ---------------------------(end of
> > > > > broadcast)--------------------------- TIP 5: Have you checked our
> > > > > extensive FAQ?
> > > > >
> > > > > http://www.postgresql.org/users-lounge/docs/faq.html
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 3: if posting/reading
> > > > through Usenet, please send an appropriate subscribe-nomail command
> > > > to majordomo@postgresql.org so that your message can get through to
> > > > the mailing list cleanly
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

  11. Re: describe table query?

    Andrew Bulmer <toastafari@yahoo.com> — 2002-09-12T19:22:27Z

    Hey... 
Thanks to everyone that gave me the info on the
describe table query... I got it working.

Peace!

__________________________________________________
Do you Yahoo!?
Yahoo! News - Today's headlines
http://news.yahoo.com