PostgreSQL for Developers: Advanced SQL Features

PostgreSQL is a powerful open-source relational database with advanced features. It supports JSON, arrays, full-text search, and more.

## Key Features

- ACID compliance
- JSON support
- Array data types
- Full-text search
- Window functions
- CTEs (Common Table Expressions)

## JSON Operations

```sql
-- Insert JSON
INSERT INTO users (data) VALUES ('{"name": "John", "age": 30}');

-- Query JSON
SELECT data->>'name' FROM users;
SELECT data->'age' FROM users WHERE data->>'name' = 'John';
```

## Window Functions

```sql
SELECT
 name,
 salary,
 RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;
```

## CTEs

```sql
WITH high_earners AS (
 SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners WHERE department = 'Engineering';
```

## Arrays

```sql
-- Create array column
CREATE TABLE posts (
 id SERIAL PRIMARY KEY,
 tags TEXT[]
);

-- Insert
INSERT INTO posts (tags) VALUES (ARRAY['sql', 'database', 'postgres']);

-- Query
SELECT * FROM posts WHERE 'sql' = ANY(tags);
```

## Full-Text Search

```sql
CREATE INDEX idx_search ON articles USING GIN(to_tsvector('english', content));

SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('postgres & database');
```

## Conclusion

PostgreSQL offers advanced features beyond basic SQL. Master JSON, arrays, and window functions for powerful data operations.