Skip to main content

PostgreSQL Tutorial

PostgreSQL is an open-source relational database management system that uses and extends the SQL language. It is known for its stability, flexibility, and advanced features.

Installation​

Windows​

  1. Go to PostgreSQL Downloads and download the installer.
  2. Run the installer and follow the steps in the installation wizard.
  3. During installation, make sure to install pgAdmin (a PostgreSQL management tool).

Linux​

Use the following commands:

sudo apt update
sudo apt install postgresql postgresql-contrib

macOS​

  1. Install PostgreSQL via Homebrew:
brew install postgresql
  1. Start PostgreSQL:
brew services start postgresql

Basic Concepts​

  • Database: A container for storing data.
  • Table: A collection of rows with the same columns.
  • Row: A single data entry in a table.
  • Column: A specific data field in a table.
  • Primary Key: A unique identifier for a row.
  • Foreign Key: A key used to link one table to another.

Setting Up a Database​

To create a new database:

psql -U postgres
CREATE DATABASE mydb;

To connect to a database:

psql -U postgres -d mydb

Data Types​

PostgreSQL supports various data types:

  • INTEGER: Whole numbers.
  • VARCHAR(n): Variable-length strings with a maximum length of n.
  • TEXT: Unlimited-length strings.
  • BOOLEAN: True or false values.
  • DATE: Date values (e.g., YYYY-MM-DD).

Creating Tables​

Example of creating a table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Inserting Data​

To insert data into a table:

INSERT INTO users (username, email) 
VALUES ('john_doe', 'john@example.com');

Querying Data​

To retrieve data from a table:

SELECT * FROM users;

To filter data:

SELECT * FROM users WHERE username = 'john_doe';

To order results:

SELECT * FROM users ORDER BY created_at DESC;

Updating Data​

To update existing data:

UPDATE users 
SET email = 'new_email@example.com' 
WHERE username = 'john_doe';

Deleting Data​

To delete data from a table:

DELETE FROM users WHERE username = 'john_doe';

Conclusion​

This tutorial provides an introduction to PostgreSQL basics. PostgreSQL is a powerful relational database that offers advanced features like transactions, indexing, and more. You can expand on these concepts by exploring PostgreSQL documentation and advanced topics.

Content Review​

The content in this repository has been reviewed by chevp. Chevp is dedicated to ensuring that the information provided is accurate, relevant, and up-to-date, helping users to learn and implement programming skills effectively.

About the Reviewer​

For more insights and contributions, visit chevp's GitHub profile: chevp's GitHub Profile.