Linux/Postgres
< Linux
Data types
| Type | min | max | comment |
|---|---|---|---|
| boolean | |||
| char(n) | |||
| varchar(n) | |||
| text | |||
| smallint | -32768 | 32767 | |
| int | |||
| serial | (~auto_increment in mysql) | ||
| float(n) | |||
| real / float8 | |||
| numeric(p,s) | |||
| date | |||
| time | |||
| timestamp | |||
| timestamptz | |||
| interval | |||
| json | |||
| jsonb | |||
| uuid | |||
| + special |
Databases
- List all databases
SELECT datname FROM pg_database WHERE datistemplate = false;
Tables
- List all tables
SELECT table_catalog,table_schema,table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_type, table_name
- Biggest tables by size
SELECT
nspname || '.' || relname AS "Object Name", relkind As "Object Type",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
List all users
SELECT * FROM pg_user;