I decided to take a closer look at various lower-level technologies to improve my general knowledge about programming and the web. I used to work a lot with high-level abstractions, meta-frameworks, systems, and libraries like Next.js, GCP, TypeORM, or GraphQL. They greatly increase development speed and offer a lot of functionality out of the box, but they also keep me from gaining a deep understanding of how things like browser APIs, databases, servers, or networking work.

I will use this series to write about things that I have learned about PostgreSQL while reading ‘Practical SQL’ by Anthony DeBarros. I will also try to work a bit with the GADM spatial data and perhaps create a simple geocoding REST API.

But first, let’s take a look at some basic SQL operations.

Tables

It’s important to understand that a relational database is not just a collection of data stored in tables; it also includes functions, indexes, triggers, and more.

I like to start my experiments with PostgreSQL by creating a docker compose file. That way docker will handle pulling the image and running it.

If you are not using a Docker image, you will need to install PostgreSQL, run the server, and create a database.

CREATE DATABASE my_database;

Tables are created with the CREATE TABLE keyword followed by comma-separated column names and types. SQL enforces the integrity of data by assigning a data type to each column.

CREATE TABLE teachers (
    id bigserial,
    name varchar(30),
    salary numeric,
    hired_date date,
);

bigserial is a special datatype that auto-increments every time a new row is added.

Insert data into the table

Data insertion is accomplished using the INSERT command. You need to specify the table name, the order of inserted values, and provide the values in the correct order, as demonstrated below:

INSERT INTO teachers (name, salary, hired_date)
VALUES ('tom', 2000, '2012-04-22'),
       ('mat', 3000, '2009-04-22');

id field is omitted since it will be automatically generated.

Table Schema

If you need to examine table schema, you can do so using TABLEcommand followed by table name.

TABLE table_name;

Querying

Get all columns from a table.

SELECT * FROM table_name;

Get selected columns in arbitrary order.

SELECT column_b, column_g, column_a FROM table_name;

Sort with ORDER BY

ASC – ascending ( default )

DESC – descending

SELECT column_name_a, column_name_b 
FROM table_name
ORDER BY column_name_c DESC;

You can pass number to ORDER BY clause to target a columns listed after SELECT keyword

// order by col_b
SELECT col_a, col_b
FROM my_table
ORDER BY 2; 

Order by multiple columns

You can use this technique to e.g. find teacher who earns highest salary for each school

SELECT teacher_name, school_name, salary
FROM teachers
ORDER BY school_name ASC, salary DESC;

Find unique with DISTINCT

Use DISTINCT to find unique values in a column. You can pass multiple columns to the DISTINCT query. This allows you to ask questions such as, ‘For each x value in the table, what are all the y values?‘ For example, ‘What are all the types of products produced in each factory?’ or ‘What are all the courses in each school?

SELECT DISTINCT course, school
FROM schools;

Filter results with WHERE

SELECT * FROM teachers
WHERE school = 'Some School Name';

Here is quick overview of available comparison characters.

=	equal to
!= , <>	not equal to	WHERE school_name <> ‘some school name’;
<	grater than
>	smaller than
<=
>=
BETWEEN	within range ( inclusive )	WHERE salary BETWEEN 4000 AND 30000;
IN	match one of a set of values	WHERE last_name IN (‘tom’, ‘mat’);
LIKE	match pattern ( case sensitive )	WHERE first_name LIKE ‘Sam%’;
ILIKE	match pattern ( case insensitive )	WHERE first_name ILIKE ‘sam%’;
NOT	negates condition

!= is not a standard ANSI SQL character.

Using WHERE with LIKE and ILIKE

• LIKE 'b%'; finds all items that start with lowercase b followed by any number of characters
• LIKE 'ba_ker'; finds items that start with ‘ba’ and end with ‘ker’ and have one character in between.

Using LIKE or ILIKE has negative impact on performance.

Using OR and AND

Use OR and AND to queries composed from multiple clauses

SELECT * 
FROM teachers
WHERE school = 'some school name'
            OR (salary > 2000 AND salary < 4000 );

Remember to use parenthesis to ensure correct order of AND and OR statements.