cancel
Showing results for 
Search instead for 
Did you mean: 

user_tables.num_rows

SOLVED
Adventurer

user_tables.num_rows

Hi,

I check the user_tables  in this site https://www.enterprisedb.com/docs/en/9.4/oracompat/Database_Compatibility_for_Oracle_Developers_Guid.... It doesn't have column NUM_ROWS.

Is there an equivalent in EDB 9.6 for Oracle's user_tables.num_rows?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Explorer

Re: user_tables.num_rows

You can pg_catalog.[obj|col]_description functions to get the comments of table and column. Something like given below:

 

edb=# SELECT
edb-# pg_catalog.obj_description(pg_catalog.to_regclass ('public.session_log') :: OID,
edb(# 'pg_class') AS table_comment,
edb-# pg_catalog.col_description(pg_catalog.to_regclass ('public.session_log') :: OID,
edb(# 1) "first column description",
edb-# pg_catalog.col_description(pg_catalog.to_regclass ('public.session_log') :: OID,
edb(# 2) "second column comment";
table_comment | first column description | second column comment
------------------+--------------------------+------------------------------------------
Our session logs | The user ID | The phone number including the area code

 

4 REPLIES
EDB Team Member

Re: user_tables.num_rows

EDB database implementation of view user_tables is a partial implementation when compared to Oracle's view user_tables.

 

It seems you are interested to know the number of rows present in a table and you can check it using view pg_stat_user_tables.

 

Please have a look at the below example.

-bash-4.1$ ./psql -p 5444 -d edb -U enterprisedb
psql.bin (9.6.6.11)
Type "help" for help.

edb=#
edb=#
edb=# select * from test;
a | b | c
---+---+---
1 | 1 | 1
2 | 2 | 2
3 | 3 | 3
4 | 4 | 4
5 | 5 | 5
(5 rows)

edb=# \d+ test;
Table "public.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
a | integer | not null | plain | |
b | integer | | plain | |
c | integer | | plain | |
Indexes:
"test_pkey" PRIMARY KEY, btree (a)
"test_b_key" UNIQUE CONSTRAINT, btree (b)

 

edb=# \x
Expanded display is on.
edb=#
edb=#
edb=# select * from pg_stat_user_tables where relname='test';
-[ RECORD 1 ]-------+---------------------------------
relid | 36859
schemaname | public
relname | test
seq_scan | 4
seq_tup_read | 10
idx_scan | 2
idx_tup_fetch | 2
n_tup_ins | 5
n_tup_upd | 0
n_tup_del | 0
n_tup_hot_upd | 0
n_live_tup | 5
n_dead_tup | 0
n_mod_since_analyze | 0
last_vacuum | 10-JAN-18 07:40:18.901299 -08:00
last_autovacuum |
last_analyze | 10-JAN-18 07:40:18.904507 -08:00
last_autoanalyze |
vacuum_count | 1
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 0

edb=# select count(*) from test;
-[ RECORD 1 ]
count | 5

 

The column n_live_tup will tell the count of rows present in the table.

 

If we delete some rows then n_live_tup will change as shown below.

 

edb=# delete from test where a>3;
DELETE 2

edb=# select count(*) from test;
-[ RECORD 1 ]
count | 3

edb=# select * from pg_stat_user_tables where relname='test';
-[ RECORD 1 ]-------+---------------------------------
relid | 36859
schemaname | public
relname | test
seq_scan | 7
seq_tup_read | 23
idx_scan | 3
idx_tup_fetch | 3
n_tup_ins | 5
n_tup_upd | 0
n_tup_del | 2
n_tup_hot_upd | 0
n_live_tup | 3
n_dead_tup | 2
n_mod_since_analyze | 2
last_vacuum | 10-JAN-18 07:40:18.901299 -08:00
last_autovacuum |
last_analyze | 10-JAN-18 07:40:18.904507 -08:00
last_autoanalyze |
vacuum_count | 1
autovacuum_count | 0
analyze_count | 1
autoanalyze_count | 0

 

 

 

Level 3 Adventurer

Re: user_tables.num_rows

user_tables is a view of "pg_class" table, the view doesn't have the num_rows column. However, you can get it from the pg_class table using below query. 

 

edb=# select reltuples,relname from pg_class where relname='emp';
reltuples | relname
-----------+---------
14 | emp
(1 row)

edb=# select count(1) from emp;
count
-------
14
(1 row)

 

 

Adventurer

Re: user_tables.num_rows

Hi,

Is there an equivalent in EDB 9.6 for Oracle's user_tab_comments and  user_col_comments?

Highlighted
Explorer

Re: user_tables.num_rows

You can pg_catalog.[obj|col]_description functions to get the comments of table and column. Something like given below:

 

edb=# SELECT
edb-# pg_catalog.obj_description(pg_catalog.to_regclass ('public.session_log') :: OID,
edb(# 'pg_class') AS table_comment,
edb-# pg_catalog.col_description(pg_catalog.to_regclass ('public.session_log') :: OID,
edb(# 1) "first column description",
edb-# pg_catalog.col_description(pg_catalog.to_regclass ('public.session_log') :: OID,
edb(# 2) "second column comment";
table_comment | first column description | second column comment
------------------+--------------------------+------------------------------------------
Our session logs | The user ID | The phone number including the area code