1. Postgres and psql for beginners

    Actually I am not sure whether I can put myself in the beginners pot, having run Postgresql for many years and built several products and service upon it. However a round of using PSQL for common use cases is needed I think. So this basically is for that usual situation where you have logged in to the server that postgres is running on and have access to the user postgres or have the rights to use psql.

    Startup psql command line interface
    psql

    Meta commands
    Meta commands are excellent short commands processed by psql itself. Some are shortcuts to longer SQL statements that get executed, others are system commands such as change directory.

    List databases on the system

    \l
    List of databases
    Name | Owner | Encoding | Collation | Ctype | Access privileges
    -----------+----------+----------+-------------+-------------+-----------------------
    database1 | tim | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    : postgres=CTc/postgres
    template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    : postgres=CTc/postgres
    database2 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
    (5 rows)

    List users on the system

    postgres=# \du
    List of roles
    Role name | Attributes | Member of
    -----------+-------------+-----------
    tim | Superuser | {}
    : Create role
    : Create DB
    postgres | Superuser | {}
    : Create role
    : Create DB
    another | | {}

    List help

    \?

    List help

    \?

    Change to database called “databasename”

    \c databasename