PostgreSQL

Creating a Database for Java Development

For Arch Linux.

initdb --encoding=UNICODE --pgdata=pgdata  --username postgres

The postgres user will be added as the administrator with an empty password.

pgdata is the name of the directory that will hold the database files.

The JDBC driver requires some form of Unicode encoding.

This will also create the default database named postgres.

Starting PostgreSQL

pg_ctl --pgdata=pgdata --log=postgresql.log --options="-k /tmp" start

The -k is necessary to redirect the lock/socket files somewhere that you have write access to. The default is /run/postgres which normal users don't have write permissions for.

Creating Users/Roles

createuser --createdb --interactive --host="localhost" --username postgres --echo

The interactive mode will prompt for remaining details (such as role name, if user should be superuser, or be allowed to create new roles).

Creating New Databases

The default postgres database is created by initdb when the cluster is first created. To create additional databases:

Via SQL:

CREATE DATABASE newdb OWNER owner;

From command line:

createdb newdb --username postgres --owner owner --host="localhost" --echo

The -h points to the directory that contains the lock/socket files used when starting the server.

Status/Stopping PostgreSQL

pg_ctl -D pgdata --options "-k /tmp"  status
pg_ctl -D pgdata --options "-k /tmp"  stop

JDBC URL

Long form:

jdbc:postgresql://localhost:5432/postgres

postgres is the database name.

Short form:

jdbc:postgresql:

localhost and 5432 are defaults. By default it will try to connect to a database that matches the username.

disableColumnSanitiser

It can be added to the connection string as a query parameter:

jdbc:postgresql://localhost:5432/postgres?disableColumnSanitiser=true

The disableColumnSanitiser property looks like it might be a solution to the annoying "lowercase column names" issue that makes it hard to switch between PostgresQL and other databases (pretty much all of which return uppercase column names). From the docs:

Setting this to true disables column name sanitiser. The sanitiser folds columns in the resultset to lowercase. The default is to sanitise the columns (off).

Need to look into this more - so far not so useful - it might only suppress the lowercasing of quoted column names.