Homepage

PostgreSQL Cheat Sheet

I will keep updating this document over time.

This is a basic PostgreSQL cheat sheet for quick tips about using PostgrSQL. It's mainly meant for me as a reference when I need to loop up commands quickly.

Starting a PostgreSQL Server

This assumes that you have PostgreSQL already and installed.

mkdir -p /tmp/databases
initdb /tmp/databases/db0
PGPORT=5005 postgres -D /tmp/databases/db0 -c shared_buffers="4GB"

Don't forget to clean this up at the end by running this command

rm -r tmp

Now from another terminal run

PGPORT=5005 psql postgres

Basics

Creating a database

CREATE DATABASE test_database

Creating a table

CREATE TABLE users(id bigserial, name VARCHAR(255) NOT NULL, height INTEGER NOT NULL, created_at TIMESTAMP DEFAULT NOW());

Inserting some data

INSERT INTO users(name, height) values('Omar Qunsul', 191);

Partitioned tables

CREATE TABLE data(data_type INT, value BIGINT) PARTITION BY LIST (data_type);

CREATE TABLE data_type_1 PARTITION OF data FOR VALUES IN (1);

INSERT INTO data VALUES(1, 10);

But this statement would fail:

INSERT INTO data VALUES(2, 10);

ERROR:  no partition of relation "data" found for row
DETAIL:  Partition key of the failing row contains (data_type) = (2).

PostgreSQL as a Vector Database

TODO

References

About

My name is Omar Qunsul. You can find me on Linkedin.

I write these articles mainly as a future reference for me. So I dedicate some time to make them look shiny, and share them with the public.

Homepage