describe table query?
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
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
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>
If You use java then that is DatabaseMetaData.getColumns etc
regards
Haris Peco
Show quoted text
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)
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:
Show quoted text
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?
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();
}
}
}
Show quoted text
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,
danOn 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 PecoOn 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?---------------------------(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
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:
Show quoted text
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,
danOn 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?---------------------------(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
\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
Show quoted text
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,
danOn 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 PecoOn 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?---------------------------(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
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:
Show quoted text
\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,
danOn 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 PecoOn 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?---------------------------(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)
Yes.I want that.
regards
Haris Peco
Show quoted text
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 PecoOn 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,
danOn 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 PecoOn 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?---------------------------(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)
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