Handy SQL Queries

Search for an SQL table

select table_name
from information_schema.tables
where table_name like '%tablenamesubstring%'

One liner

select table_name from information_schema.tables where table_name like '%tablenamesubstring%';

Search for an SQL Column

select distinct table_name, column_name
from information_schema.columns
where column_name like '%columnsubstring%'
  and table_schema = 'databasename'

One Liner

select distinct table_name, column_name from information_schema.columns where column_name like '%columnsubstring%' and table_schema = 'databasename';

Create a number sequence as rows

SELECT @row := @row + 1 as row
FROM (select id from <SOME TABLE WIH LOADS OF ROWS> limit 1000 ) t, (SELECT @row := 0) r;

One Liner

SELECT @row := @row + 1 as row FROM (select id from <SOME TABLE WIH LOADS OF ROWS> limit 1000 ) t, (SELECT @row := 0) r;

Connect to psql database

psql -h <HOST> -p 5432 -d <DATABASE> -U <USERNAME>

View active psql connections per user

=> SELECT usename, count(*) FROM pg_stat_activity group by usename;