postgres-world-db

Example Database for PostgreSQL : World DB

Database script downloaded at : http://pgfoundry.org/frs/?group_id=1000150&release_id=366#world-world-1.0-title-content

See also PostgreSQL Sample Databases.

Database details

Important note : from version 2.0, tables and columns names use snake case. This version is incompatible with version 1.x.

Default parameters

• database : world-db
• user : world
• password : world123

Schema

• public

Tables

This database contains 4 tables :

• city
• country
• country_language
• country_flag

Relationships

• country_language -> country (country_language_country_code_fkey)
• city -> country (country_fk)
• country -> city (country_capital_fkey)

Run a Docker container

You can run a Docker container with this command (replace xxxx by your local port) :

docker run -d -p xxxx:5432 ghusta/postgres-world-db:2.11

PostgreSQL configuration

If you need to inspect the PostgreSQL server configuration, you can print this file : /var/lib/posgtresql/data/postgresql.conf.

All settings are documented here : https://www.postgresql.org/docs/current/runtime-config.html

With Docker, you can run :

docker exec <my-container-name> cat /var/lib/postgresql/data/postgresql.conf

Log all statements

To log all statements, you can activate this line in the configuration :

log_min_duration_statement = 0

Log categories of statements

You can also log only some categories of statements with log_statement.

Valid values are none, ddl, mod, all. Default is none.

See details : https://www.postgresql.org/docs/current/runtime-config-logging.html

Test it

With the psql CLI command

docker exec -it <container_name> psql -d world-db -U world

Then try a command, like :

List of relations

psql (15.0 (Debian 15.0-1.pgdg110+1))
Type "help" for help.

world-db=# \d
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+-------
 public | city             | table | world
 public | country          | table | world
 public | country_flag     | table | world
 public | country_language | table | world
(4 rows)

List of schemas

world-db=# \dn
List of schemas
  Name  | Owner
--------+-------
 public | world
(1 row)

Describe the city table

world-db=# \d+ city
...

A simple query

world-db=# select * from city limit 10;
  1 | Kabul          | AFG          | Kabol         |    1780000
  2 | Qandahar       | AFG          | Qandahar      |     237500
  3 | Herat          | AFG          | Herat         |     186800
  4 | Mazar-e-Sharif | AFG          | Balkh         |     127800
  5 | Amsterdam      | NLD          | Noord-Holland |     731200
  6 | Rotterdam      | NLD          | Zuid-Holland  |     593321
  7 | Haag           | NLD          | Zuid-Holland  |     440900
  8 | Utrecht        | NLD          | Utrecht       |     234323
  9 | Eindhoven      | NLD          | Noord-Brabant |     201843
 10 | Tilburg        | NLD          | Noord-Brabant |     193238

With Java

Plain Java with JDBC

First add the PostgreSQL JDBC Driver, with Maven for example : org.postgresql » postgresql.

Then execute a test query with :

package org.example;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcDemo {

    public static void main(String[] args) {
        String jdbcUrl = "jdbc:postgresql://localhost:5432/world-db";
        String user = "world";
        String password = "world123";

        try (Connection conn = DriverManager.getConnection(jdbcUrl, user, password)) {
            String query = "select count(*) from city";
            try (Statement stmt = conn.createStatement()) {
                ResultSet rs = stmt.executeQuery(query);
                while (rs.next()) {
                    int count = rs.getInt(1);
                    System.out.println("Count = " + count);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

Spring Boot

Initialize a Spring Boot project with dependencies PostgreSQL Driver and Spring Data JPA (or Spring Data JDBC if you want to keep simple).

Add these properties in application.properties :

• spring.datasource.url=jdbc:postgresql://localhost:5432/world-db
• spring.datasource.username=world
• spring.datasource.password=world123

Then just add JPA entities for each table (City, Country), and interfaces for each DAO which extends JpaRepository.

With Python

Install psycopg 3, a PostgreSQL database adapter for Python.

pip install psycopg

Then create a connection...

import psycopg

DB_HOST = "localhost"
DB_PORT = "5432"
DB_NAME = "world-db"
DB_USER = "world"
DB_PASS = "world123"

with  psycopg.connect(dbname=DB_NAME, user=DB_USER, password=DB_PASS, host=DB_HOST, port=DB_PORT) as conn:

    with conn.cursor() as cur:
        cur.execute("select count(*) from city")
        row = cur.fetchone()
        print('Count = ', row[0])

Alternatives

You can find alternative DBMS or databases examples at this page.