Home
Ads holder by Fournalist

Table of contents

Handy queries

List procedure/function

SELECT * FROM pg_proc WHERE proname='__procedurename__'

List view (including the definition)

SELECT * FROM pg_views WHERE viewname='__viewname__';

Show DB table space in use

SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));

Show DB space in use

SELECT pg_size_pretty(pg_database_size('__database_name__'));

Show current user's statement timeout

show statement_timeout;

Show table indexes

SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';

Get all indexes from all tables of a schema:

SELECT
   t.relname AS table_name,
   i.relname AS index_name,
   a.attname AS column_name
FROM
   pg_class t,
   pg_class i,
   pg_index ix,
   pg_attribute a,
    pg_namespace n
WHERE
   t.oid = ix.indrelid
   AND i.oid = ix.indexrelid
   AND a.attrelid = t.oid
   AND a.attnum = ANY(ix.indkey)
   AND t.relnamespace = n.oid
    AND n.nspname = 'kartones'
ORDER BY
   t.relname,
   i.relname

Execution data

SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE datname='__database_name__';

Get all queries from all dbs waiting for data (might be hung)

SELECT * FROM pg_stat_activity WHERE waiting='t'

Currently running queries with process pid:

SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
 pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

Casting

  • CAST (column AS type) or column::type
  • '__table_name__'::regclass::oid: Get oid having a table name

Query analysis

  • EXPLAIN __query__: see the query plan for the given query
  • EXPLAIN ANALYZE __query__: see and execute the query plan for the given query
  • ANALYZE [__table__]: collect statistics

 Generating random data

INSERT INTO some_table (a_float_value) SELECT random() * 100000 FROM generate_series(1, 1000000) i;

Create command

There are many CREATE choices, like CREATE DATABASE __database_name__, CREATE TABLE __table_name__ ... Parameters differ but can be checked at the official documentation.

Configuration

Service management commands:

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

Changing verbosity & querying Postgres log:

First edit the config file, set a decent verbosity, save and restart postgres:

sudo vim /etc/postgresql/9.3/main/postgresql.conf

# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1

sudo service postgresql restart

Now you will get tons of details of every statement, error, and even background tasks like VACUUMs

tail -f /var/log/postgresql/postgresql-9.3-main.log

How to add user who executed a PG statement to log (editing postgresql.conf):

log_line_prefix = '%t %u %d %a '

PSQL

Login

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

  • \q: Quit/Exit
  • \c __database__: Connect to a database
  • \d __table__: Show table definition (columns, etc.) including triggers
  • \d+ __table__: More detailed table definition including description and physical disk size
  • \l: List databases
  • \dy: List events
  • \df: List functions
  • \di: List indexes
  • \dn: List schemas
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \dT+: List all data types
  • \dv: List views
  • \dx: List all extensions installed
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables
  • \copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV
  • \des+: List all foreign servers
  • \dE[S+]: List all foreign tables

User Related:

  • \du: List users
  • \du __username__: List a username if present.
  • create role __test1__: Create a role with an existing username.
  • create role __test2__ noinherit login password __passsword__;: Create a role with username and password.
  • set role __test__;: Change role for current session to __test__.
  • grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__.
  • \deu+: List all user mapping on server
More

Dplyr

dplyr cheat sheet is a quick reference for dplyr that is a grammar of data manipulation, providing a consistent set of verbs that help you solve the most common data manipulation challenges.

Regex

A regular expression is a sequence of characters that specifies a search pattern.

PySpark

PySpark is an interface for Apache Spark in Python. It not only allows you to write Spark applications using Python APIs

Golang

Go is a statically typed, compiled programming language designed at Google by Robert Griesemer, Rob Pike, and Ken Thompson.

Watchman

Watchman exists to watch files and record when they change. It can also trigger actions (such as rebuilding assets) when matching files change.

Rollup.Js

Rollup Rollup is a module bundler for JavaScript which compiles small pieces of code into something larger and more complex, such as a library or application.

Bluebird.Js

Bluebird is a fully-featured Promise library for JavaScript. The strongest feature of Bluebird is that it allows you to "promisify" other Node modules in order to use them asynchronously. Promisify is a concept applied to callback functions.

Flow

Flow is a static type checker for your JavaScript code. It does a lot of work to make you more productive. Making you code faster, smarter, more confidently, and to a bigger scale.

Node.Js Api

Node.js® is a JavaScript runtime built on Chrome's V8 JavaScript engine.

C Preprocessor

Quick reference for the C macro preprocessor, which can be used independent of C/C++.

Sass

Sass is a preprocessor scripting language that is interpreted or compiled into Cascading Style Sheets. SassScript is the scripting language itself. Sass consists of two syntaxes.

Activeadmin

Active Admin is a Ruby on Rails plugin for generating administration style interfaces. It abstracts common business application patterns to make it simple for developers to implement beautiful and elegant interfaces with very little effort.

Fetch

The Fetch standard defines requests, responses, and the process that binds them: fetching.

Httpie

$ http POST http://example.com name="John" Host:example.com — JSON, cookies, files, auth, and other httpie examples.

Rspec

RSpec is a computer domain-specific language testing tool written in the programming language Ruby to test Ruby code. It is a behavior-driven development framework which is extensively used in production applications.

Saucelabs

Sauce Labs allows users to run tests in the cloud on more than 700 different browser platforms, operating systems, and device combinations.

Jasmine

Jasmine is a behavior-driven development framework for testing JavaScript code.

Sequelize

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server.

Ubuntu

Ubuntu is a Linux distribution based on Debian and composed mostly of free and open-source software. Ubuntu is officially released in three editions: Desktop, Server, and Core for Internet of things devices and robots.

Rails Models

Ruby on Rails, or Rails, is a server-side web application framework written in Ruby under the MIT License. Rail is a model–view–controller framework, providing default structures for a database, a web service, and web pages.

Git Log

Git is software for tracking changes in any set of files, usually used for coordinating work among programmers collaboratively developing source code during software development.

Controllers

Ruby on Rails, or Rails, is a server-side web application framework written in Ruby under the MIT License. Rails is a model–view–controller framework, providing default structures for a database, a web service, and web pages.

Ansible Examples

Ansible is an open-source software provisioning, configuration management, and application-deployment tool enabling infrastructure as code.

Bolt Quickstart

A quick guide to getting started writing Bolt tasks

Rspec-Rails

RSpec Rails defines ten different types of specs for testing different parts of a typical Rails application.

Rsync

rsync is a utility for efficiently transferring and synchronizing files between a computer and an external hard drive and across networked computers by comparing the modification times and sizes of files.

Ledger Cli

Ledger is a command-line based double-entry bookkeeping application. Accounting data is stored in a plain text file, using a simple format, which the users prepare themselves using other tools.

Homebrew

Homebrew is a free and open-source software package management system that simplifies the installation of software on Apple's operating system macOS as well as Linux.

Bundler

Bundler provides a consistent environment for Ruby projects by tracking and installing the exact gems and versions that are needed.