Right, let’s talk about creating a database. Not the most glamorous part of the job, I know. It’s like building the foundation for a house: if you get it wrong here, you’ll be dealing with weird, structural cracks for the entire project. The good news is, it’s not hard to get it right. The CREATE DATABASE command seems simple, but the options you choose (or ignore) have long-term consequences.

The Absolute Bare Minimum

At its simplest, you just name the thing and run. PostgreSQL will take a bunch of sensible defaults from its base configuration. This is fine for kicking the tires, but I wouldn’t do it for anything real.

CREATE DATABASE my_amazing_app;

What just happened? Postgres actually cheated. It didn’t create your database from nothing. It cloned an existing database called template1. Think of template databases as a genetic blueprint; we’ll get into the juicy details of why that’s both brilliant and occasionally infuriating in a bit.

Encoding: Don’t Get It Wrong

This is the big one. Get the encoding (ENCODING) wrong and you’re in for a world of pain involving question marks and sad squares where your emojis used to be. UTF8 is the only encoding you should even be considering for a new application. It’s the standard. It handles pretty much every character from every human language, plus emojis, which are clearly the most important part of modern communication. 😉

The default encoding is usually UTF8, but why leave it to chance? Be explicit. It’s like putting your name on your lunch in the office fridge—it just avoids future drama.

CREATE DATABASE my_amazing_app
    ENCODING 'UTF8';

Collation and Ctype: The Sorting Police

While we’re talking about text, let’s discuss collation (LC_COLLATE) and character classification (LC_CTYPE). These settings control how text is sorted and what is considered a letter, number, etc.

The catch? These values are set at the cluster level when you run initdb. You cannot change them for a database after the fact. If you try to specify a different one in your CREATE DATABASE command, it will only work if it’s compatible with the cluster’s settings. It’s a mess.

The best practice? Just accept the defaults from your cluster for these two unless you have a very specific, well-researched need (like case-insensitive sorting in a language-specific way). Otherwise, you’re just creating future headaches for yourself. The default is usually fine.

CREATE DATABASE my_amazing_app
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8';

(Only run that if you know your cluster supports it. Otherwise, just omit them and let it use the default).

The Connection Limit Safety Valve

The CONNECTION LIMIT option is a safety net, not a performance tuning knob. By default, it’s -1, meaning “unlimited.” For your main application database, that’s probably what you want.

You might set a low connection limit on a dedicated reporting or analytics database to prevent a runaway script from consuming all your available connections and taking down the primary app. It’s a blunt instrument, but sometimes that’s what you need.

CREATE DATABASE reporting_db
    CONNECTION LIMIT 10;

Template Databases: The Clone Wars

Here’s where it gets interesting. Remember I said Postgres clones a template? You can see which one it used with the TEMPLATE option. The default is template1. This is a system database that exists specifically for this purpose.

Why should you care? Because anything you put in template1—every table, function, extension—will appear in every new database you create from that point on. This is either incredibly powerful or a horrifying way to pollute your entire system. I’ve seen people load the postgis extension into template1 so every new database is spatially aware. I’ve also seen people create a users table in there and then spend a week wondering why their accounting database has a users table. You have been warned.

The smarter move is to use template0. This is a pristine, unalterable vanilla template. Cloning from template0 guarantees you a clean, empty database with absolutely no user-added cruft. This is what you want 99.9% of the time.

CREATE DATABASE a_clean_slate
    TEMPLATE template0;

This is also your only way out if you bork template1 so badly that you can’t even connect to it to fix it. template0 is your emergency backup blueprint.

The One Weird Trick: Being Idempotent

Trying to create a database that already exists will throw an error, which is annoying if you’re running a setup script. Wrap it in a conditional to make it idempotent.

CREATE DATABASE IF NOT EXISTS my_amazing_app
    TEMPLATE template0
    ENCODING 'UTF8';

It’s simple, it’s clean, and it keeps your scripts from blowing up. You’re welcome. Now go build a solid foundation. And for the love of all that is holy, use UTF8.