PostgreSQL handy queries

postgresql.sql

-- Get size of database

SELECT pg_size_pretty(pg_database_size('database-name'));
                                       
-- Set the current session as read only
SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;
                                  
-- Locks
-- https://www.postgresql.org/docs/10/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS
SELECT pg_try_advisory_lock(123123213123) as lockObtained;
SELECT pg_try_advisory_unlock(123123213123);

-- Anonymous functions to verify migrations
do language plpgsql $$
begin
  if (select count(*) from table_name) > 0 then
    raise exception 'Invalid count in table_name';
  end if;
end;
$$;