Re: FW: information about tables via psql

On Tue, Jan 27, 2009 at 11:26:13AM -0500, Markova, Nina wrote:
> > I am in process of learning psql. I'm trying to extract information
> > about tables - as many details as possible. Something similar to what
> > I'm used to see using Ingres RDBMS

Postgres and Ingres are different database engines, so the output from
the various utilities will be different.

> > table structure,

Not sure what you mean by this, but if you mean the columns, their data
types and associated constraints then \d should be what you want.

> > creation date,

PG doesn't record this anywhere.

> > number of rows,

You have to explicitly get this by doing:

SELECT COUNT(*) FROM table;

The reason is that it's an expensive operation (in terms of disk IOs)
because a whole table scan has to be performed. I'd guess Ingress
has this information to hand at the cost of worse performance in the
presence of multiple writers. If you want a lower cost solution for
PG you can look in the statistics for your tables; but this will be
somewhat out of date. Something like this works for me:

SELECT n_live_tup FROM pg_stat_user_tables
WHERE relid = 'mytable'::REGCLASS;

> > primary keys,

again, \d is what you want

> > is it journalled,

Not sure what you mean here; but everything in PG is written to the WAL.
You can't control this.

--
Sam http://samason.me.uk/