Crash course on SQL with PostgreSQL

Here's a deep dive into the basics of SQL with PostgreSQL. These notes will walk you through the essential commands and concepts to get you started on your PostgreSQL adventure.

Quick start with Docker

Before we delve into PostgreSQL specifics, let's quickly set up a PostgreSQL container using Docker. Docker allows you to run PostgreSQL in a self-contained environment. Here's how you can do it:

docker run -e POSTGRES_PASSWORD=lol --name=pg --rm -d -p 5432:5432 postgres:14

• -e POSTGRES_PASSWORD=lol: Set the PostgreSQL password to "lol."
• --name=pg: Name the container "pg."
• --rm: Automatically remove the container when it's stopped.
• -d: Run Docker in the background.
• -p 5432:5432: Map port 5432 on your machine to port 5432 in the container.
• postgres:14: Use the PostgreSQL version 14 image.

You can check if the container is running using:

docker ps

To stop the container and remove it along with its storage, use:

docker kill pg

Now that you have PostgreSQL up and running, let's explore some fundamental PostgreSQL commands.

Connecting to PostgreSQL

To connect to your PostgreSQL instance, you can use the following command:

docker exec -u postgres -it pg psql

This command connects to the "pg" container with the user "postgres" and opens an interactive session using the psql command-line tool.

PostgreSQL Basics

Creating a Database

Let's create a new database called "recipeguru":

CREATE DATABASE recipeguru;

Creating Tables

You can create tables to organize your data. Here's an example of creating a table named "ingredients":

CREATE TABLE ingredients (  
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,  
    title VARCHAR(255) UNIQUE NOT NULL   
);

In this table, "id" is an automatically generated primary key, and "title" is a unique, non-null text field.

Inserting Data

To insert data into the "ingredients" table, you can use the INSERT INTO command:

INSERT INTO ingredients (title) VALUES ('bell pepper');

Make sure to end SQL statements with a semicolon.

Updating Data

You can update existing records using the UPDATE statement. For example:

UPDATE ingredients SET image = 'strawberry.jpg' WHERE title = 'watermelon';

Deleting Data

To delete records, you can use the DELETE FROM statement:

DELETE FROM ingredients WHERE image='delete.jpg' RETURNING *;

Querying Data

You can query data using the SELECT statement:

SELECT title, type FROM ingredients LIMIT 10 OFFSET 10;

For better pagination, consider using:

SELECT id, title, type FROM ingredients WHERE id > 22 LIMIT 10;

Advanced Querying

PostgreSQL offers powerful querying capabilities, including:

• Pattern matching with LIKE.
• Case-insensitive matching with ILIKE.
• Combining columns with CONCAT.
• Aggregation functions like COUNT.
• Joins to combine data from multiple tables.

.png)

Working with Tables

Table Operations

• \d: List all tables.
• \d <table>: List columns for a specific table.

Creating Unique Constraints

You can create a unique constraint on a column to ensure uniqueness:

ALTER TABLE ingredients ADD CONSTRAINT unique_title UNIQUE (title);

Adding and Dropping Columns

To add a new column to an existing table:

ALTER TABLE ingredients ADD COLUMN image VARCHAR(255);

To drop a column:

ALTER TABLE ingredients DROP COLUMN image;

Enumerated Types

You can add an enum check constraint to a column:

ALTER TABLE ingredients  
ADD CONSTRAINT type_enums  
CHECK (type IN ('meat', 'vegetable', 'fruit', 'other'));

Using DISTINCT

To retrieve unique values, use DISTINCT:

SELECT DISTINCT type FROM ingredients;

Working with JSONB

PostgreSQL supports JSONB (binary JSON) data type for storing structured data. You can perform various operations on JSONB data:

• -> to get a value from inside a JSON object.
• --> to get a string for formatting purposes.

SELECT meta -> 'tags' -> 0 FROM recipes WHERE meta IS NOT NULL;

You can also check if a JSONB object contains a specific key:

WHERE meta -> 'tags' ? 'cake';

Advanced Database Techniques

Indexing

Indexes can significantly improve query performance. You can create indexes on columns to speed up data retrieval. For example:

CREATE INDEX idx_name ON movies(name);

Indexes are especially useful for columns used in WHERE clauses and JOIN conditions.

Materialized Views

Materialized views are like cached queries. They store the result of a query and can be refreshed to update the data. They're excellent for improving query performance:

CREATE MATERIALIZED VIEW actor_categories AS
SELECT ...
FROM ...
WITH NO DATA;

Refresh a materialized view using:

REFRESH MATERIALIZED VIEW actor_categories;

Subqueries

Subqueries allow you to run a query within another query. While they are powerful, it's essential to optimize them for performance. Use subqueries when necessary but prefer JOINs for readability.

Transactions

Transactions ensure data consistency by grouping multiple SQL statements into one unit. They are especially useful when dealing with multiple database operations that must succeed or fail together.

Window Functions

Window functions allow you to perform calculations across a set of rows related to the current row. They can be incredibly powerful for advanced analytics and reporting.

Self Joins

Self joins are used when you need to join a table to itself. This is helpful for hierarchical data or when comparing records within the same table.

Happy Postgres-ing!