Advance Tables

Generated Columns

• faster than triggers

create table if not exists area (
    w real,
    h real,
    area real GENERATED ALWAYS AS ( w*h ) STORED
);

INSERT INTO area (w, h) values (2,3),(4,7);

select * from area;

update area
set w = 10
where w = 4

select * from area;

Internals

-- size of database 
SELECT 
    datname as db_name,
    pg_size_pretty(pg_database_size(datname)) 
        as database_size
FROM
    pg_database
ORDER BY
    pg_database_size(datname) DESC;

-- list all databases and schema
SELECT 
    catalog_name as "Database Name"
FROM 
    information_schema.information_schema_catalog_name;

-- list all schemas
SELECT 
    catalog_name, schema_name, schema_owner
FROM 
    information_schema.schemata;

-- list all schema starting with pg_...
SELECT *
FROM information_schema.schemata
WHERE schema_name LIKE 'pg%';

-- list all tables
SELECT * 
FROM  information_schema.tables
WHERE table_schema = 'public'

-- list all views
SELECT * 
FROM  information_schema.views
WHERE table_schema = 'public'

-- views from information_schema
SELECT * 
FROM  information_schema.views
WHERE table_schema = 'information_schema'

-- list all columns
SELECT *
FROM information_schema.columns
WHERE table_name = 'orders'

-- look at system metadata
SELECT 
    CURRENT_CATALOG,
    CURRENT_DATABASE(),
    CURRENT_SCHEMA,
    CURRENT_USER,
    SESSION_USER;

-- LOOK AT DATABASE VERSION
SELECT VERSION();

SELECT
    has_database_privilege('learning','CREATE')
    has_schema_privilege('public','USAGE'),
    has_table_privilege('orders','INSERT'),
    has_any_column_privilege('orders','SELECT');

SELECT  current_setting('timezone');

-- show all running queries
SELECT  
    pid,
    age(clock_timestamp(),query_start),
    usename as run_by_user_name,
    query as running
FROM pg_stat_activity
WHERE query != '<IDLE>'
    AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start DESC;

-- show all idle query
SELECT  
    pid,
    age(clock_timestamp(),query_start),
    usename as run_by_user_name,
    query as running
FROM pg_stat_activity
WHERE query = '<IDLE>'
ORDER BY query_start DESC;

-- KILL running query
SELECT pg_cancel_backend(pid);

-- get live and dead rows in table
SELECT
    relname,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables;

-- show location of postgres data directory
show data_directory;

-- show files of a table is located
SELECT pg_relation_filepath('orders');