Getting Into PostgreSQL: psql, Connections, and the First Commands You Actually Need

Most tutorials rush past this part. "Install Postgres, connect, start querying" — as if opening the database is the boring bit you skip through to get to the real content. When I was starting, that gap was exactly where I felt lost. You're connected, technically, but you have no idea what's actually in front of you.

This article covers those first few minutes: getting into psql, figuring out what you're connected to, and a handful of commands that eventually become reflexes if you use Postgres regularly.

Why not knowing your way around costs you time

Say you inherit a project. Someone hands you a connection string, you open psql, and you're in. Now what? You don't know what databases exist on the server. You don't know what tables are in the one you're connected to. Someone mentioned a table in a Slack message three months ago and you have no idea what columns it has.

Without a small set of orientation commands, every one of those questions turns into either opening a GUI (if one's set up) or trying to remember how to query Postgres's internal system catalogs directly, which honestly nobody has memorized. There's a small handful of psql shortcuts that answer all of this in a couple of seconds, and once they're second nature, this stops being a problem.

Connecting, and the commands that orient you

Connecting

psql -U username -d database_name -h host -p 5432

Each flag does one job:

• -U — the user you're connecting as
• -d — the database to connect to
• -h — the host, a remote server or localhost
• -p — the port, and 5432 is Postgres's default. It's a small detail, but it comes up as a quick sanity-check question fairly often.

A few commands worth knowing by heart

Once you're inside psql, there's a set of meta-commands — not SQL, just shortcuts specific to psql itself, which is why they start with a backslash and don't need a semicolon:

Command	What it does
\l	list databases
\c dbname	connect to a database
\dt	list tables in the current schema
\d tablename	describe a table — columns, types, constraints, indexes
\du	list roles/users
\q	quit

\d tablename is the one I use the most. It's the fastest way to see what a table actually looks like without leaving the terminal, and it shows constraints and indexes too, not just column names — something a plain SELECT * LIMIT 1 won't tell you.

Why these are separate from SQL

psql splits things into two categories on purpose. SQL is for talking to the database engine about your data — it gets parsed, planned, and executed on the server. Meta-commands are for talking to the client about your environment, so \dt doesn't need a semicolon and responds more or less instantly, because it's not going through the same query pipeline.

If you get asked how to check a table's structure without a GUI, \d tablename is basically the expected answer. It's less about the command itself and more a quick check on whether you've actually spent time in a terminal with Postgres, versus only clicking around in a visual tool.

Where the confusion usually starts: databases, clusters, templates

Creating and dropping a database

CREATE DATABASE project_db;
DROP DATABASE project_db;

One convention worth knowing early: one database per logical application is the norm. A geospatial dashboard would get its own database, separate from anything unrelated, even if they're sitting on the same server.

In multi-tenant setups, people sometimes reach for "one database per tenant" because it feels like clean isolation. It usually isn't the right call — hundreds of databases becomes a real management headache. A tenant_id column, or separate schemas per tenant within one database, tends to hold up much better as things grow.

What CREATE DATABASE is actually doing

Here's a detail that surprised me the first time I read about it: CREATE DATABASE doesn't build an empty database from scratch. It clones a template database — template1 by default. That explains a specific category of confusing bugs. If template1 has an unexpected encoding or locale setting, every database you create afterward quietly inherits it, and the symptoms show up much later as garbled text or sorting that doesn't behave the way you'd expect.

Setting up a project database, step by step

Say you're starting the geospatial dashboard from nothing. Here's roughly how that goes in practice:

psql -U postgres -h localhost -p 5432

You're connected to the default postgres database — not your project's yet, just the entry point.

\l

Confirms nothing named venue_dashboard already exists, so you're not about to collide with something.

CREATE DATABASE venue_dashboard;
\c venue_dashboard

Now you're connected to your project's own database, kept separate from anything else on the server.

\dt

Nothing comes back — no tables yet, which is expected on a fresh database. It also confirms you're actually in the right place before running any CREATE TABLE statements.

Connect, list, create, switch, verify. It's a small sequence, but it's one you end up running at the start of most new projects, and doing it deliberately once means you're not fumbling through it later, especially in something like a live technical interview.

Where this leaves you

The few minutes before your first CREATE TABLE matter more than they seem to. Knowing \dt, \d, and \l well enough that you don't have to think about them, and understanding that CREATE DATABASE clones a template rather than starting from nothing, makes navigating Postgres a lot less stressful day to day.

Next time you sit down in psql, even in a database you know well, try running the orientation commands first anyway. It's a small habit, but it holds up well as the number of databases and tables you're dealing with grows.

Next up: actually shaping data — tables, data types, and constraints that make bad data hard to insert in the first place.