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.

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.
Table of Contents
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
postgresuser when it asks, and keep pgAdmin and the command-line tools selected. - Ubuntu / Debian:
sudo apt install postgresql, thensudo -u postgres psqlto 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
| Command | What it does |
|---|---|
\l | List all databases |
\c shop | Connect to the shop database |
\dt | List the tables in the current database |
\d customers | Show a table’s columns, types, defaults and indexes |
\x | Switch to vertical output, handy for wide rows |
\q | Quit 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 IDENTITYis the modern, SQL-standard replacement forSERIAL. It works likeIDENTITYin SQL Server.numeric(18,3)stores money exactly. Never userealordouble precisionfor 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. timestamptzstores 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, plaindateis 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 Server | PostgreSQL |
|---|---|
IDENTITY(1,1) | GENERATED ALWAYS AS IDENTITY |
NVARCHAR(100) | varchar(100) or text (UTF-8 by default) |
DATETIME2 | timestamp / timestamptz |
BIT | boolean |
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.id | RETURNING 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 Customerscreatescustomers. 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/2returns 3, not 3.5. Write7/2.0or cast one side tonumericfor percentages and averages. LIKEis case-sensitive. UseILIKEfor case-insensitive search, for exampleWHERE name ILIKE '%noor%'.
Connecting from your application
- Laravel: set
DB_CONNECTION=pgsqland port5432in.envand enable thepdo_pgsqlPHP 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
| PostgreSQL | MySQL | SQL Server | |
|---|---|---|---|
| Owner | Community | Oracle | Microsoft |
| Cost | Free for any use | Free Community edition | Free editions; paid for most production |
| Strength | Standards, features, strictness | Simplicity, web hosting, WordPress | Business software, Microsoft tools |
| Auto-number | GENERATED ... AS IDENTITY | AUTO_INCREMENT | IDENTITY(1,1) |
| Top N rows | LIMIT 10 | LIMIT 10 | TOP (10) |
| Case-insensitive search | ILIKE | LIKE (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
- Who owns PostgreSQL?
- What does
\c shopdo in psql, and is it SQL? - What does
SELECT 7/2return in PostgreSQL, and how do you get 3.5? - Which data type lets you store flexible JSON details and search them?
- Which extension would you use to add AI vector search?
Answers
- Nobody. It’s developed by a worldwide community and free for any use.
- It connects to the
shopdatabase. It’s a psql command, not SQL, which is why it starts with a backslash and needs no semicolon. 3, because whole numbers divided give a whole number. Write7/2.0to get 3.5.jsonb.- 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
- SQL for Complete Beginners: the queries you’ll use every day.
- SQL WHERE, ORDER BY and LIMIT: filter, sort and page through results.
- MySQL vs PostgreSQL: the full comparison.
Related articles
- MySQL Explained for Beginners: what MySQL is and where it’s used.
- How to Choose the Right Database Server: a checklist for your next project.
- SQL INSERT, UPDATE and DELETE: adding and changing data safely.
- SQL Server vs MySQL vs PostgreSQL Cheat Sheet: the same tasks in all three.