Postgres Notes I

7 minute read Published: 2023-06-26

Tutorial notes and additions on Derek Banas' video on Postgres.

Postgres is an open source relational Database Management System.

Installation

Postgres comes installed on some Linux distros like Debian. If not install it by running.

sudo apt install postgresql postgresql-contrib

The -contrib packages includes extensions and additions that are distributed with PostgreSQL but not part of the PostgreSQL core.

Roles

To access psql - the command line client, as the postgres default role.

sudo -u postgres psql

Alternatively, you might want to setup authorization and for this postgres uses a system known as roles

To create a new role in postgres

createuser --interactive

The defaults should work just fine if you are just getting started with postgres.

Output
Enter name of role to add: `jimii`
Shall the new role be a superuser? (y/n) y

I also like to change the auth mode to enable me use postgreSQL clients, like beekeper

Start by editing the config file sudo nano /etc/postgresql/14/main/pg_hba.conf
In my case, i created the jimii role and I want to use password auth, thus I change the config file to look like this

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             jimii                                   scram-sha-256

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

The first column represents the connection type, i.e are you connecting to the db locally or remotely via TCP/IP ...etc

The second column represents the database you want to connect to all.

The third column represents the user that is connecting to the database.

The fourth column represents the addresses where the connection requests might come from.

The last column represents the auth method. scram-sha-256 replaced md5 which was the password auth that was used and replaced due to security concerns.

Creating a Database

To create a db with the user you created above.

sudo -u jimii createdb school

To delete/drop the database you just created above

sudo -u jimii dropdb school

Alternatively you could use perform the operations using psql as demonstrated below.

`psql` usage

To access the psql prompt directly with the created database jimii_dev_test_db with the role jimii

sudo -u jimii psql school

To list local databases

\l

To list the relations/tables on the database you are working on

\dt

To show/describe the table

\d <table name>

To list the custom types of the database, with

\`dT` or  `\dT+` for additional info like values

To quite the psql prompt

\q

DB Operations

_{from the Derek Banas tutorial}

Designing a DB

One table represents one real world object e.g students table, teachers table e.t.c
Columns store one piece of informations. e.g using the students table, you might have name, address.
Think about how different tables will relate to each other, e.g how will a teacher be matched to the students table?
Reduce the use of redundant data.

Creating tables

To create our first relation, in our psql prompt,

CREATE TABLE teachers(
   first_name VARCHAR(30) NOT NULL,
   last_name VARCHAR(30) NOT NULL,
   email VARCHAR(60) NOT NULL,
   date_entered TIMESTAMP NOT NULL,
   birth_date DATE NULL,
   sex CHAR(1) NOT NULL,
   student_id integer REFERENCES students,
   id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
);

NOT NULL is a constraint which will not allow you to insert null values in the specified column.

The last column for the id generates auto-incremented id values. It is similar to writing SERIAL PRIMARY KEY but the verbose nature means that it is conforming to SQL standards.

Check these two articles for a great-writeup on postgres identifiers.

Inserting into Table

To insert values into the table,

INSERT INTO teachers(first_name, last_name, email, date_entered, birth_date, sex, student_id) VALUES ('Christopher', 'Jones', 'christopherjones@bp.com', current_timestamp, '1938-09-11', 'M', 1);

To view everything in the table in an ascending order

SELECT * FROM teachers ORDER BY id ASC

To create a custom type for example sex, and change the type in our relation,

CREATE TYPE sex_type as enum ('M', 'F');

alter table teachers
alter column sex type sex_type USING sex::sex_type;

Foreign and Public Keys

A foreign key is a column or a group of columns in a table that reference the primary key of another table.

There are different ways to do this as described by this stack overflow answer

e.g

CREATE TABLE students
(
  student_id SERIAL PRIMARY KEY,
  first_name TEXT,
  age SMALLINT,
  birth_date DATE,
);

The tests table references the students table column student_id in the student_id column.

CREATE TABLE tests
(
   subject_id SERIAL,
   subject_name text,
   student_id integer REFERENCES students
);

To make changes to an existing table we use the ALTER keyword. The first below adds the last_name and city columns to the students database.

ALTER TABLE students ADD last_name VARCHAR(30), city VARCHAR(256);

To make changes/modify a column, e.g adding the NOT NULL constraint

ALTER TABLE students ALTER COLUMN first_name SET NOT NULL;

To change the name of a column table

ALTER TABLE students RENAME COLUMN first_name TO given_name;

If you want to delete a column

ALTER TABLE students DROP COLUMN given_name;

If you want to rename a table...

ALTER TABLE tests RENAME TO test_scores;

To improve the performance of your database as it grows, you might consider indexing your tables. In a way, it is a form of sorting your data allowing for binary searches to be performed on the records. It speeds up the execution of SELECT and WHERE. The downsides include, additional storage space usage and slowdown of the INSERT and ADD statements.

CREATE INDEX first_name_index ON students(first_name);

To delete all the data in a table

TRUNCATE TABLE students

Meanwhile to delete a table completely

DROP TABLE tests

To change the type of a column, say age in student from integer to smallint,

ALTER TABLE students ALTER COLUMN age SET DATA TYPE smallint;
`or`
ALTER TABLE students ALTER COLUMN age TYPE smallint;

To extract, say the month from a datetime data type,

SELECT * from students where EXTRACT(MONTH FROM birth_date) = 10;

select the birthdate of students who were born in October.

conditional operators and filters

Say we want to find students whose age is greater than 10;

SELECT * FROM students WHERE age > 10;

You can also chain it with conditional operators like AND, OR, NOT

SELECT student_id, name FROM students WHERE age > 10 AND age < 18;

The order by clause causes the fetched data to be returned sorted in ascending order by default, or you can specify for the results to be order descending.

SELECT * FROM students ORDER BY first_name DESC;

The statement above will return data in the students table ordered in a descending format, A - z

You can limit results with the limit clause. The statement below limits the result to five and returns them in ascending order;

SELECT * FROM students ORDER BY first_name LIMIT 5;

You can also return merged results where two columns of a table are returned as one

SELECT CONCAT(first_name, last_name) AS name, age from students where age > 5;

Another useful command is distinct which we can use to eliminate duplicates.

SELECT DISTINCT city FROM students ORDER BY first_name;

If we wanted to exclude a certain city from our results we could do

SELECT DISTINCT city FROM students WHERE city != 'Nairobi' ORDER BY first_name;

To return specified cities in our query above

SELECT DISTINCT city FROM students WHERE city IN('Nairobi', 'Kisumu', Mombasa) ORDER BY first_name;