Code snippets

  • List the size of PostgreSQL databases

      select schemaname as table_schema,
             relname as table_name,
             pg_size_pretty(pg_relation_size(relid)) as data_size
      from pg_catalog.pg_statio_user_tables
      order by pg_relation_size(relid) desc;
  • From StackOverflow - Postgres Manually Modify Sequence

    Re-build a SEQUENCE

    SELECT setval(pg_get_serial_sequence('table_name', 'id'), coalesce(max(id), 0)+1 , false) FROM table_name;
  • Set the schema for the psql command using PGOPTIONS:

    PGOPTIONS="--search_path=${schema_name}" psql -U postgres -h localhost "$db_name" -f "$sql_file"
  • pg-shortkey - a trigger that allows you to use YouTube-like short keys as a primary key