Sequences in Postgres + Other Goodies

I had an issue at work where we needed to worry about an integer being updated to a certain value. Sequences to the rescue!

This FAQ By Neil Conway answers a lot of good questions. Sequences are psql internal "counters" that can be tied to a particular column in a table. A min, max, and increment value can be set. It can also be cyclic. No need to worry about race conditions as sequences are atomic. They can also be shared across tables.

Creating a Sequence

Here are the basics that cover most of it. Check the link above for more. Sequences can be temporary + options can be set to not error out if it's already existing. There's more.

CREATE SEQUENCE category_id_counter  
INCREMENT 1  
MINVALUE 100  
MAXVALUE 255  
NO CYCLE  
OWNED BY grading_category_x.id;  
Destroying a Sequence
// might not need the single quotes...
drop sequence 'foo';  
ERROR: must be owner of relation

You might run into an error where the sequence to be attached to a table column might require that it be created by the same owner of the table column. This led me to try and become the user who owned the table, similar to su. I had to grant a role to myself:

grant <owner of table> to <current user>  

To reverse/revoke:

revoke all on <table owner> from <current user>;  
Sequence Manipulation Functions
// Check what the current value is
> SELECT * from 

// Increments the sequence and returns the new value
> nextval('foo')

// Set the sequence to a particular value
> setval('foo', 100)

/* Set the sequence to a particular value and set the is_called flag. 
This flag toggles whether the next immediate call to nextval should return the number that was set, OR if it should increment the number and continue. If false, number set will be returned. */  
> setval('foo', 100, false)

Other Goodies

List All Tables Owned by Current User
SELECT *  
FROM pg_tables t  
WHERE t.tableowner = current_user;  
Check Role of Current User
> SELECT * FROM pg_roles;
Check Table Owners
SELECT * FROM pg_tables;  
Get Current User and Current Database
SELECT current_user;  
SELECT current_database();  
List All Columns in a Table
\d+ <table_name>

Can also describe a table with describe <TABLE HERE>;

Change Ownership of a Table
ALTER TABLE public.myname_record OWNER TO <user>;  
Combine Multiple Select Statements
(SELECT result FROM tbl1 LIMIT 1)
UNION ALL  
(SELECT result FROM tbl2 LIMIT 1)