Обсуждение: Describe Table
I've reviewed much of the documentation and the forums, but unable to seem to find a easy way to obtain the same thing as the 'psql \d table' through sql. I know I can create through collecting info on table, but seems there must be an easier way. I desire to create a standard type SQL dump syntax. ex. DROP TABLE IF EXISTS postgresqltypes; CREATE TABLE postgresqltypes ( -- Table id and creation data entries. data_type_id serial NOT NULL, smallInt_type smallint DEFAULT NULL, int_type integer DEFAULT NULL, bigInt_type bigintDEFAULT NULL, decimal_type decimal(16,2) DEFAULT NULL, numeric_type numeric(10,2) DEFAULT NULL, real_type real DEFAULTNULL, doublePrecision_type double precision DEFAULT NULL, serial_type serial, bigSerial_type bigserial, PRIMARYKEY (data_type_id)); dana.
On Dec 17, 2007, at 10:56 AM, <danap@dandymadeproductions.com> <danap@dandymadeproductions.com> wrote: > I've reviewed much of the documentation and the forums, > but unable to seem to find a easy way to obtain the same > thing as the 'psql \d table' through sql. I know I can create > through collecting info on table, but seems there must be an > easier way. I desire to create a standard type SQL dump > syntax. If you start psql with the -E flag, it will display all sql generated by internal commands such as those generated by \d commands. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
danap@dandymadeproductions.com wrote: > I've reviewed much of the documentation and the forums, > but unable to seem to find a easy way to obtain the same > thing as the 'psql \d table' through sql. I know I can create > through collecting info on table, but seems there must be an > easier way. I desire to create a standard type SQL dump > syntax. Briefly, you use the special pg_ tables [0]. The following query is probably not the most efficient way of doing it, but it shows the column names for the "wines" table. The first seven listed are system columns (tableoid - ctid), and the rest are data columns (name - score). You can look at the descriptions for each of the pg_ tables to refine your query a bit, exclude system columns, figure out data types, and so forth. cww=# SELECT pg_class.relname, attname FROM pg_attribute, pg_class WHERE attrelid = pg_class.reltype::integer - 1 AND pg_class.relname = 'wines'; relname | attname ---------+----------------- wines | tableoid wines | cmax wines | xmax wines | cmin wines | xmin wines | oidwines | ctid wines | name wines | vintage wines | origin wines | specific_origin wines | color wines | typewines | description wines | vintner wines | entry_date wines | score (17 rows) This query works on 8.1.9. Colin [0] http://www.postgresql.org/files/documentation/books/aw_pgsql/node183.html
--- On Mon, 12/17/07, danap@dandymadeproductions.com > I've reviewed much of the documentation and the forums, > but unable to seem to find a easy way to obtain the same > thing as the 'psql \d table' through sql. I I've never understood the information schema well enough to construct my own sql queries that provide the same useful informationas 'psql \d'. So instead, I cheat: start psql with the '-E' option to echo all sql strings psql sends to the back-end server. Simply copy and tailor each statement to suit your needs. Regards, Richard Broersma Jr.
Erik Jones <erik@myemma.com> writes: > <danap@dandymadeproductions.com> wrote: >> I've reviewed much of the documentation and the forums, >> but unable to seem to find a easy way to obtain the same >> thing as the 'psql \d table' through sql. > If you start psql with the -E flag, it will display all sql generated > by internal commands such as those generated by \d commands. Another alternative is to run "pg_dump -s -t <table>"; that will get you a lot closer to SQL-ready output, and you won't need to worry as much about updating your code for future system catalog changes. regards, tom lane
Thanks guys, That pretty much answers my questions. No there is not an easy way. While the access to a lot of internals to the postgresql database can be advantages it is exactly the kind of thing to break generic code anytime something changes. I think I'll take the harder approach and stick with the standard defined interfaces in java.sql. Did I read this in the manual somewhere? :) MySQLView Project Manager Dana Proctor