Monthly Archives: July 2016

Useful SQL queries

#To get all the rows in table student which have the address column as empty

select * from student where coalesce( trim(address),”)!=”;

#Get last 25 records whose time value 30 days greater than current time

SELECT to_timestamp(time/1000) from mytable WHERE to_timestamp(time) > NOW() – INTERVAL ’30 days’ order by desc time limit 25;

#Enable and disable query execution time

MYDB=# \timing
Timing is on.

MYDB=# \timing
Timing is off.
#Enable expanded display

This is an alternative output format. The default output would look clumsy if the query output has more number columns

MYDB=# \x ;
Expanded display is on.
MYDB=# select * from user;
-[ RECORD 1 ]—–+————————————————————————————————–
name            | Ravi

id                | 57622302

#Get count of all tables

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;