Running PostgreSQL

Docker

Docker Desktop

To start a (detached) Postgres Docker Container

docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

To directly go into pqsl

docker run -it --rm postgres psql -h some-postgres -U postgres

Docker Compose

To run PostgreSQL using Docker Compose, create a file:

1
2
3
4
5
6
7
version: '3.1'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example

AWS RDS

Database Connection

Database/Table Management

Creating/Deleting Databases

Create Database

CREATE DATABASE suppliers;

Delete Database

Creating/Deleting Tables

Create Table

1
2
3
4
5
6
CREATE TABLE [IF NOT EXISTS] table_name (
   column1 datatype(length) column_contraint,
   column2 datatype(length) column_contraint,
   column3 datatype(length) column_contraint,
   table_constraints
);

Delete Table

1
2
DROP TABLE [IF EXISTS] table_name 
[CASCADE | RESTRICT];

CASCADE:
RESTRICT:

Altering Tables

?

Data Types

Constraints

NOT NULL

ensures that values in a column cannot be NULL.

UNIQUE

ensures the values in a column unique across the rows within the same table.

PRIMARY KEY

a primary key column uniquely identify rows in a table. A table can have one and only one primary key. The primary key constraint allows you to define the primary key of a table.

CHECK

a CHECK constraint ensures the data must satisfy a boolean expression.

1
2
3
4
CREATE TABLE person (
	id SERIAL PRIMARY KEY,
	birth_date DATE CHECK (birth_date > '1900-01-01'),
);

FOREIGN KEY

ensures values in a column or a group of columns from a table exists in a column or group of columns in another table. Unlike the primary key, a table can have many foreign keys.

Querying

Select

Filtering

Joins

Inner Joins

1
2
3
4
5
6
7
8
9
SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
INNER JOIN basket_b
    ON fruit_a = fruit_b;

Left Join

1
2
3
4
5
6
7
8
SELECT
	pka,
	c1,
	pkb,
	c2
FROM
	A
LEFT JOIN B ON pka = fka;

PostgreSQL

.