Simple user/database setup for PostgreSQL

commands_postgres.sql

-- 
-- Reset auto increment
-- 
ALTER SEQUENCE {table}_{column}_seq RESTART WITH 1;
--
TRUNCATE TABLE table RESTART IDENTITY;

commands_psql.sh

mysql: SHOW TABLES
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;

mysql: SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

mysql: DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

psqlrc

#
# https://www.digitalocean.com/community/tutorials/how-to-customize-the-postgresql-prompt-with-psqlrc-on-ubuntu-14-04
#
\set PROMPT1 '%M:%> %n@%/%R%#%x '
\set COMP_KEYWORD_CASE upper

setup_postgres.sql

--
-- Create new user.
-- Create new database.
-- Assign the user to the database.
--
CREATE USER username WITH PASSWORD 'password';
CREATE DATABASE newdb TEMPLATE template0 ENCODING 'UNICODE';
ALTER DATABASE newdb OWNER TO username;
GRANT ALL PRIVILEGES ON DATABASE newdb TO username;