Skip to content

Instantly share code, notes, and snippets.

View andreyshuster's full-sized avatar

Andrey Shuster andreyshuster

16 followers · 25 following
View GitHub Profile
@andreyshuster
andreyshuster / dashes.sh
Created September 18, 2022 17:37
replace spaces with dashes if filenames
# delete first echo for real results
for file in *; do echo mv "$file" "$(echo $file | sed 's/ - /_/g ; s/ /-/g')" ; done
@andreyshuster
andreyshuster / cyrlat.py
Created September 18, 2022 17:36
convert cyrillic to latin in filenames
#!/usr/bin/env python
import os
from shutil import move
translation_table = {
"А": "A",
"а": "a",
"Б": "B",
"б": "b",
@andreyshuster
andreyshuster / batch-kill-queries-by-condition.md
Created July 27, 2022 16:40
Batch kill queries by condition

There is two commands to kill query Soft kill: pg_cancel_backend(pid) Hard kill pg_terminate_backend(pid)

SELECT
	pg_terminate_backend(pid)
FROM
	pg_stat_activity
WHERE
@andreyshuster
andreyshuster / select-long-running-queries.md
Created July 27, 2022 16:36
Select long running queries 
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE query like '%<condition>%' AND (now() - pg_stat_activity.query_start) > interval '5 minutes';
@andreyshuster
andreyshuster / import-csv-to-sqlite.md
Last active July 27, 2022 16:53
import csv to sqlite db 
> .mode csv
> .import file_name.csv table_name
@andreyshuster
andreyshuster / gist:27ccc3a2d8537c817df3a6d60af23109
Last active May 4, 2022 18:56
How to output result of psql query to a file

From PSQL console

$ psql <postgres connection string>
=> \o result-filename.txt
=> SELECT * from the table

Another variant (CSV export)




  


  




    


          
    

      

        
        

          

              @andreyshuster
          

          

            
                andreyshuster
                / rotate_matrix.md
            
            

              Last active
              July 27, 2022 16:41
            

              
                Rotate matrix oneliner
              
          

        

      

        

  

      

    
list(zip(*original[::-1]))



  


  




    


          
    

      

        
        

          

              @andreyshuster
          

          

            
                andreyshuster
                / psql.md
            
            

              Last active
              May 24, 2022 02:56
            

              
                [PGSQL]Get rows count from all tables
              
          

        

      

        

  

      

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



  


  




    


          
    

      

        
        

          

              @andreyshuster
          

          

            
                andreyshuster
                / useful-snippets.org
            
            

              Created
              August 31, 2021 07:54
            

          

        

      

        

  

      

    
Find all folders older than certain date(Useful to find old projects)


find . -type d  -d 1 -not -newermt "2020-09-01 00:00:00"



  


  




    


          
    

      

        
        

          

              @andreyshuster
          

          

            
                andreyshuster
                / useful-snippets.org
            
            

              Created
              August 31, 2021 07:51
            

          

        

      

        

  

      

    
Add all untracked files to .gitignore


git status -s | grep -e "^\?\?" | cut -c 4- >> .gitignore