Databases Cheatsheet

Common

SQL style guide

http://www.sqlstyle.guide/ru/

PostgreSQL

useful queries

• check the current session TZ

   SELECT current_setting('TIMEZONE');
   show timezone;

• show db tables

   SELECT * FROM pg_catalog.pg_tables;

• show tables without system tables

   SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

• list all databases
• \l
• show tables \tables and sequences \tables and views
• \dt \d \dS
• use database
• \c dbname
• alter column type e.g. boolean to integer

   ALTER TABLE tbname ALTER colname SET DEFAULT null;

   ALTER TABLE tbname ALTER colname TYPE INTEGER USING CASE WHEN colname = false THEN 0 ELSE 1 END;

   ALTER TABLE tbname ALTER colname SET DEFAULT 0;
   COMMIT;

backup, export database

• whole
• pg_dump -U username -f /path/dump.sql dbname
• schema only
• pg_dump -U username -s -f /path/dump.sql dbname
• single table
• pg_dump -U username -d dbname -t tbname > dump.sql
• single table data only
• pg_dump -U username --data-only --table=tablename sourcedb > dump.sql
• export data to csv

   COPY (SELECT * FROM table1) TO '/path/to/csv/data.txt' (format CSV);
   COPY (SELECT * FROM table1) TO '/path/to/csv/data.txt' WITH CSV DELIMITER ';' FORCE QUOTE *;

restore, import database

• restore database
• \i /path/dump.sql
• single table to database
• psql dbname < dump.sql
• extracting single table out of the large dump
• pg_restore --data-only --table=tablename fulldump.pg > dump.pg
• import data from csv

   COPY table1(col1, col2, col3) FROM '/path/to/csv/data.txt' WITH (FORMAT csv);

• import data from csv - include the header row

   COPY table1 FROM '/path/to/csv/data.txt' DELIMITER ',' CSV HEADER;

useful system commands

• show pg_config file location

  SHOW config_file

• connect as admin

• psql -h localhost postgres postgres

• prevent future connections && terminate all connections to this db except your own

  REVOKE CONNECT ON DATABASE thedb FROM public;
  SELECT pid, pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND pid <> pg_backend_pid();
  GRANT CONNECT ON DATABASE thedb TO public;

• drop all active sessions

  ALTER DATABASE db CONNECTION LIMIT 1;
  SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'db';

MySQL

restore, import database

• import table with the same name from one database to another

   CREATE TABLE db1.table1 SELECT * FROM db2.table1;

• import data from csv file. Basename of a file must be equal to tablename
• mysqlimport -u user -p -c column1,column2 --fields-terminated-by=; database_name table_name.txt