.. -*- 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