Skip to main content

PostgreSQL Relational Database example

how to run

  • Install postgresql

    brew install postgresql

  • Start postgresql server

    brew services start postgresql

  • Create database

    createdb database_name

  • Load sql file into database

    psql -d database_name -a -f tables.sql

  • Connect to database

    psql -d database_name

  • Run queries from queries.sql by copy and paste

Atomicity example

  • open 2 terminals, both connected to the same database
  • 1 starts the BEGIN TRANSACTION while the other does not
  • after updating the one that has began transaction, the other does not reflect the results
    • this is because COMMIT has not been initiated and thus the transaction has not been fully completed, displaying the atomicity feature

Isolation example

  • open 2 terminals, both connected to the same database
  • both start the BEGIN TRANSACTION
  • update the 1st terminal, then try updating the 2nd terminal
    • the 1st terminal will be updated successfully
    • the 2nd terminal will hang
    • this means that transactions could be run concurrently, but effectively they will be executed sequentially
  • 2nd terminal will only complete after the 1st terminal perform a COMMIT;

SQL code examples

Tables

CREATE TABLE payments (
customer_name varchar(128),
processed_at date,
amount int
);

CREATE TABLE balances (
username varchar(128),
balance int
);

CREATE TABLE large_table (
random_int int
);

INSERT INTO payments VALUES ('clement', '2019-12-15', 10);
INSERT INTO payments VALUES ('antoine', '2020-01-01', 100);
INSERT INTO payments VALUES ('clement', '2020-01-02', 10);
INSERT INTO payments VALUES ('antoine', '2020-01-02', 100);
INSERT INTO payments VALUES ('antoine', '2020-01-03', 100);
INSERT INTO payments VALUES ('simon', '2020-02-05', 1000);
INSERT INTO payments VALUES ('antoine', '2020-02-01', 100);
INSERT INTO payments VALUES ('clement', '2020-02-03', 10);
INSERT INTO payments VALUES ('meghan', '2020-01-12', 80);
INSERT INTO payments VALUES ('meghan', '2020-01-13', 70);
INSERT INTO payments VALUES ('meghan', '2020-01-14', 90);
INSERT INTO payments VALUES ('alex', '2019-12-11', 10);
INSERT INTO payments VALUES ('clement', '2020-02-01', 10);
INSERT INTO payments VALUES ('marli', '2020-01-18', 10);
INSERT INTO payments VALUES ('alex', '2019-12-15', 10);
INSERT INTO payments VALUES ('marli', '2020-01-25', 10);
INSERT INTO payments VALUES ('marli', '2020-02-02', 10);

INSERT INTO balances VALUES ('antoine', 0);
INSERT INTO balances VALUES ('clement', 1000);

INSERT INTO large_table (random_int)
SELECT round(random() * 1000000000)
FROM generate_series(1, 50000000) s(i);

Queries

/*
Powerful Queries
*/

-- Sum the number of payments for each user.
SELECT customer_name, count(*)
FROM payments
GROUP BY customer_name
ORDER BY count DESC;

-- Sum the payment amounts for each month.
SELECT sum(amount), extract(year from processed_at) as year, extract(month from processed_at) as month
FROM payments
GROUP BY month, year
ORDER BY sum DESC;

-- Sum the payment amounts for each month for each user.
SELECT customer_name, sum(amount), extract(year from processed_at) as year, extract(month from processed_at) as month
FROM payments
GROUP BY customer_name, month, year
ORDER BY customer_name DESC;

-- Find the largest single-user payments for each month.
SELECT max(amount), year, month
FROM (
SELECT customer_name, sum(amount) as amount, extract(year from processed_at) as year, extract(month from processed_at) as month
FROM payments
GROUP BY customer_name, month, year
) AS monthly_sums
GROUP BY year, month;

/*
Transaction
*/

-- Transfer 100 from Clement to Antoine.
BEGIN TRANSACTION;
UPDATE balances SET balance = balance - 100 WHERE username = 'clement';
UPDATE balances SET balance = balance + 100 WHERE username = 'antoine';
COMMIT;

/*
Indexes

demonstrates database indexes
*/

-- Find the 10 largest ints.
SELECT * FROM large_table ORDER BY random_int DESC LIMIT 10;

-- Create an index on the ints in the table.
CREATE INDEX large_table_random_int_idx ON large_table(random_int);