Re: FW: information about tables via psql - Mailing list pgsql-general

From Sam Mason
Subject Re: FW: information about tables via psql
Date
Msg-id 20090127181327.GH3008@frubble.xen.chris-lamb.co.uk
Whole thread Raw
In response to FW: information about tables via psql  ("Markova, Nina" <Nina.Markova@NRCan-RNCan.gc.ca>)
List pgsql-general
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/

pgsql-general by date:

Previous
From: Karsten Hilbert
Date:
Subject: performance advice needed: join vs explicit subselect
Next
From: Jeff Davis
Date:
Subject: Re: New 8.4 hot standby feature