Skip to content

Latest commit

 

History

History
126 lines (97 loc) · 2.74 KB

PostgreSQL.md

File metadata and controls

126 lines (97 loc) · 2.74 KB

6747d053-b51c-8012-ab23-3f0d07bacc15

PostgreSQL is a powerful, open-source object-relational database system known for its reliability, feature robustness, and performance. Here’s a quick overview of its core features and how you can work with it:

Key Features of PostgreSQL:

  1. ACID Compliance
    Ensures transactions are processed reliably.

  2. Extensibility
    Supports custom functions and data types.

  3. Advanced Querying

    • Full-text search.
    • JSON and JSONB data types for handling semi-structured data.

  4. Foreign Data Wrappers (FDWs)
    Enables querying external data sources like CSVs or other databases.

  5. Indexing Support

    • B-tree, Hash, GIN, GiST, and BRIN indexing.
    • Supports partial and expression indexes.

  6. Concurrency Control
    Multi-Version Concurrency Control (MVCC) allows high transaction throughput.

Basic SQL Operations in PostgreSQL

1. Connect to a Database

\c your_database_name

2. Create a Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. Insert Data

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

4. Query Data

SELECT * FROM users WHERE name = 'Alice';

5. Update Data

UPDATE users 
SET email = 'new_email@example.com' 
WHERE id = 1;

6. Delete Data

DELETE FROM users WHERE id = 1;

7. Join Tables

SELECT orders.id, users.name 
FROM orders 
INNER JOIN users ON orders.user_id = users.id;

Advanced Features

JSON Support:

PostgreSQL can handle JSON and JSONB (binary JSON).

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO products (details) 
VALUES ('{"name": "Laptop", "price": 999.99}');

Query JSON Fields:

SELECT details->>'name' AS product_name 
FROM products 
WHERE details->>'price' > '500';

Window Functions:

For advanced analytics:

SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Getting Started with PostgreSQL:

  1. Install PostgreSQL:

    • On Linux: sudo apt-get install postgresql
    • On Mac: brew install postgresql
    • On Windows: Download from official site.

  2. Access the PostgreSQL Shell:

    psql -U username -d database_name

  3. Use a GUI Tool (Optional): Tools like pgAdmin or DBeaver provide user-friendly interfaces for managing databases.

Let me know if you’d like detailed examples or help setting up PostgreSQL!