# Internals

## Advance Tables

#### Generated Columns

* faster than triggers

```sql
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

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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://dev117uday.gitbook.io/databases/sql/advance-tables/internals.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.