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=#