10 Helpful Postgresql Commands

Here are some more helpful postgresql commands that can help your out in your first few weeks of using Postgresql.  Finding the current, time, which database you are in, the size of a database or a table, the database ipaddress and port, etc…

1.  Select Current TimeStamp from Postgresql:

uptimemadeeasy=> select current_time;
(1 row)

2.  Select the current database that you are using:

uptimemadeeasy=> select current_database();
(1 row)

3.  Show the User You Are Logged in as:

uptimemadeeasy.com=# select current_user;
(1 row)

4.  Show the Version of Postgresql That You are Using:

uptimemadeeasy=> select version();
PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120
313 (Red Hat 4.4.7-3), 64-bit
(1 row)

5.  Show the IP Address you used to connect to the Postgresql Database

uptimemadeeasy=> select inet_server_addr();
(1 row)

6.  Show the IP Port That the Postgresql Database is Listening On

uptimemadeeasy=> select inet_server_port();
(1 row)

7.  Select When Postgresql was Last Started

uptimemadeeasy=> select pg_postmaster_start_time();
2013-11-27 17:42:43.390377+00
(1 row)

8.  Select How Long Postgresql Has Been Up

uptimemadeeasy=> select date_trunc(‘minute’, current_timestamp – pg_postmaster_start_time()) as “postgresql uptime”;
postgresql uptime
7 days 08:59:00
(1 row)

 9.  Get the Size of Your Postgresql Database

uptimemadeeasy=> select pg_database_size(‘uptimemadeeasy’);
(1 row)

10.  Find the Size of a Table, its Indexes, and Other Related Space

uptimemadeeasy=> select pg_total_relation_size(‘awards’);
(1 row)
The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: http://uptimemadeeasy.com. You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *