# Cheatsheet

### Truncate/empty all tables

```sql
SELECT 'TRUNCATE TABLE ' ||  tablename || ' CASCADE;' FROM pg_tables WHERE tableowner='acovix_devl';
```

### Show process / query list

```sql
SELECT * FROM pg_stat_activity;

-- active queries
SELECT pid, age(query_start, clock_timestamp()), usename, query
    FROM pg_stat_activity
    WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;
```

### Kill running query

```sql
SELECT pg_cancel_backend(procpid);

-- kill idle query
SELECT pg_terminate_backend(procpid);
```

### List databases size

```sql
select datname, pg_size_pretty(pg_database_size(datname)) from pg_database order by pg_database_size(datname) desc;
```

### Show all triggers from a schema

```sql
SELECT
    event_object_table,
    trigger_name,
    event_manipulation,
    action_statement,
    action_timing
FROM information_schema.triggers ORDER BY event_object_table, event_manipulation;
```

### Rename database

```sql
alter database old_name rename to new_name;
```

### Set database owner

```sql
alter database sample_name owner to david;
```

### Rename role

```sql
alter role role_name rename to new_role_name;
```

* set password, because after rename password is cleared

### List enum

```sql
select n.nspname as enum_schema,
    t.typname as enum_name,
    string_agg(e.enumlabel, ', ') as enum_value
from pg_type t
    join pg_enum e on t.oid = e.enumtypid
    join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by enum_schema, enum_name
```