Homepage
Other posts to read
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

Managing users

TODO

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).

Indexes on partitioned tables

Indexes on partitioned tables

If you create an index on a partitioned table, but the index is already on the child tables. Then it will be ignored on child partitions, but the indexes on them will be attached to the index on the parent table.

Sample code to test

CREATE TABLE sales (id INT, sale_date DATE, amount NUMERIC) PARTITION BY RANGE (sale_date);

-- Create partitions

CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');
CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2024-12-31');

-- Add index to the parent (propagates to all partitions) 

CREATE INDEX ON sales_2023 (sale_date);
CREATE INDEX ON sales (sale_date);

TODO: Write about partitioned tables attached indexes

Reference: MR on GitLab

PostgreSQL as a Vector Database

TODO

Getting a table count

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