Getting Started

Before you can use Redrock Postgres you need to install it, of course. It is possible that PostgreSQL is already installed at your site, either because it was included in your operating system distribution or because the system administrator already installed it. If that is the case, you should uninstall the installed PostgreSQL first, as it may conflict with Redrock Postgres.

If you are installing Redrock Postgres yourself, then refer to Installation Guide for instructions on installation, and return to this guide when the installation is complete. Once you have created a database, you can access it by:

• Running the PostgreSQL interactive terminal program, called psql, which allows you to interactively enter, edit, and execute SQL commands.
• Using a graphical frontend tool such as DBeaver, pgAdmin 4, Navicat for PostgreSQL to access and manage a database.
• Choose a programming language you are good at, and write a custom application based on the PostgreSQL driver corresponding to the language.

You can use the command line tool psql to access the database, for example, execute the following command to access the postgres database:

$ psql postgres

If you do not supply the database name then it will default to your user account name.

In psql, you will be greeted with the following message:

psql (12.1)
Type "help" for help.

postgres=>

The last line could also be:

postgres=#

That would mean you are a database superuser, which is most likely the case if you installed the PostgreSQL instance yourself. Being a superuser means that you are not subject to access controls.

The last line printed out by psql is the prompt, and it indicates that psql is listening to you and that you can type SQL queries into a work space maintained by psql. Try out these commands:

postgres=> SELECT current_date;
    date
------------
 2016-01-07
(1 row)

postgres=> SELECT 2 + 2;
 ?column?
----------
        4
(1 row)

The psql program has a number of internal commands that are not SQL commands. They begin with the backslash character, “\”. For example, to get out of psql, type:

postgres=> \q

and psql will quit and return you to your command shell. (For more internal commands, type \? at the psql prompt.) The full capabilities of psql are documented in psql.

Python is a very popular scripting language. Here we take the Python programming language as an example to briefly introduce how to access the database. Before you start, you will need Python on your computer.

Assume that you already have an up to date version of Python installed, then you should install psycopg. Psycopg is a PostgreSQL adapter for the Python programming language. It is a wrapper for the libpq, the official PostgreSQL client library.

For most operating systems, the quickest way to install psycopg is using the wheel package available on PyPI:

$ pip install "psycopg[binary]"

This will install a pre-compiled binary version of the module which does not require the build or runtime prerequisites. Make sure to use an up-to-date version of pip (you can upgrade it using something like pip install --upgrade pip).

You may then import the psycopg package as usual, start to access database:

import psycopg

# Connect to your postgres DB
conn = psycopg.connect("dbname=postgres user=postgres password=pgpass")

# Open a cursor to perform database operations
cur = conn.cursor()

# Execute a query
cur.execute("SELECT * FROM pg_namespace")

# Retrieve query results
records = cur.fetchall()

More detailed information about psycopg, please refer to Psycopg Documentation.

There are a lot more things to discover. To get more information, please refer to PostgreSQL Documentation.