PostgreSQL getting started

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

This is the one that worked on Ubuntu 21.04: 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: 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;'

Table of contents
- PostgreSQL tips PostgreSQL getting started

PostgreSQL tips

0 0

output one column per line: stackoverflow.com/questions/9604723/alternate-output-format-for-psql-showing-one-column-per-line-with-column-name
PostgreSQL does not automatically index foreign keys! stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys

 Ancestors (12)

View article source

Discussion (0)

New discussion

There are no discussions about this article yet.

Articles by others on the same topic (0)

There are currently no matching articles.

See all articles in the same topic Create my own version

PostgreSQL getting started

PostgreSQL tips

 Ancestors (12)

Discussion (0) Subscribe (1)

Articles by others on the same topic (0)

Discussion (0)