{c}

On Ubuntu 20.10 PostgreSQL 12.6, login with `psql` on my default username without <sudo> fails with: https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist

This is the one that worked on <Ubuntu 21.04>: https://stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152
``
sudo -u postgres createuser -s $(whoami)
createdb $(whoami)
``
Explanation:
* `sudo -u postgres` uses the `postgres` user via <peer authentication>
* `-s` in `createuser -s`: make it a superuser
* `createdb`: TODO why do we have to create a table with the same name as the user? Otherwise login fails.

You can now run `psql` without any password. This works without password due to peer authentication:
``
sudo cat /etc/postgresql/12/main/pg_hba.conf
``
shows that peer authentication is available to all users apparently:
``
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
``

List users:
``
psql -c '\du'
``
output:
``
                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
``

Delete user later on:
``
psql -c 'DROP USER username;'
``

Create a database:
``
createdb testdb0
``

Help toplevel:
``
help
``

Get help for Postgres commands such as `\h` and so on:
``
\?
``

List supported SQL commands:
``
\h
``

Show syntax for one type of command:
``
\h SELECT
``

List all databases:
``
psql -c '\l'
``
which shows:
``
    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)
``

Delete a database:
``
psql -c 'DROP DATABASE "testdb0";'
``

If you didn't give a database from the command line e.g.:
``
psql
``
you can do that afterwards with:
``
\c testdb0
``

Let's create a table and test that it is working:
``
psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
``

List tables, no special tables:
``
psql testdb0 -c '\dt'
``
gives:
``
        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)
``

View table schema: https://stackoverflow.com/questions/109325/postgresql-describe-table
``
psql testdb0 -c '\d+ table0'
``
output:
``
                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              |
 char0  | character(16) |           |          |         | extended |              |
``

Insert some data into it and get the data out:
``
psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'
``
output:
``
 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)
``

Delete the table:
``
psql testdb0 -c 'DROP TABLE table0;'
``


Postgresql getting started

ID: postgresql-getting-started