Useful Postgres tables queries

03/26/2020 1,048 People found this article helpful 483,210 Views

Description

It is useful to see if summarized data is written in the Postgres database. Below are some of the example query which may be useful in some circumstances.

Resolution

Connect to Postgres database using KB: 200356
To see if summarized data currently being written in the table:

syslogs=# select count(*) from raw_201607;
count
---------
6329367 --------> make a note of the figure
(1 row)

- We can use the same command few times to see if the raw is increasing in numbers. If the number goes up this indicates data is being currently written in the reporting database.
To see when the last time data being written in the table:

syslogs=# select max(rcvd_time) from raw_201607;
max
---------------------
2016-07-19 10:11:55
(1 row)
To see the number of raws of data for a specific month
syslogs=# select count(*) from raw_201607;
count --------- 1090081 (1 row)
To see the list of Firewalls serials with number of rows:

syslogs=# select serial, count(1) from raw_201607 group by serial;
serial | count
---------------------------+--------------
C0EAE4819A66 | 240490
C0EAE4AF3D7C | 305249
C0EAE4744004 | 6378
To see the list of tables in the database:

syslogs=# dt
List of relations
Schema | Name | Type | Owner | Options
--------+----------------+-------+----------+---------------------
public | raw_201512 | table | postgres | {engine=infobright}
public | raw_201601 | table | postgres | {engine=infobright}
public | summary_status | table | mydbuser | {engine=postgres}
To see the list of users:

syslogs=# du
List of roles
Role name | Attributes | Member of
---------------+--------------------------------------------------------------------------+-----------
administrator | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
To exit from the database

syslogs=# q + Enter

Not Finding Your Answers?

ASK THE COMMUNITY

Was This Article Helpful?

YES NO

Useful Postgres tables queries

Description

Resolution

Related Articles

Categories

Not Finding Your Answers?

Was This Article Helpful?

Article Helpful Form

Article Not Helpful Form

Follow Us

Subscribe to newsletter

Stay In Touch