pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDescribe Table
6+ messages / 5 participants
[nested] [flat]
* Describe Table
@ 2007-12-17 16:56 danap@dandymadeproductions.com
2007-12-17 17:33 ` Re: Describe Table Erik Jones <erik@myemma.com>
2007-12-17 17:34 ` Re: Describe Table Colin Wetherbee <cww@denterprises.org>
2007-12-17 17:47 ` Re: Describe Table Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 3 replies; 6+ messages in thread
From: danap@dandymadeproductions.com @ 2007-12-17 16:56 UTC (permalink / raw)
To: pgsql-sql
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 bigint DEFAULT NULL,
decimal_type decimal(16,2) DEFAULT NULL,
numeric_type numeric(10,2) DEFAULT NULL,
real_type real DEFAULT NULL,
doublePrecision_type double precision DEFAULT NULL,
serial_type serial,
bigSerial_type bigserial,
PRIMARY KEY (data_type_id));
dana.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Describe Table
2007-12-17 16:56 Describe Table danap@dandymadeproductions.com
@ 2007-12-17 17:33 ` Erik Jones <erik@myemma.com>
2007-12-17 18:03 ` Re: Describe Table Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 6+ messages in thread
From: Erik Jones @ 2007-12-17 17:33 UTC (permalink / raw)
To: danap@dandymadeproductions.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Describe Table
2007-12-17 16:56 Describe Table danap@dandymadeproductions.com
2007-12-17 17:33 ` Re: Describe Table Erik Jones <erik@myemma.com>
@ 2007-12-17 18:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2007-12-17 18:03 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: danap@dandymadeproductions.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Describe Table
2007-12-17 16:56 Describe Table danap@dandymadeproductions.com
@ 2007-12-17 17:34 ` Colin Wetherbee <cww@denterprises.org>
2 siblings, 0 replies; 6+ messages in thread
From: Colin Wetherbee @ 2007-12-17 17:34 UTC (permalink / raw)
To: danap@dandymadeproductions.com; +Cc: pgsql-sql
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 | oid
wines | ctid
wines | name
wines | vintage
wines | origin
wines | specific_origin
wines | color
wines | type
wines | 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Describe Table
2007-12-17 16:56 Describe Table danap@dandymadeproductions.com
@ 2007-12-17 17:47 ` Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 0 replies; 6+ messages in thread
From: Richard Broersma Jr @ 2007-12-17 17:47 UTC (permalink / raw)
To: pgsql-sql; danap@dandymadeproductions.com
--- 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 information as '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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Describe Table
@ 2007-12-18 01:21 danap@dandymadeproductions.com
0 siblings, 0 replies; 6+ messages in thread
From: danap@dandymadeproductions.com @ 2007-12-18 01:21 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-12-18 01:21 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-12-17 16:56 Describe Table danap@dandymadeproductions.com
2007-12-17 17:33 ` Erik Jones <erik@myemma.com>
2007-12-17 18:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-12-17 17:34 ` Colin Wetherbee <cww@denterprises.org>
2007-12-17 17:47 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-12-18 01:21 Re: Describe Table danap@dandymadeproductions.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox