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.
Important note : from version 2.0, tables and columns names use snake case. This version is incompatible with version 1.x.
- database : world-db
- user : world
- password : world123
- public
This database contains 4 tables :
- city
- country
- country_language
- country_flag
- country_language -> country (country_language_country_code_fkey)
- city -> country (country_fk)
- country -> city (country_capital_fkey)
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.10
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
To log all statements, you can activate this line in the configuration :
log_min_duration_statement = 0
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
docker exec -it <container_name> psql -d world-db -U world
Then try a command, like :
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)
world-db=# \dn
List of schemas
Name | Owner
--------+-------
public | world
(1 row)
world-db=# \d+ city
...
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
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();
}
}
}
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
.
Install psycopg, 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])
You can find alternative DBMS or databases examples at this page.