Table of Contents

PosgreSQL QuickRef

PostgreSQL quick reference.

Create Role & DB

CREATE DATABASE <dbname>;
CREATE ROLE <rolename>;
ALTER USER <rolename> WITH PASSWORD '<password>';
ALTER USER <rolename> WITH LOGIN;
ALTER DATABASE <dbname> OWNER TO <rolename>;
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <rolename>;

Reset Serial Count

-- Reset sequence
ALTER SEQUENCE seq RESTART WITH 1;
 
-- update column to reference sequence
-- (not needed if the column in question is already
-- referenced by the sequence)
UPDATE t SET idcolumn=NEXTVAL('seq');

Add/Drop Constraint

Postgres names constraints on table creation in the following format: (table name)_(column name)_(constraint type). 

ALTER TABLE <table> ADD CONSTRAINT <constraint_name> <constraint_expression>;
ALTER TABLE <table> DROP CONSTRAINT <constraint_name>;