Simple PostgreSQL Setup

After installing Postgres, we can manually start it using the pg_ctl executable.

pg_ctl -D /usr/local/var/postgres start

The path used above is to define where the database files will live.

To access the postgres CLI with the postgres user:

psql postgres

To access the postgres CLI with the primes_db database:

psql -d primes_db

Create a database:

postgres=# create database atomosdb;
> CREATE DATABASE

Create a user

postgres=# create user atomos_dev with login encrypted password 'picksomepassword';
> CREATE ROLE

Grant privileges to user we just created on database we just created.

postgres=# grant all privileges on database atomosdb to atomos_dev;
> GRANT

Create a table

atomosdb=# CREATE TABLE users(
atomosdb(#   ID INT PRIMARY KEY NOT NULL,
atomosdb(#   USERNAME TEXT NOT NULL,
atomosdb(#   FIRSTNAME TEXT NOT NULL,
atomosdb(#   LASTNAME TEXT NOT NULL,
atomosdb(#   CREATED_ON TIMESTAMPTZ,
atomosdb(#   UPDATED_ON TIMESTAMPTZ
atomosdb(# );
> CREATE TABLE

Create a sequence

atomosdb=# create sequence users_seq START 100;
> CREATE SEQUENCE

Insert a record

atomosdb=# insert into users (id, username, firstname, lastname, created_on, updated_on ) values ( nextval('users_seq'), 'atomosadmin', 'Daniel', 'Marvin', current_timestamp, current_timestamp );
> INSERT 0 1