Wednesday, March 6, 2019

New language injection in PyCharm

I love the PyCharm's ability to recognize one code type inside another. It is very convenient to use all the help to edit, say, SQL query inside a python function. However, I noticed if you have an HTML snippet inside the python code, it's not recognized as HTML, just as mere string. Time to fix it.

1. Go to Preference -> Language Injections

2. Press Add (the plus sign icon under the list of existing injections) and choose 2. Generic Python

3. Enter the following in the dialog.

4. Press OK to save all your changes

Now go to a python file and try it

Tuesday, February 12, 2019

Top 5 biggest tables in currently active scheme in PostgreSQL

SELECT C.relname                                     AS table_name,
       pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size,
       S.n_live_tup                                  AS total_rows
FROM pg_class C
       LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
       LEFT JOIN pg_stat_user_tables S ON (C.relname = S.relname)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC

Drop all active connections to PostgreSQL server

SELECT pg_terminate_backend(
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'db' -- ! replace db with the target database name
  AND pid <> pg_backend_pid();