Thread: FW: information about tables via psql
Hi,
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 - table structure, creation date, number of rows, primary keys, is it journalled, etc. In Ingres in order to see that information I use commands 'help <tablename>' and more detailed version 'help table <tablename>'. Is there any postgres equivalent of these commands?
With psql I used '\d <tablename>' and '\d+ <tablename'. None of those showed me what I need. See below.
Thanks in advance,
Nina
eq=# \d+ c_ass \g
Table "public.c_ass"
Column | Type | Modifiers | Description
-------------+---------------+------------------------------+-------------
arrid | character(16) | not null |
solid | character(16) | not null |
wt_flag | character(1) | not null default ' '::bpchar |
res | real | |
wt | real | |
dist | real | |
az | real | |
octant | character(2) | not null default ' '::bpchar |
mag1 | real | |
av_mag1_flg | character(1) | not null default ' '::bpchar |
mag1type | character(4) | not null default ' '::bpchar |
mag1_res | real | not null default 0 |
cphase | character(8) | not null default ' '::bpchar |
emerang | real | |
delta | real | |
Has OIDs: no
Ingres
=====
* help c_ass \g
Executing . . .
Name: c_ass
Owner: dba
Created: 2008-05-02 10:03:09
Type: user table
Version: II2.6
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
arrid char 16 no no 2
solid char 16 no no 1
wt_flag char 1 no yes
res float 4 yes null
wt float 4 yes null
* help table c_ass \g
Executing . . .
Name: c_ass
Owner: dba
Created: 2008-05-02 10:03:09
Location: ii_database
Type: user table
Version: II2.6
Page size: 8192
Cache priority: 0
Alter table version: 0
Alter table totwidth: 87
Row width: 87
Number of rows: 3350762
Storage structure: btree
Compression: none
Duplicate Rows: not allowed
Number of pages: 83329
Overflow data pages: 0
Journaling: enabled
Base table for view: no
Optimizer statistics: none
Column Information:
Key
Column Name Type Length Nulls Defaults Seq
arrid char 16 no no 2
solid char 16 no no 1
wt_flag char 1 no yes
res float 4 yes null
wt float 4 yes null
Secondary indexes:
Index Name Structure Keyed On
c_ass_idx isam arrid, solid
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/