← Back to posts

PostgreSQL References

#postgresql#database#sql

Intro

This is a PostgreSQL reference post for commands and patterns I want to keep handy.

Reading Large Object Values

Large object columns store an object identifier rather than the raw value inline in the row. If you need to inspect the contents directly from SQL, lo_get() reads the large object.

If the data is text-like, encode(..., 'escape') makes the result easier to read in a query output.

Basic Pattern

select encode(lo_get(column_name), 'escape');

Example Query

select
  device_uuid,
  status,
  encode(lo_get(masterdata), 'escape') as masterdata_text
from app.reg_masterdatas
where device_uuid = '11111111-2222-3333-4444-555555555555'
order by creation_date desc
limit 1;

This is useful when the application stores large payloads as PostgreSQL large objects and you want a quick inspection path without exporting data first.

Backup and Restore

For routine backups, I prefer PostgreSQL’s custom dump format. It is compressed, portable, and works well with pg_restore.

Backup

pg_dump \
  --clean \
  --large-objects \
  --format=custom \
  --host=localhost \
  --username="$POSTGRES_USER" \
  --dbname="$POSTGRES_DB" \
  --file="/backup/$(date +%Y-%m-%d-%H-%M-%S).dump"

Restore

pg_restore \
  --clean \
  --large-objects \
  --host=localhost \
  --username="$POSTGRES_USER" \
  --dbname="$POSTGRES_DB" \
  "/backup/2026-04-11-12-00-00.dump"

Primary Key Notes

For a good overview of PostgreSQL primary key tradeoffs, Supabase has a useful reference:

Choosing a Postgres Primary Key

serial and bigserial

serial is a convenient shorthand that creates an integer column backed by a PostgreSQL sequence.

create table users (
  id serial primary key,
  email citext not null check (length(email) < 255),
  name text not null
);

This still works well, but it is older PostgreSQL syntax.

Prefer identity for New Tables

For new schemas, I generally prefer the SQL-standard identity syntax introduced in PostgreSQL 10.

create table users (
  id integer primary key generated by default as identity,
  email citext not null check (length(email) < 255),
  name text not null
);

identity is more explicit, more portable in intent, and usually the better default when designing a fresh table.

← Back to posts