# PostgreSQL cheatsheet

Useful cheatsheet for PostgreSQL

# 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

# Testing

# Query performance

create or replace function perf_query() 
returns void
language sql stable
as $$
  select true;
$$;

explain analyze
select perf_query()
from generate_series(1,1000);

/*
"Function Scan on generate_series  (cost=0.00..100.00 rows=10000 width=1) (actual time=10.781..17.027 rows=1000 loops=1)"
"Planning Time: 4.880 ms"
"Execution Time: 23.155 ms"
*/

# 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

Updating tables with dependent views. Usage exanple:

select deps_save_and_drop_dependencies('public', 'users');
select deps_restore_dependencies('public', 'users');

# Resources

Hacker News

← Mental Models Loading JSON into Postgres →