Postgres Cheatsheet

commands.sh

# Postgres database as docker container
docker run -p 5432:5432 --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

# creating a table with auto increment primary key prior to PostgreSQL 10
CREATE TABLE category (
  ID SERIAL PRIMARY KEY,
  DESCRIPTION VARCHAR(255)
);

# creating auto increment primary key since PostgresSQL 10
CREATE TABLE test_new (
    id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    payload text
);
INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *;

# simple insert
INSERT INTO category (DESCRIPTION) VALUES ('Hello WORLD!');

# creating a new user
CREATE ROLE phil PASSWORD 'hallo' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

# get the version of the PostgreSQL database
SELECT version();