PostgreSQL Cheatsheet

postgresql-cheatsheet.sql

-- Set a Sequence to a specific value
select setval('address_seq', 1, true);

-- Show last used sequence value
SELECT last_value FROM my_sequence_name;

-- Show text from a TEXT datatype column: http://www.solewing.org/blog/2015/08/hibernate-postgresql-and-lob-string/
SELECT
  convert_from(loread(
    lo_open(my_large_text::int, x'40000'::int), x'40000'::int), 'UTF-8'
  ) AS my_large_text
FROM my_table_with_large_text

-- Estimated row count
SELECT reltuples::bigint AS estimate FROM pg_class where relname='tablename';

-- Number of connections
SELECT sum(numbackends) FROM pg_stat_database;

-- Active queries
SELECT * FROM pg_stat_activity WHERE state = 'active';

-- Cancel query
SELECT pg_cancel_backend(id);

-- DB stats
SELECT * FROM pg_stat_database;

-- Transactions that have a lock on the table and when they started
SELECT pid, a.state, a.xact_start
FROM pg_locks AS l
         JOIN pg_stat_activity AS a USING (pid)
WHERE l.relation = 'tablename'::regclass;

-- Create table with default UUID as primary key
CREATE TABLE IF NOT EXISTS product (
  id uuid DEFAULT gen_random_uuid() PRIMARY KEY
);

-- Create role
CREATE ROLE <role-name> LOGIN PASSWORD '<password>';

-- Change owner of database
ALTER DATABASE <database> OWNER TO <role>;

-- Change owner of table
ALTER TABLE <table> OWNER TO <role>;