PostgreSQL is a relational database management system (RDBMS) similar to MySQL. I usually go for MySQL, but I’m going to be working on a project that uses PostgreSQL, so I need to get familiar with it.

Installation

Installation on an Ubuntu system is easy:

1
sudo apt install postgresql

Roles

PostgreSQL uses roles as method of authentication. A role is basically a user, and each role has certain permissions.

With a new installation, a postgres role is created that is allowed to connect via peer authentication.

Peer Authentication

This method of authentication is only allowed for local connections. It consists on checking the currently logged-in Unix username against the available roles.

This means; to log-in using the postgres role, we need to first log-in as the postgres user:

1
2
sudo -i -u postgres
psql

Prompt

The PostgreSQL prompt looks like this:

1
postgres=#

To exit you can use \q or Ctrl + d.

Working with databases

To list all the databases \list or \l can be used:

1
2
3
4
5
6
7
8
9
10
postgres-# \list
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)

By default, you will be connected to the postgres database. \conninfo can be used to check which database you are currently connected to:

1
2
\conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

To create a new database:

1
CREATE DATABASE test;

You can switch databases with \connect or \c:

1
2
\c test
You are now connected to database "test" as user "postgres".

To delete a database:

1
DROP DATABASE test;

Woking with tables

Once connected to a database, \dt can be used to list all the tables:

1
2
3
4
5
6
\dt
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | users | table | postgres
(1 row)

SQL can be used to create a new table. For example:

1
2
3
4
CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255)
);

SERIAL is the equivalent of MySQL’s AUTO_INCREMENT.

To see the structure of a table \d can be used:

1
2
3
4
5
6
7
8
\d users
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(255) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

Conclusion

In this article I covered enough to log-in to the PostgreSQL server and work with databases and tables. In another post I’ll to try to cover user management, permissions and authentication.

[ postgresql  linux  ]
PostgreSQL user management
PostgreSQL - Connection refused