Table row counts

Post date: Oct 20, 2014 12:0:40 AM

sachi=# CREATE TABLE sachitabletest (

sachi(#     id          integer,

sachi(#     gender     char(1),

sachi(#     title       varchar(40) NOT NULL,

sachi(#     did         integer NOT NULL,

sachi(#     date_prod   date,

sachi(#     kind        varchar(10),

sachi(#     len         interval hour to minute

sachi(# ) DISTRIBUTED BY (id,gender);

CREATE TABLE

sachi=# SELECT   oid, nspname AS schemaname,relname,reltuples

 FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND  relkind='r' 

and relname='sachitabletest'

ORDER BY reltuples DESC;

-[ RECORD 1 ]--------------

schemaname | public

relname    | sachitabletest

reltuples  | 0

sachi=# 

sachi=# insert into sachitabletest values(1,'M','Greenplum DBA','1',current_timestamp,'abc','3 hour');

INSERT 0 1

sachi=# 

sachi=# SELECT  N.oid, 

nspname AS schemaname,

relname,reltuples 

FROM pg_class C 

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 

 WHERE nspname NOT IN ('pg_catalog', 'information_schema') 

AND relkind='r' 

AND relname='sachitabletest' 

 ORDER BY reltuples DESC;

-[ RECORD 1 ]--------------

oid | 2200

schemaname | public

relname | sachitabletest

reltuples | 1

-- Finding no of rows in all tables

SELECT  N.oid, 

nspname AS schemaname,

relname,reltuples 

FROM pg_class C 

LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 

 WHERE nspname NOT IN ('pg_catalog', 'information_schema') 

AND relkind='r' 

--AND relname='sachitabletest' 

 ORDER BY reltuples DESC;

-- Important PG_CATALOG functions

sachi=# \df pg*analyze*

List of functions

-[ RECORD 1 ]-------+----------------------------------

Schema              | pg_catalog

Name                | pg_stat_get_last_analyze_time

Result data type    | timestamp with time zone

Argument data types | oid

Type                | normal

-[ RECORD 2 ]-------+----------------------------------

Schema              | pg_catalog

Name                | pg_stat_get_last_autoanalyze_time

Result data type    | timestamp with time zone

Argument data types | oid

Type                | normal

sachi=# \df pg*tuples*

List of functions

-[ RECORD 1 ]-------+----------------------------

Schema              | pg_catalog

Name                | pg_stat_get_tuples_deleted

Result data type    | bigint

Argument data types | oid

Type                | normal

-[ RECORD 2 ]-------+----------------------------

Schema              | pg_catalog

Name                | pg_stat_get_tuples_fetched

Result data type    | bigint

Argument data types | oid

Type                | normal

-[ RECORD 3 ]-------+----------------------------

Schema              | pg_catalog

Name                | pg_stat_get_tuples_inserted

Result data type    | bigint

Argument data types | oid

Type                | normal

-[ RECORD 4 ]-------+----------------------------

Schema              | pg_catalog

Name                | pg_stat_get_tuples_returned

Result data type    | bigint

Argument data types | oid

Type                | normal

-[ RECORD 5 ]-------+----------------------------

Schema              | pg_catalog

Name                | pg_stat_get_tuples_updated

Result data type    | bigint

Argument data types | oid

Type                | normal

sachi=# \df pg*vacuum*

List of functions

-[ RECORD 1 ]-------+---------------------------------

Schema              | pg_catalog

Name                | pg_stat_get_last_autovacuum_time

Result data type    | timestamp with time zone

Argument data types | oid

Type                | normal

-[ RECORD 2 ]-------+---------------------------------

Schema              | pg_catalog

Name                | pg_stat_get_last_vacuum_time

Result data type    | timestamp with time zone

Argument data types | oid

Type                | normal

sachi=#