Postgres Perfected

A collection of notes on PostgreSQL

by Hadley Bradley

I’ve recently been working on a project that uses a PostgreSQL database as the data repository. This document is primarily for myself, to remind me of things that I had learn during the development process.

 Installing the PostgreSQL client

Many people use PGAdmin to administer their PostgreSQL databases. I found it bloated and overkill for my requirements, preferring to stick with the interactive command-line utility psql.

To install psql on Ubuntu 20.04:

sudo apt-get install -y postgresql-client

To connect to a database, launch the command and pass in the hostname, port number and username. You’ll then be prompted to enter the database password for the supplied user.

psql -h localhost -p XXXX -U postgres

To list the tables within a database use the \d command like:

postgres=> \d

 public | lamp90_lab_receipts       | table | postgres
 public | lamp90_lab_receipts_audit | table | postgres
 public | lamp90_results            | table | postgres
 public | lamp90_results_audit      | table | postgres

To list the schema of a given table supply the table name as a parameter to the \d command. Note: you can use tab completion to save typing in the whole table name.

postgres=> \d lamp90_results_audit 

 computer_hostname | character varying(255)      | not null |
 filename          | character varying(255)      | not null |
 submittedDT       | timestamp without time zone | not null |

To run a file containing SQL statements use the \i command followed by the path and file name of the file containing the SQL:

postgres=> \i /home/hadley/input.sql

To output the results of any SQL query you can use the \o command followed by the path and file name of where you want the data to be piped to:

postgres=> \o /home/hadley/output.dat

 Select records within 5 days of current date

In this example we’re selecting rows which have a requested date/time within the last five days.

SELECT *
  FROM pathway
 WHERE requested > current_date - interval '5' day

 Select rows with missing data in a related table

In this example we’re selecting all records within the pathway table which don’t have a corresponding record in the results table using the barcode field as the matching criteria.

SELECT *
  FROM pathway p
   AND NOT EXISTS (
 SELECT
     FROM results
    WHERE barcode = p.barcode
);

 Exporting data to a delimited file

To export data from a table you can use the \copy command of the psql utility. After the \copy command you can specify an SQL query to filter out the records you require. You then designate the file name you wish the data to be extracted to. Lastly, you have some control over the file formatting. For example, which delimiter character should be used between the field values; and if the file should have a header row/record.

\copy (SELECT * FROM path_labs) TO '/path/to/file/path_labs.csv'
 WITH (format csv, header true, delimiter '|');

 Change the timezone of the database

SET TIME ZONE 'Europe/London';
ALTER DATABASE postgres SET timezone TO 'Europe/London';

 Resources


Table of Contents

  1. Installing the PostgreSQL client
  2. Select records within 5 days of current date
  3. Select rows with missing data in a related table
  4. Exporting data to a delimited file
  5. Change the timezone of the database
  6. Resources