SQL - Advanced Features

Basic SQL discussed how to store and access data in PostgreSQL.
More advanced features of SQL discussed here

• how to simplify management
• prevent loss or corruption of your data.
• PostgreSQL extensions.

Views

best used for

• a stable API layer
• simplifying complex queries
• security / permission control
• virtual columns / computed fields
• providing backward compatibility

sql

-- creates a view
CREATE OR REPLACE VIEW sf_weather_list AS
SELECT name, location, temp_lo, temp_hi, prcp, cur_date
    FROM weather JOIN cities on city = name
    WHERE city = 'San Francisco';

-- query/use a view like any other table
SELECT name, location, temp_lo, temp_hi, prcp, cur_date
    FROM sf_weather_list;

security / permission control

sql

-- expose only part of a table.
CREATE VIEW public_users AS
SELECT id, name FROM users;


-- Give permissions
GRANT SELECT ON public_users TO user1;

Foreign Keys

sql

-- failed - referenced table(cities), column (name) - no unique restrain
basicsdb=# ALTER TABLE weather ADD FOREIGN KEY (city) REFERENCES cities(name);
ERROR:  there is no unique constraint matching given keys for referenced table "cities"

-- alter table, add primary key to cities, name
basicsdb=# ALTER TABLE cities ADD CONSTRAINT cities_name_pk PRIMARY KEY (name);
ALTER TABLE

-- alter table weather - add foreign key city column, references cities, name
basicsdb=# ALTER TABLE weather ADD FOREIGN KEY (city) REFERENCES cities(name);

-- failed weather contains - 'Hayward' city not in cities,name
ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Hayward) is not present in table "cities".

-- added entry Hayward to cities table
basicsdb=# insert  into cities ("name", "location") values ('Hayward', '123.57,-25.43');
INSERT 0 1

-- success alter table, add foreign key to city, refrencing cities, name
basicsdb=# ALTER TABLE weather ADD FOREIGN KEY (city) REFERENCES cities(name);
ALTER TABLE

basicsdb=# \d cities
                       Table "basics.cities"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 name     | character varying(80) |           | not null |
 location | point                 |           |          |
Indexes:
    "cities_name_pk" PRIMARY KEY, btree (name)
Referenced by:
    TABLE "weather" CONSTRAINT "weather_city_fkey" FOREIGN KEY (city) REFERENCES cities(name)



basicsdb=# \d weather;
                      Table "basics.weather"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 city     | character varying(80) |           |          |
 temp_lo  | integer               |           |          |
 temp_hi  | integer               |           |          |
 prcp     | real                  |           |          |
 cur_date | date                  |           |          |
Foreign-key constraints:
    "weather_city_fkey" FOREIGN KEY (city) REFERENCES cities(name)

basicsdb=#

Transactions

Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

init transdb

sql

DROP DATABASE IF EXISTS transdb;
CREATE DATABASE transdb;
\c transdb;

CREATE SCHEMA test;
SET SEARCH_PATH TO test, public;


-- Drop old tables if they exist (optional)
-- DROP TABLE IF EXISTS accounts CASCADE;
-- DROP TABLE IF EXISTS branches CASCADE;

-------------------------------------
-- 1. Create branches table
-------------------------------------
CREATE TABLE branches (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    balance NUMERIC(12,2) NOT NULL DEFAULT 0
);

-------------------------------------
-- 2. Create accounts table
-------------------------------------
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    branch_id INT NOT NULL REFERENCES branches(id),
    balance NUMERIC(12,2) NOT NULL DEFAULT 0
);


-- bulk inserts
-- good for initializing data base from scratch
-- 10+ branches, thousands of accounts, CSV input
-- Begin transaction
BEGIN;

-- Create temporary staging tables
CREATE TEMP TABLE staging_branches (
    name text,
    balance numeric
);

CREATE TEMP TABLE staging_accounts (
    branch_name text,
    name text,
    balance numeric
);

-- Load CSVs into staging tables
-- Adjust paths as needed
\copy staging_branches(name, balance) FROM 'branches.csv' DELIMITER ',' CSV HEADER;
\copy staging_accounts(branch_name, name, balance) FROM 'accounts.csv' DELIMITER ',' CSV HEADER;

-- Insert branches into main table, capture IDs
-- Use CTE to map branch_name to branch_id
WITH inserted_branches AS (
    INSERT INTO branches(name, balance)
    SELECT name, balance
    FROM staging_branches
    RETURNING id, name
)
-- Insert accounts using the branch IDs
INSERT INTO accounts(name, branch_id, balance)
SELECT a.name, b.id, a.balance
FROM staging_accounts a
JOIN inserted_branches b ON a.branch_name = b.name;

-- Commit transaction
COMMIT;




-------------------------------------
-- 3. Insert branch data
-------------------------------------

-- 4. Insert account data
-------------------------------------
-- insert using CTE
/*
WITH BranchA AS (
    INSERT INTO branches(name, balance)
    VALUES ('BranchA', 5000)
    RETURNING id
)
INSERT INTO accounts(name, branch_id, balance)
SELECT * FROM (
    SELECT 'Alice', id, 100.00 FROM BranchA
    UNION ALL
    SELECT 'Wally', id, 200.00 FROM BranchA
) AS t;


WITH BranchB AS (
    INSERT INTO branches(name, balance)
    VALUES ('BranchB', 5000)
    RETURNING id
)
INSERT INTO accounts(name, branch_id, balance)
SELECT * FROM (
    SELECT 'Mark', id, 100.00 FROM BranchB
    UNION ALL
    SELECT 'John', id, 200.00 FROM BranchB
) AS t;

*/

-- insert using INSERT ... VALUES

/*

DO $$
DECLARE
    branch_a_id INT;
    branch_b_id INT;
BEGIN
    INSERT INTO branches(name, balance)
    VALUES ('BranchA', 5000)
    RETURNING id INTO branch_a_id;

    INSERT INTO accounts(name, branch_id, balance)
    VALUES ('Alice', branch_a_id, 100.00),
           ('Wally', branch_a_id, 200.00);

    INSERT INTO branches(name, balance)
    VALUES ('BranchB', 5000)
    RETURNING id INTO branch_b_id;

    INSERT INTO accounts(name, branch_id, balance)
    VALUES ('Alice', branch_b_id, 100.00),
           ('Wally', branch_b_id, 200.00);
END $$;


*/

/*

BEGIN;

-- Insert branch and capture returned id
-- make sure there is no semicolon after the SQL --
-- gset is the one that executes the statement
-- and captures the return into a variable
INSERT INTO branches(name, balance)
VALUES ('BranchA', 5000)
RETURNING id
\gset

-- Use the returned id in subsequent inserts
INSERT INTO accounts(name, branch_id, balance) VALUES
('Alice', :id, 100.00),
('Wally', :id, 200.00);

COMMIT;


*/

/*
-- for a different variable name

INSERT INTO branches(name, balance)
VALUES ('BranchA', 5000)
RETURNING id AS branch_id
\gset

-- Now you can use :branch_id
INSERT INTO accounts(name, branch_id, balance) VALUES
('Alice', :branch_id, 100.00),
('Wally', :branch_id, 200.00);



*/



/*

Long id = jdbcTemplate.queryForObject(
    "INSERT INTO branches(name) VALUES (?) RETURNING id",
    Long.class,
    "New Branch"
 );

 jdbcTemplate.update(
    "INSERT INTO accounts(name, branch_id, balance) VALUES (?, ?, ?)",
    "Mark", id, 500.00
 );

*/

-------------------------------------
-- 5. Show initial data
-------------------------------------
SELECT * FROM accounts;
SELECT * FROM branches;

sample csv data files

branches

branches.csv

name,balance
BranchA,5000
BranchB,3000

accounts

accounts.csv

branch_name,name,balance
BranchA,Alice,100
BranchA,Wally,200
BranchB,Mark,100
BranchB,John,200
BranchB,Charlie,250

transaction queries

transaction.sql

BEGIN;
    UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
    UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name =
    'Alice');
    UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
    UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name =
    'Bob');
COMMIT;