A collection of notes on PostgreSQL
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';
- PostegreSQL: Up and Running - Practical Guide to the Advanced Open Source Database
- PostgreSQL Indexing - How, why, and when
Table of Contents
- Installing the PostgreSQL client
- Select records within 5 days of current date
- Select rows with missing data in a related table
- Exporting data to a delimited file
- Change the timezone of the database