Basic

PostgreSQL

Introduction
Docker
psql
Scott
Datagrip
pgAdmin

Introduction

PostgreSQL is a powerful, open source object-relational database system.

If you don’t know how an SQL database works, start with the activities in Sqlite.

Docker

Start a Linux virtual machine with Windows - Windows Subsystem for Linux (WSL).

Install docker as explained in Docker.

Create a new container running postgres:

docker run -d --name postgres --restart=always -p 5432:5432 -e POSTGRES_PASSWORD=password -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password postgres:18

psql

psql is a shell for PostgreSQL that lets you write queries interactively, send them to PostgreSQL, and view the results.

Create a new terminal session in the container:

docker exec -it postgres bash

Start a session with the user postgres:

psql -U postgres

A Postgres server can manage several databases at the same time.

Use the command \l (or \list) to see all databases defined on the server,

postgres=# \l
                                                List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+-------+--------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C       | en    |        |           |
 template0 | postgres | UTF8     | libc            | C       | en    |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C       | en    |        |           | =c/postgres          +
           |          |          |                 |         |       |        |           | postgres=CTc/postgres
(3 rows)

postgres is the default database you connect to.

Create a database named test:

create database test;

CREATE DATABASE

Connect to the test database:

\c test

You are now connected to database "test" as user "postgres".
test=>

With \dt you can see which tables the database has:

\dt

Did not find any relations.

At any time you can “clear” the shell with \! cls:

postgres=# \! cls

Use \q to end the session:

test=# \q
$

Scott

Scott is a demo database Scott.

Import the database “Scott” to the container:

wget https://gitlab.com/xtec/postgres-data/-/raw/main/scott.sql
docker cp scott.sql postgres:scoot.sql
cat scott.sql | docker exec -i postgres su postgres -c "psql"

In the activity Scott you have many example queries for the Scott database.

Datagrip

You can use Dades - DataGrip to manage the database.

pgAdmin

Install pgAdmin:

docker run -d --name pgadmin --restart=always --network host -e PGADMIN_DEFAULT_EMAIL=postgres@mail.com -e PGADMIN_DEFAULT_PASSWORD=password dpage/pgadmin4:8

Open the pgAdmin web page at http://localhost.

If you haven’t changed the default settings, the user is postgres@mail.com and the password is password.