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.
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;
// might not need the single quotes... drop sequence 'foo';
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>
revoke all on <table owner> from <current user>;
// 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)
SELECT * FROM pg_tables t WHERE t.tableowner = current_user;
> SELECT * FROM pg_roles;
SELECT * FROM pg_tables;
SELECT current_user; SELECT current_database();
Can also describe a table with
describe <TABLE HERE>;
ALTER TABLE public.myname_record OWNER TO <user>;
(SELECT result FROM tbl1 LIMIT 1) UNION ALL (SELECT result FROM tbl2 LIMIT 1)