PostgreSQL cheatsheet

Useful cheatsheet for Postgres

Upgrading

Updating a running server (from 10 to 11):

apt-get install -y postgres-11 # Install the latest version, which will start on port 5433
sudo pg_dropcluster 11 main --stop # Stop the instance on port 5433
sudo pg_upgradecluster 10 main  # Upgrade the instance on port 5432
sudo pg_dropcluster 10 main  # Drop the outdated instance

Templates

New tables

CREATE TABLE public.table_name (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  data jsonb,
  inserted_at timestamp without time zone DEFAULT timezone('utc'::text, now()) NOT NULL,
  updated_at timestamp without time zone DEFAULT timezone('uc'::text, now()) NOT NULL
);

COMMENT ON TABLE public.table_name IS 'Some table name.';
COMMENT ON COLUMN public.table_name.inserted_at IS 'UTC timestamp of when the record was inserted.';
COMMENT ON COLUMN public.table_name.updated_at IS 'UTC timestamp of when the record was last updated.';

Utilities