.. -*- coding: utf-8; -*-

==========
 Postgres
==========
.. contents::
   :local:

Installing on Debian
====================

Install and create new user and database::

  $ sudo apt-get install postgresql postgresql-client
  $ sudo su - postgres
  % psql
  postgres=# CREATE USER myuser WITH PASSWORD 'mypass';
  postgres=# CREATE DATABASE mydb OWNER myuser;
  postgres=# \q

Connect as user ``mypguser`` to new database::

  $ su - mypguser
  $ psql mypgdatabase

In order to create local host superuser::

  $ sudo su - postgres
  $ createuser --superuser USER
  $ exit
  $ sudo -u USER psql

..

https://wiki.debian.org/PostgreSql
  Debian wiki instructions.

Check version
=============

By SQL query::

  SELECT version();
  SHOW server_version;
  SHOW server_version_num;

From executable::

  $ postgres -V

Check locations
===============
::

  SHOW hba_file;
  SHOW config_file;

Check settings
==============
::

   SELECT name, setting FROM pg_settings;

List databases, schemas and tables
==================================

Default database is ``postgres``.

To list databases and database locales::

  $ psql -U pgadmin -l

or::

  => SELECT datname FROM pg_database WHERE datistemplate = false;
  => \l

To switch databases::

  => \connect NAME

Schemas::

  => select schema_name from information_schema.schemata;
  => select nspname from pg_catalog.pg_namespace;
  => \dn *

To list all tables in the current database::

  => SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;
  => \dt

or in all schemas::

  => \dt *.*

List views::

  \dv

List users::

  \du
  \du+

List schemas::

  \dn

List indexes::

  \di

List functions::

  \df

List triggers::

  \dy


List misc info::

  select current_database();
  select current_schema();
  select current_user;
  select session_user;
  select current_date;
  select version();

https://www.postgresql.org/docs/current/functions-info.html
  System Information Functions.

Set default schema.
===================
::

  set search_path to NAME;
  set schema 'NAME';

Database, table, index size
===========================

Database size::

  SELECT pg_database_size('geekdb');  -- in bytes
  SELECT pg_size_pretty(pg_database_size('dbname'));

List of databases sizes::

  \l+

List tables sizes::

  \d+

Table total size (with indexes)::

  SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename'));

Sole table size (without indexes and other)::

  SELECT pg_size_pretty(pg_relation_size('schemaname.tablename'));

Largest table in the PostgreSQL database::

  SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Using psql client
=================

Using password file ``~/.pgpass``::

  # comment
  hostname:port:database:username:password
  hostname:port:*:username:password
  hostname:*:*:username:password

Connect by::

  $ psql -U $USER -h $HOST  $SCHEMA

How to view execution plan::

  EXPLAIN query;
  EXPLAIN ANALYZE query;

Collect statistics::

  ANALYZE table;

How to redirect the output of query to a file::

  \o output_file
  SELECT * FROM pg_class;

Switch database::

  \connect DBNAME
  \c DBNAME

Load external SQL file::

  \i my.sql

Export data
===========

Export from ``psql``::

  \copy (SELECT * FROM foo) TO '/tmp/my.csv' WITH CSV

Import data
===========

Import CSV file::

  \copy tbl_name from 'my.csv' csv;
  \copy tbl_name from 'my.csv' delimiter ':' csv;

  $ psql -U $USER -h $HOST $DB -c "\\copy tbl_name from 'my.csv' csv"

JDBC driver
===========

https://jdbc.postgresql.org/about/about.html
  The current version of the driver should be compatible with PostgreSQL 8.2 and higher, and Java 6
  (JDBC 4.0), Java 7 (JDBC 4.1), Java 8 (JDBC 4.2) and Java 9.
https://jdbc.postgresql.org/download.html
  Java/JDBC/Postgres compatibility table.