.. -*- coding: utf-8; -*-
.. include:: HEADER.rst

==========
 Postgre.
==========
.. contents::

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

Install and create new user and database::

  $ sudo apt-get install postgresql postgresql-client
  $ sudo su - postgres
  % psql
  postgres=# CREATE USER "mypguser" WITH PASSWORD 'mypguserpass';
  postgres=# CREATE DATABASE "mypgdatabase" OWNER "mypguser";
  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.

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

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

  set search_path to NAME;

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