Postgres

TL;DR

connection string

postgres://<user>:<password>@<host>:<port>/<database_name>

Different Kinds of Schema

.
|-- db cluster (optional)
    |-- db server (1 or more)
        |-- schema
            |-- table

Schema in this context doesn't mean the structure of a table, that's a different kind of schema. In SQLite or MySQL, this extra schema separation doesn't exist. So questions like What schema qre you querying against? and What schema does the table live in?, as opposed to talking about the structure What are the columns?. Both are called schema, but they mean different things.

Data Types Best Practices

• use pg_typeof and pg_column_size for debugging

Numbers

Integers are fast (don't include NaN and Infinity), numeric (accurate, but slow), floats (fast, but inaccurate).

Don't use money, except maybe for formatting data. Store money as numerics or integers.

Characters

Don't use fixed char, because it's probably going to perform bad. Prefer VARCHAR (or character varying in postgres). Or even better to use TEXT with a check constraint.

Check Constraints

Any data integrity related logic should go inside the database.

Write clear error message for constraint's, debugging Kevin will be happy. Column constraint can be written as a table constraint, but not reversed.

Changing the logic of a constraints means dropping the constraint, and recreating it, but can be done in one transaction>

CREATE TABLE check_example (
    discount_price NUMERIC CHECK (discount_price > 0), -- check constraint, without
    price NUMERIC CONSTRAINT price_must_be_positive CHECK (price > 0), -- with name
    CONSTRAINT price_must_exceed_discount CHECK (price > discount_price) -- table label
);

Domain Types

Domain types combine check constraints and data types under a custom name, this is not really a new type, but rather wraps all the logic of the domain. Possible to alter a domain, and it's possible to only validate new incoming values.

Useful for re-using constraints, documentation for the rest of the team, ...

CREATE DOMAIN us_postal_code AS text
CONSTRAINT format CHECK (
    LENGTH(VALUE) <= 5
);

Encoding and Collations

encoding

how to translates from bytes to (UTF-8, ANSI, ...) and reversed

collation

set of rules how characters compare to each other in a language (en_US.UTF-8)

SELECT 'abc' = 'ABC' COLLATE "en_US.UTF-8" AS result; -- en_US rule says lower and uppercase are not the same characters

Binary Data

Storing small files are ok'ish, not great, but it's easy. Great example of storing binary data in the db is checksums, because you don't have to mess with encoding and just check the raw bytes. So, it's a super great way to do strict equality lookups of large data.

SELECT md5('hello world') as value -- not secure, but very fast
SELECT sha256('hello world') as value -- use this if you get dinged by security audit despite only using md5 for checksums

UUID

No reason to store UUID's in anything other than UUID data types, because the data type is hyper optimized for it. Random UUID's are great for communicating id's between unrelated services, but aren't great for a primary key (unless it's UUID v7, since the first part is timestamp based).

Boolean

true, false or unknown

Enums

Adding a new variant is easy with postgres, but they are kind of a bitch to deal with when you want to remove a variant. You will need to alter the current column and map any variant that you want to remove to another variant, before altering the enum to the updated one.

They are sorted by order the variants are stored in the enum.

CREATE TYPE mood AS ENUM ('happy', 'neutral', 'sad');

Timestamps

Always store timestamptz (ISO 8601), don't try to be ambiguous and let postgres figure out the timezone. Or Unix timestamps to_timestamp() are great too (cause it's a time in UTC).

Timezones

Keep the timezone in UTC for as long as possible, because it's less confusing. Referring to timezone, refer to it by name, because otherwise run into Daylight Saving Time issues.

The worst one to use is the numbers, because they refer to a POSIX style where a positive number meant going "west" and a negative one meant going "east". You can fix this by using at time zone interval.

SELECT
    '2025-01-20 11:30:08'::timestamptz as utc,
    '2025-01-20 11:30:08'::timestamptz at time zone 'Europe/Helsinki' as euh,
    '2025-01-20 11:30:08'::timestamptz at time zone 'EET' as eet,
    '2025-01-20 11:30:08'::timestamptz at time zone 'EEST' as eest,
    '2025-01-20 11:30:08'::timestamptz at time zone '+02:00' as hour_offset,
    '2025-01-20 11:30:08'::timestamptz at time zone interval '+02:00' as interval_offset;

Checkout all the information about the timezones.

SELECT * FROM pg_timezone_names WHERE name like %Helsinki;

Date and Time

Use only when it makes sense to not use a timestamp, such as birthdays, or store opening hours. But even that is ambiguous, because someone might look at your store from a different timezone for the opening hours and call to check if there's still in stock of something.

In these rare usecase where you actually only want the date or time, it's the best to not use a timezone because a time with a timezone is meaningless without a date. So, prefer to use LOCALTIME or CURRENT_DATE (where the local of the db is always UTC). Avoid CURRENT_TIME.

Intervals

References