PostgreSQL Getting Started: A Developer's Tutorial

What Is PostgreSQL?

PostgreSQL is the world's most advanced open-source relational database. It supports complex queries, foreign keys, triggers, updatable views, transactional integrity, and multiversion concurrency control. Whether you're building a simple blog or a multi-tenant SaaS platform, PostgreSQL scales with you.

In this tutorial you'll go from zero to a working PostgreSQL database with real tables and queries.

Creating Your First Database

Once you have access to a PostgreSQL instance — either locally or on a managed platform like [PandaStack](https://dashboard.pandastack.io) — connect using the psql CLI:

psql -h your-host -U your-user -d postgres

Now create a database and switch to it:

CREATE DATABASE myapp;
\c myapp

Creating Tables

Let's create a simple users table:

CREATE TABLE users (
  id        SERIAL PRIMARY KEY,
  email     VARCHAR(255) UNIQUE NOT NULL,
  name      VARCHAR(100),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

And a posts table with a foreign key:

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  user_id    INT REFERENCES users(id) ON DELETE CASCADE,
  title      VARCHAR(255) NOT NULL,
  body       TEXT,
  published  BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Inserting and Querying Data

Insert some sample rows:

INSERT INTO users (email, name) VALUES
  ('alice@example.com', 'Alice'),
  ('bob@example.com',   'Bob');

INSERT INTO posts (user_id, title, body, published) VALUES
  (1, 'Hello World', 'My first post.', true),
  (1, 'Draft Post',  'Work in progress.', false),
  (2, 'Bob Writes',  'PostgreSQL rocks.', true);

Fetch all published posts with the author name:

SELECT p.id, p.title, u.name AS author, p.created_at
FROM   posts p
JOIN   users u ON u.id = p.user_id
WHERE  p.published = true
ORDER  BY p.created_at DESC;

Filtering, Aggregating, and Grouping

Count posts per user:

SELECT u.name, COUNT(p.id) AS post_count
FROM   users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP  BY u.name
ORDER  BY post_count DESC;

Search posts by keyword using ILIKE (case-insensitive):

SELECT title, body
FROM   posts
WHERE  body ILIKE '%postgresql%';

Updating and Deleting Rows

-- Publish all of Alice's drafts
UPDATE posts
SET    published = true
WHERE  user_id = 1 AND published = false;

-- Remove a specific post
DELETE FROM posts WHERE id = 2;

Using Indexes

Add an index to speed up email lookups:

CREATE INDEX idx_users_email ON users(email);

We cover indexing in depth in the [Database Indexing Explained](/blog/database-indexing-explained) guide.

Transactions

Wrap related writes in a transaction to keep data consistent:

BEGIN;
  INSERT INTO users (email, name) VALUES ('carol@example.com', 'Carol');
  INSERT INTO posts (user_id, title, published)
    VALUES (currval('users_id_seq'), 'Carol Joins', true);
COMMIT;

If anything fails, run ROLLBACK; instead of COMMIT; to undo all changes atomically.

Running PostgreSQL on PandaStack

[PandaStack](https://dashboard.pandastack.io) provides managed PostgreSQL with automated backups, connection pooling, and zero-downtime restarts — no server management required. Spin up a PostgreSQL instance from the dashboard in under a minute, grab the connection string from the Databases panel, and paste it into your app's environment variables.

Full documentation is available at [docs.pandastack.io](https://docs.pandastack.io).

Next Steps

• Add indexes for frequently queried columns
• Use EXPLAIN ANALYZE to profile slow queries
• Explore JSON/JSONB columns for semi-structured data
• Set up connection pooling (see our [Connection Pooling Guide](/blog/database-connection-pooling))

PostgreSQL rewards the time you invest in learning it. Start simple, grow incrementally, and lean on managed hosting to handle the operational overhead.