PostgreSQL Tutorial for Beginners: What It Is and How to Start

If MySQL is the most popular database on the web, PostgreSQL is the one developers keep choosing for new projects. It’s free, it’s very powerful, and in recent Stack Overflow developer surveys it has come out on top as both the most used and the most admired database.

Diagram of key PostgreSQL features around a central PostgreSQL node

This guide explains what PostgreSQL is, what makes it different from MySQL and SQL Server, and then walks you through your first ten minutes with it: installing it, creating a database and running real queries.

Quick answer: what is PostgreSQL?
PostgreSQL (or simply “Postgres”) is a free, open-source relational database. It stores data in tables, uses SQL, and is known for following the SQL standard closely, protecting data carefully, and offering advanced features such as JSONB, arrays and extensions (PostGIS for maps, pgvector for AI search). It runs on Windows, Mac and Linux.

Want to start right away? Install PostgreSQL, open psql, then create a database, a table and your first query in about 10 minutes. Jump to the step-by-step quick start, or keep reading to learn what makes PostgreSQL different.

What Is PostgreSQL?

PostgreSQL (say “post-GRES-Q-L”, or just “Postgres”) is a relational database management system: software that stores data in tables, links them with keys and answers questions in SQL. If that sentence is new to you, start with What Is a Database?.

It started in 1986 as a research project called POSTGRES at the University of California, Berkeley. SQL support was added in the mid-1990s, and the name changed to PostgreSQL to show it. Unlike MySQL (owned by Oracle) or SQL Server (Microsoft), PostgreSQL isn’t owned by any single company. It’s developed by a worldwide community, released under a very permissive licence, and free for any use, including commercial software.

A new major version comes out every year (PostgreSQL 18 was released in September 2025), and each version is supported with fixes for five years.

Its personality in one line: where MySQL started out focused on speed and simplicity, PostgreSQL has always focused on correctness and features. It follows the SQL standard more closely than most databases, and it’s very strict about bad data.

What Is PostgreSQL Used For?

  • Business applications such as accounting, invoicing, stock and HR, where data must stay exact.
  • Web and mobile app back ends. Many new projects start on PostgreSQL, and services like Supabase are built on it.
  • Data analysis and reporting. Its rich SQL (window functions, CTEs, FILTER) makes complex reports easier.
  • Maps and location data. With the PostGIS extension, it’s one of the best geographic databases available.
  • AI applications. With the pgvector extension, it can store and search vectors for AI-powered search.

PostgreSQL Tutorial: Your First 10 Minutes

This short walkthrough takes you from nothing to a working table with real queries. Every command below was run on PostgreSQL 16 and gives exactly the output shown.

Step 1: Install PostgreSQL

  • Windows or Mac: download the installer from postgresql.org. Choose a password for the postgres user when it asks, and keep pgAdmin and the command-line tools selected.
  • Ubuntu / Debian: sudo apt install postgresql, then sudo -u postgres psql to connect.
  • No install at all: create a free database on Supabase or Neon and use their SQL editor in the browser. Skip to Step 3.

Step 2: Connect with psql

Open a terminal (on Windows, “SQL Shell (psql)” from the Start menu) and connect as the default user. You’ll see a postgres=# prompt, which means you’re in.

psql -U postgres

Step 3: Create a database and switch to it

CREATE DATABASE shop;
\c shop

\c is a psql command, not SQL: it connects to another database. psql commands start with a backslash and don’t need a semicolon.

Step 4: Create a table

CREATE TABLE customers (
    customer_id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name         varchar(100) NOT NULL,
    city         varchar(50),
    joined_on    date NOT NULL DEFAULT current_date
);

GENERATED ALWAYS AS IDENTITY numbers the rows for you, and DEFAULT current_date fills in today’s date when you don’t give one.

Step 5: Add some rows

INSERT INTO customers (name, city) VALUES
    ('Sara Ahmed', 'Manama'),
    ('Omar Khalid', 'Riffa'),
    ('Lina Saleh', 'Manama');

Step 6: Query, update and delete

SELECT customer_id, name, city
FROM customers
WHERE city = 'Manama'
ORDER BY name;

--  customer_id |    name    |  city
-- -------------+------------+--------
--            3 | Lina Saleh | Manama
--            1 | Sara Ahmed | Manama

UPDATE customers SET city = 'Muharraq' WHERE customer_id = 2;
DELETE FROM customers WHERE customer_id = 3;
SELECT * FROM customers;

Always check the WHERE clause before an UPDATE or DELETE: without it, every row changes. More on this in SQL INSERT, UPDATE and DELETE.

Step 7: The psql commands you’ll use every day

CommandWhat it does
\lList all databases
\c shopConnect to the shop database
\dtList the tables in the current database
\d customersShow a table’s columns, types, defaults and indexes
\xSwitch to vertical output, handy for wide rows
\qQuit psql

That’s a complete first session. Next, learn the queries that do most of the daily work in WHERE, ORDER BY and LIMIT, and keep the SQL Server vs MySQL vs PostgreSQL cheat sheet open when you switch between databases.

What Makes PostgreSQL Different

It’s strict, in a good way

PostgreSQL refuses data that doesn’t fit, instead of quietly changing it. Put text in a number column, or an invalid date like 31 February, and you get an error rather than a silently “fixed” value. For business data, that strictness saves a lot of cleaning later.

JSONB: flexible data inside a relational table

Sometimes a few details vary from row to row. PostgreSQL’s jsonb type lets you store them as JSON, and still search them quickly:

CREATE TABLE products (
    product_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       varchar(100) NOT NULL,
    details    jsonb
);

INSERT INTO products (name, details) VALUES
    ('Notebook', '{"pages": 120, "colour": "blue"}'),
    ('Backpack', '{"colour": "black", "litres": 20, "laptop_pocket": true}');

SELECT name, details ->> 'colour' AS colour
FROM products
WHERE details @> '{"laptop_pocket": true}';
-- Backpack | black

->> reads a value as text, and @> means “contains”. You get much of the flexibility of a document database without leaving SQL. (Types of Databases explains document databases.)

Data types other databases don’t have

Arrays, UUIDs, IP addresses, date ranges and more are built in. An array column, for example, holds a short list in one field:

CREATE TABLE posts (
    post_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title   text NOT NULL,
    tags    text[]
);

INSERT INTO posts (title, tags) VALUES
    ('SQL JOINs Explained', ARRAY['sql', 'joins', 'beginners']),
    ('Backup and Restore',  ARRAY['sql server', 'backup']);

SELECT title FROM posts WHERE 'beginners' = ANY (tags);
-- SQL JOINs Explained

Use arrays for small lists you only read together. If you need to search and link the items a lot, a separate table is usually better (see Database Normalization).

Extensions add whole new abilities

An extension is an add-on you switch on with one command, CREATE EXTENSION. The best known:

  • PostGIS: maps, distances and geographic queries.
  • pgvector: vector search for AI applications.
  • pg_trgm: fuzzy text search, for “did you mean…?” features.
  • TimescaleDB: fast storage and queries for time-series data like sensor readings.

Readers don’t block writers

PostgreSQL keeps older versions of a row while it’s being changed (a design called MVCC). So a long report can read data while other users keep saving new records, and neither waits for the other. In SQL Server, people often reach for WITH (NOLOCK) to get a similar effect; in PostgreSQL you don’t need it.

PostgreSQL in a Real Business Database

The quick start used one small customers table. Business systems such as sales, accounting and inventory ask more of a database: money must be exact, document numbers must never repeat, and reports must add up. Here is how PostgreSQL handles a small invoicing schema, and which of its features matter most in that kind of work.

An invoice table done properly

CREATE TABLE customers (
  customer_id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name         varchar(150) NOT NULL,
  tax_no       varchar(20)
);

-- Tax number is optional, but when it is filled it must be unique
CREATE UNIQUE INDEX ux_customers_tax_no
  ON customers (tax_no) WHERE tax_no IS NOT NULL;

CREATE TABLE invoices (
  invoice_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_id    int NOT NULL REFERENCES branches,
  fiscal_year  smallint NOT NULL,
  invoice_no   int NOT NULL,
  customer_id  int NOT NULL REFERENCES customers,
  invoice_date date NOT NULL DEFAULT current_date,
  total        numeric(18,3) NOT NULL CHECK (total >= 0),
  status       varchar(10) NOT NULL DEFAULT 'open'
               CHECK (status IN ('open', 'paid', 'void')),
  created_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (branch_id, fiscal_year, invoice_no)
);

A few choices here are worth copying:

  • GENERATED ALWAYS AS IDENTITY is the modern, SQL-standard replacement for SERIAL. It works like IDENTITY in SQL Server.
  • numeric(18,3) stores money exactly. Never use real or double precision for amounts. Three decimal places suit currencies such as the Bahraini dinar or Kuwaiti dinar; use 2 for most others.
  • UNIQUE (branch_id, fiscal_year, invoice_no) lets every branch restart its numbering each year while making a duplicate invoice number impossible.
  • The partial unique index (WHERE tax_no IS NOT NULL) allows many customers without a tax number but blocks two customers with the same one.
  • timestamptz stores an exact moment in time, which avoids confusion when servers, users or branches are in different time zones. For a date with no time, such as an invoice date, plain date is right.

For more on these rules, see SQL Constraints Explained Simply and Primary Keys vs Foreign Keys.

Get the new ID back with RETURNING

After saving an invoice header, the application needs its new ID to save the lines. PostgreSQL returns it in the same statement:

INSERT INTO invoices (branch_id, fiscal_year, invoice_no, customer_id, total)
VALUES (1, 2026, 1001, 1, 250.500)
RETURNING invoice_id, created_at;

RETURNING also works with UPDATE and DELETE. SQL Server does the same job with OUTPUT.

Upsert with ON CONFLICT

A daily job that downloads exchange rates should insert today’s rate, or update it if the job runs twice. One statement does both:

INSERT INTO exchange_rates (rate_date, currency, rate)
VALUES ('2026-11-08', 'EUR', 0.918400)
ON CONFLICT (rate_date, currency)
DO UPDATE SET rate = EXCLUDED.rate;

EXCLUDED means “the row you tried to insert”. The conflict target must match a primary key or unique constraint, here PRIMARY KEY (rate_date, currency). PostgreSQL 15 and later also support the standard MERGE statement.

Reports with FILTER

Management reports often need several totals side by side. Instead of a CASE inside every SUM, PostgreSQL has the FILTER clause:

SELECT b.name AS branch,
       COUNT(*)                                      AS invoices,
       SUM(i.total) FILTER (WHERE i.status = 'paid') AS paid_total,
       SUM(i.total) FILTER (WHERE i.status = 'open') AS open_total
FROM invoices i
JOIN branches b ON b.branch_id = i.branch_id
WHERE i.status <> 'void'
GROUP BY b.name
ORDER BY b.name;

Note that a branch with no paid invoices shows NULL, not 0, in paid_total. Wrap it in COALESCE(..., 0) if the report should print zero.

Partial indexes for “open” documents

Most screens show open invoices, but most rows in the table are paid or void. A partial index only covers the rows you search, so it stays small and fast:

CREATE INDEX ix_invoices_open
  ON invoices (customer_id)
  WHERE status = 'open';

SQL Server calls the same idea a filtered index. PostgreSQL can use it for queries whose WHERE includes status = 'open'.

Safer upgrades: DDL inside a transaction

In PostgreSQL, CREATE, ALTER and DROP can run inside a transaction and be rolled back. If step 3 of a database upgrade script fails, steps 1 and 2 are undone too, and the database is not left half-changed:

BEGIN;
ALTER TABLE invoices ADD COLUMN due_date date;
UPDATE invoices SET due_date = invoice_date + 30;
-- something goes wrong...
ROLLBACK;   -- the new column is gone as well

MySQL commits each ALTER TABLE automatically, so the same rollback isn’t possible there. (A few commands, such as CREATE INDEX CONCURRENTLY and VACUUM, can’t run inside a transaction.)

Coming from SQL Server? Quick translation table

SQL ServerPostgreSQL
IDENTITY(1,1)GENERATED ALWAYS AS IDENTITY
NVARCHAR(100)varchar(100) or text (UTF-8 by default)
DATETIME2timestamp / timestamptz
BITboolean
GETDATE()now() or current_timestamp
ISNULL(x, 0)COALESCE(x, 0)
'Inv-' + CAST(no AS varchar)'Inv-' || no
SELECT TOP 10 ...... LIMIT 10
OUTPUT inserted.idRETURNING id
WITH (NOLOCK)Not needed: readers don’t block writers

Three differences catch most people moving from SQL Server or MySQL:

  • Names are folded to lowercase. CREATE TABLE Customers creates customers. If you write "Customers" in double quotes, the name becomes case-sensitive and you must quote it in every query. The simplest rule: use lowercase names with underscores.
  • Integer division stays integer. SELECT 7/2 returns 3, not 3.5. Write 7/2.0 or cast one side to numeric for percentages and averages.
  • LIKE is case-sensitive. Use ILIKE for case-insensitive search, for example WHERE name ILIKE '%noor%'.

Connecting from your application

  • Laravel: set DB_CONNECTION=pgsql and port 5432 in .env and enable the pdo_pgsql PHP extension. Eloquent and the query builder work the same way.
  • Delphi: FireDAC includes a PostgreSQL driver (Enterprise and Architect editions), and it needs the PostgreSQL client library (libpq) next to your application.
  • Reporting and BI tools: Power BI, Excel and most reporting tools connect through the PostgreSQL ODBC driver or a built-in connector.

PostgreSQL vs MySQL vs SQL Server

PostgreSQLMySQLSQL Server
OwnerCommunityOracleMicrosoft
CostFree for any useFree Community editionFree editions; paid for most production
StrengthStandards, features, strictnessSimplicity, web hosting, WordPressBusiness software, Microsoft tools
Auto-numberGENERATED ... AS IDENTITYAUTO_INCREMENTIDENTITY(1,1)
Top N rowsLIMIT 10LIMIT 10TOP (10)
Case-insensitive searchILIKELIKE (default)LIKE (default)

For the full head-to-head, read MySQL vs PostgreSQL: Which Should You Use?. The other two are covered in MySQL Explained and SQL Server Explained, and the syntax cheat sheet puts 31 everyday tasks side by side.

PostgreSQL Tools

  • psql: the command-line tool used in the quick start. Fast, and available everywhere PostgreSQL is.
  • pgAdmin: the official free visual tool, included with the Windows and Mac installers.
  • DBeaver: a free tool that handles PostgreSQL and many other databases.
  • VS Code: with a PostgreSQL extension, if you already write code there.

Prefer not to install anything? Supabase and Neon give you a free cloud PostgreSQL database with a web dashboard, and Amazon, Microsoft Azure and Google Cloud all offer managed PostgreSQL for real projects.

When Should You Choose PostgreSQL?

Choose PostgreSQL when you’re starting a new application and have a free choice, when data accuracy matters (money, stock, records), when you need advanced SQL, JSON, maps or AI search, or when you want a free database with no licence questions at all.

Choose MySQL when you’re using WordPress or another platform built for it, or your web host only offers MySQL. Choose SQL Server when your company’s software and tools are built around Microsoft.

If you’re learning SQL and nothing decides it for you, PostgreSQL is an excellent first database: free, strict enough to teach good habits, and very close to standard SQL.

Check Your Understanding

  1. Who owns PostgreSQL?
  2. What does \c shop do in psql, and is it SQL?
  3. What does SELECT 7/2 return in PostgreSQL, and how do you get 3.5?
  4. Which data type lets you store flexible JSON details and search them?
  5. Which extension would you use to add AI vector search?

Answers

  1. Nobody. It’s developed by a worldwide community and free for any use.
  2. It connects to the shop database. It’s a psql command, not SQL, which is why it starts with a backslash and needs no semicolon.
  3. 3, because whole numbers divided give a whole number. Write 7/2.0 to get 3.5.
  4. jsonb.
  5. pgvector.

Frequently Asked Questions

Is PostgreSQL free for commercial use?

Yes, completely. Its licence lets you use, change and include it in commercial software without paying anything. Companies that charge for PostgreSQL are selling hosting, support or tools, not the database itself.

What is the difference between Postgres and PostgreSQL?

None. “Postgres” is the original name and the everyday nickname. “PostgreSQL” is the official name.

Is PostgreSQL harder to learn than MySQL?

Not really. The everyday SQL is almost identical. PostgreSQL is a little stricter and has more features to discover later, but for a beginner writing SELECT, JOIN and GROUP BY, the difference is small.

Can PostgreSQL replace SQL Server?

For many applications, yes, and some companies move to it to save licence costs. But it’s a real project: T-SQL code, stored procedures and tools like SQL Server Agent have to be rewritten or replaced. For an existing system that works well, the move has to be worth the effort.

What to Learn Next

  1. SQL for Complete Beginners: the queries you’ll use every day.
  2. SQL WHERE, ORDER BY and LIMIT: filter, sort and page through results.
  3. MySQL vs PostgreSQL: the full comparison.

Related articles

Scroll to Top