================
PostgreSQL Usage
================

User Basics
===========

Create a new user
-----------------

.. code-block:: bash

   su - postgres
   psql
   create user USER with password 'PASSWORD';


Database Basics
===============

Create a new database
---------------------

.. code-block:: bash

   su - postgres
   psql
   create database DATABASE with owner USER;

Copying a database
------------------

.. code-block:: bash

   su - postgres
   createdb -O USER -T DATABASE_ORIGINAL DATABASE_COPY

Renaming a database
-------------------

.. code-block:: bash

   su - postgres
   psql
   alter database DATABASE_ORIGINAL rename to DATABASE_RENAMED;

Remove a database
-----------------

.. code-block:: bash

   su - postgres
   psql
   drop database DATABASE;

Creating a database dump
------------------------

.. code-block:: bash

   su - postgres
   pg_dump DATABASE > dump.sql

.. code-block:: bash

   su - postgres
   pg_dump DATABASE | plzip > dump.sql.lz

Importing a database dump
-------------------------

.. code-block:: bash

   su - postgres
   psql -d DATABASE -f dump.sql

Switching to a database
-----------------------

.. code-block:: bash

  su - postgres
  psql
  \connect DATABASE;


Give all rights on a database to a user
---------------------------------------

.. code-block:: bash

   su - postgres
   psql
   grant all privileges on database DATABASE to USER;

Table Basics
============

Create a table
--------------

.. code-block:: bash

  su - postgres
  psql
  \connect DATABASE;
  create table TABLE(...);

Remove a table
--------------

.. code-block:: bash

   su - postgres
   psql
   \connect DATABASE;
   drop table TABLE;

Show table schema
-----------------

.. code-block:: bash

   su - postgres
   psql
   \connect DATABASE;
   \d TABLE;
   \d+ TABLE;