PostgreSQL Cheat Sheet

        -- Insert data from another table.
        insert into persons from tmp_persons;

        -- Update with data from another table.
        update orders set email = p.email from persons p where user_id = p.id;

        -- Upsert - insert or update conflicting records.
        -- EXCLUDED can be used to reference the new values.
        insert into persons from tmp_persons
                on conflict(id) update set name = EXCLUDED.name;

        -- Delete with joining another table. 
        delete from persons p using orders o where o.customer_id = p.id;

          -- Export data from withing ~psql~:
          \copy (SELECT * FROM persons) to 'persons.csv' with csv;

          -- Import data from a CSV to a given table:
          \copy persons from 'persons.csv' with csv;

          -- Get information about running vacuum jobs:
          select * from pg_stat_progress_vacuum;

          -- Find tables with the highest transaction IDs:
          select c.relnamespace::regnamespace as schema_name, c.relname as table_name,
          greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
          2^31 - 1000000 - greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as remaining
          from pg_class c left join pg_class t on c.reltoastrelid = t.oid
          where c.relkind in ('r', 'm') order by remaining;

          -- Or this even more extensive query from the Timescale blog:
          with overridden_tables as (
          select
          pc.oid as table_id,
          pn.nspname as scheme_name,
          pc.relname as table_name,
          pc.reloptions as options
          from pg_class pc
          join pg_namespace pn on pn.oid = pc.relnamespace
          where reloptions::text ~ 'autovacuum'
          ), per_database as (
          select
          coalesce(nullif(n.nspname || '.', 'public.'), '') || c.relname as relation,
          greatest(age(c.relfrozenxid), age(t.relfrozenxid)) as age,
          round(
          (greatest(age(c.relfrozenxid), age(t.relfrozenxid))::numeric *
          100 / (2 * 10^9 - current_setting('vacuum_freeze_min_age')::numeric)::numeric),
          2
          ) as capacity_used,
          c.relfrozenxid as rel_relfrozenxid,
          t.relfrozenxid as toast_relfrozenxid,
          (greatest(age(c.relfrozenxid), age(t.relfrozenxid)) > 1200000000)::int as warning,
          case when ot.table_id is not null then true else false end as overridden_settings
          from pg_class c
          join pg_namespace n on c.relnamespace = n.oid
          left join pg_class t ON c.reltoastrelid = t.oid
          left join overridden_tables ot on ot.table_id = c.oid
          where c.relkind IN ('r', 'm') and not (n.nspname = 'pg_catalog' and c.relname <> 'pg_class')
          and n.nspname <> 'information_schema'
          order by 3 desc)
          SELECT *
          FROM per_database;

          SELECT
          pid,
          user,
          pg_stat_activity.query_start,
          now() - pg_stat_activity.query_start AS query_time,
          query,
          state,
          wait_event_type,
          wait_event
          FROM pg_stat_activity
          WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

          SELECT pg_terminate_backend(pid)
          FROM pg_stat_activity
          WHERE pid = <pid>
          AND pid != pg_backend_pid();

          SELECT
          pss.userid,
          pss.dbid,
          pd.datname as db_name,
          round((pss.total_exec_time + pss.total_plan_time)::numeric, 2) as total_time,
          pss.calls,
          round((pss.mean_exec_time+pss.mean_plan_time)::numeric, 2) as mean,
          round((100 * (pss.total_exec_time + pss.total_plan_time) / sum((pss.total_exec_time + pss.total_plan_time)::numeric) OVER ())::numeric, 2) as cpu_portion_pctg,
          substr(pss.query, 1, 200) short_query
          FROM pg_stat_statements pss, pg_database pd
          WHERE pd.oid=pss.dbid
          ORDER BY (pss.total_exec_time + pss.total_plan_time)
          DESC LIMIT 30;

          SELECT *
          FROM pg_available_extensions
          WHERE
          name = '<extension-name>' and
          installed_version is not null;
          If the table is empty, create the extension:

          CREATE EXTENSION <extension-name>;