Database, Schemas and Tables

If you’ve ever had to store data, whether it’s a simple list of contacts or a full-blown application, you’ve probably run into databases. And if you’ve looked into relational databases, PostgreSQL has likely popped up. So what makes it special?

Why PostgreSQL?

PostgreSQL (or just Postgres) is an open-source, feature-rich relational database known for its robustness and extensibility. It supports advanced data types, ACID compliance, and has a powerful query planner. In short, it’s the Swiss Army knife of databases.

Unlike MySQL, which started as a lightweight alternative, PostgreSQL was built with strict standards and enterprise features from the start. Need JSON support? Full-text search? Geospatial queries? PostgreSQL handles all of that natively. It’s widely used in applications ranging from small startups to massive enterprise systems, thanks to its reliability and scalability.

Another reason developers love PostgreSQL is its strong community. There’s extensive documentation, active forums, and regular updates introducing new features. This means you’re never alone when facing an issue, there’s always a guide or a fellow developer who has solved it before.

Installing PostgreSQL

Before we dive into using it, let’s get it installed.

macOS

brew install postgresql
brew services start postgresql

Linux (Debian-based)

sudo apt update && sudo apt install postgresql postgresql-contrib

Windows

Download the installer from postgresql.org and follow the setup wizard.

Once installed, PostgreSQL runs as a background service. Now let’s connect to it.

Meet psql

psql is PostgreSQL’s interactive command-line tool. If you’ve used MySQL’s mysql client, it’s similar, but more powerful.

To start it, run:

psql -U postgres

This connects to the default postgres user. If you set a password during installation, you’ll need to enter it.

If you see a prompt like this, you’re in:

postgres=#

Basic Commands

Inside psql, you can run SQL queries or use special \ commands. Here are a few useful ones:

• \l: List all databases
• \c database_name: Connect to a database
• \dt: Show tables in the current database
• \d table_name: Show details of a specific table
• \q: Exit psql

You can also enter SQL queries directly, and PostgreSQL will execute them. Try running:

SELECT version();

This will display the PostgreSQL version currently running on your system.

Databases, Schemas, and Tables

Understanding how PostgreSQL organizes data is key to using it effectively. Let’s break it down:

• Database: A PostgreSQL instance can host multiple databases, each with its own set of schemas and tables. Databases are isolated from one another
• Schema: A namespace within a database that groups related tables together. By default, PostgreSQL uses the public schema unless specified otherwise
• Table: The actual data storage structure, consisting of rows and columns. Tables belong to a schema within a database

For example, if you’re building an e-commerce app, you might have:

• A database named shop_db
• A schema named sales
• A table orders inside the sales schema

Creating this structure looks like this:

CREATE DATABASE shop_db;
\c shop_db

CREATE SCHEMA sales;

CREATE TABLE sales.orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP DEFAULT now()
);

Now, if you want to query this table, you need to specify the schema:

SELECT * FROM sales.orders;

If you find typing the schema name cumbersome, you can set a default search path:

SET search_path TO sales;
SELECT * FROM orders;

Creating Your First Database

Let’s create a database and a table:

CREATE DATABASE my_first_db;
\c my_first_db

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT now()
);

This creates a users table with an auto-incrementing id, a required name, a unique email field, and a timestamp of creation.

Adding and Querying Data

Insert some data:

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

Retrieve it:

SELECT * FROM users;

You should see:

 id | name  |        email         |       created_at       
----+-------+----------------------+------------------------
  1 | Alice | alice@example.com    | 2025-04-01 12:00:00
  2 | Bob   | bob@example.com      | 2025-04-01 12:01:00

Want to see just a specific user?

SELECT * FROM users WHERE name = 'Alice';

Need to update information?

UPDATE users SET email = 'alice@newdomain.com' WHERE name = 'Alice';

Or remove a record:

DELETE FROM users WHERE name = 'Bob';

Wrapping Up

That’s a solid introduction to PostgreSQL! You’ve installed it, connected with psql, learned about databases, schemas, and tables, and even written some queries. PostgreSQL is a vast ecosystem with powerful features, and we’ve just scratched the surface.