.. title: PostgreSQL useful commands
.. slug: postgresql-useful-commands
.. date: 2016-12-18 22:23:33 UTC+01:00
.. tags: 
.. author: Bruno Santeramo
.. category: 
.. link: 
.. description: 
.. type: text

PostgreSQL useful commands
==========================

enter psql as postgres user
---------------------------

.. code:: bash

    sudo -u postgres psql

list all databases
******************

.. code:: psql

    \l

connect to database xxx
***********************

.. code:: psql

    \c xxx

list all tables in the current database
***************************************

.. code:: psql

    \dt

list all tables and indexes in current database
***********************************************

.. code:: psql

    \d

list column yyy detail
**********************

.. code:: psql

    \d yyy

drop table xxx and yyy in current database
******************************************

.. code:: psql

    drop table xxx, yyy;

drop xxx database
*****************

.. code:: psql

    DROP DATABASE "xxx";

show postgres version
*********************

.. code:: psql

    select version();

show current database
*********************

.. code:: psql

    SELECT current_database();

connection info
***************

.. code:: psql

    \conninfo

exit psql
*********

.. code:: psql

    \q

rename database
***************

.. code:: psql

    ALTER DATABASE oldame RENAME TO newname;

clone database
**************

.. code:: psql

    CREATE DATABASE newdb WITH TEMPLATE originaldb;