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;

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    balance NUMERIC(12,2) NOT NULL DEFAULT 0
);

INSERT INTO accounts (name, balance) VALUES
('Alice', 1000.00),
('Bob',   500.00),
('Wally', 300.00);


SELECT * FROM accounts;

trans queries

sql

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');