PostgreSQL Basics: Understanding Databases, Tables, and SQL Commands

After installing PostgreSQL, the next step is to understand the basic concepts required to work with it confidently.

In this article, you will learn:

What a database is
What tables, rows, and columns are
Basic PostgreSQL SQL commands
How data is stored and accessed

This post is written for absolute beginners.

What Is a Database?

A database is an organized collection of data stored electronically.

Examples of data stored in databases:

User information
Orders
Products
Payments
Logs

PostgreSQL stores data in databases, and each database contains tables.

What Is a Table?

A table is where the actual data is stored.

A table:

Has a name
Contains rows and columns
Stores records

Example:
A users table may store information about users.

Rows and Columns Explained

Columns

Define the type of data
Have names and data types

Example columns:

id
name
email
created_at

Rows

Each row is a record
Represents one entry

Example:

One row = one user

What Is SQL?

SQL (Structured Query Language) is used to interact with PostgreSQL.

Using SQL, you can:

Create tables
Insert data
Retrieve data
Update data
Delete data

PostgreSQL uses standard SQL with additional advanced features.

Connecting to PostgreSQL

To connect to PostgreSQL using the command line:

psql -U postgres

You will be logged into the PostgreSQL shell.

Creating a Database

Create a new database:

CREATE DATABASE mydb;

Connect to the database:

\c mydb

Creating a Table

Example table creation:

CREATE TABLE users (

id SERIAL PRIMARY KEY,

name TEXT,

email TEXT,

created_at TIMESTAMP

);

Explanation:

SERIAL automatically generates numbers
PRIMARY KEY uniquely identifies each row
TEXT stores text data
TIMESTAMP stores date and time

Inserting Data into a Table

INSERT INTO users (name, email, created_at)

VALUES ('Ravi', 'ravi@example.com', NOW());

This command adds a new row to the table.

Retrieving Data (SELECT)

View all records:

SELECT * FROM users;

SELECT name, email FROM users;

Updating Data

Update existing data:

UPDATE users

SET email = 'ravi.suhag@example.com'

WHERE id = 1;

The WHERE clause is important to avoid updating all rows.

Deleting Data

Delete a row:

DELETE FROM users

WHERE id = 1;

Always use WHERE with DELETE.

Common PostgreSQL Concepts

Primary Key

Uniquely identifies each row
Cannot be duplicated or NULL

Data Types

Common PostgreSQL data types:

INTEGER
TEXT
BOOLEAN
DATE
TIMESTAMP

Constraints

NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY

Viewing Tables in a Database

\dt

View table structure:

\d users

Summary

Databases store structured data
Tables contain rows and columns
SQL is used to interact with PostgreSQL
Basic commands include CREATE, INSERT, SELECT, UPDATE, DELETE

You now understand the foundation of PostgreSQL.

What’s Next?

Next, we will explore PostgreSQL Data Types in detail.

Next article:
PostgreSQL Data Types Explained with Examples

PostgreSql

Saturday, 18 April 2026