sql-utilities/docs/SqlUtilities.PostgreSql.md

Strata.SqlTools.PostgreSql

PostgreSQL SQL Query Analysis and Breakdown

---

Overview

The Strata.SqlTools.PostgreSql package provides comprehensive support for parsing, analyzing, and manipulating PostgreSQL SQL queries. It extends the core Strata.SqlTools library with PostgreSQL-specific syntax support, including positional parameters ($1, $2) and named parameters (:param).

Key Features

• ✅ PostgreSQL Syntax Support - Full support for PostgreSQL SQL dialect
• ✅ Positional Parameters - $1, $2, $3 parameter syntax
• ✅ Named Parameters - :parameter and @parameter syntax
• ✅ Query Breakdown - Parse SELECT statements into component clauses
• ✅ Query Collections - Batch analysis with parameter usage reports
• ✅ Statement Parsing - Token-based SQL parsing with PostgreSQL extensions
• ✅ Expression System - Type-safe expression trees for query building
• ✅ Mermaid Diagrams - Visual query structure and flow diagrams

---

Installation

dotnet add package Strata.SqlTools.PostgreSql

Dependencies:

• Strata.SqlTools (core functionality)
• .NET 8.0+

---

Quick Start

Basic Query Breakdown

using Strata.SqlTools.Breakdowns.PostgreSql;

string sql = @"
    SELECT id, name, email, age
    FROM users
    WHERE age > $1
      AND is_active = $2
    ORDER BY name ASC
";

var breakdown = new QueryBreakdown(sql);

Console.WriteLine($"SELECT: {breakdown.SelectClause}");
Console.WriteLine($"FROM: {breakdown.FromClause}");
Console.WriteLine($"WHERE: {breakdown.WhereClause}");
Console.WriteLine($"ORDER BY: {breakdown.OrderByClause}");

// Access parameters
var parameters = breakdown.GetParameters();
foreach (var param in parameters)
{
    Console.WriteLine($"Parameter: {param.Name}");
}

Output:

SELECT: id, name, email, age
FROM: users
WHERE: age > $1 AND is_active = $2
ORDER BY: name ASC
Parameter: $1
Parameter: $2

---

PostgreSQL-Specific Features

Positional Parameters ($n)

PostgreSQL uses $1, $2, etc. for positional parameters:

string sql = @"
    SELECT * FROM orders
    WHERE customer_id = $1
      AND order_date > $2
      AND status = $3
";

var breakdown = new QueryBreakdown(sql);

// Add parameter values
breakdown.AddParameter("$1", 12345);
breakdown.AddParameter("$2", DateTime.Now.AddDays(-30));
breakdown.AddParameter("$3", "Pending");

// Get SQL with parameters
string fullSql = breakdown.GetSql();

Named Parameters (:param or @param)

PostgreSQL also supports named parameters:

string sql = @"
    SELECT * FROM products
    WHERE price > :min_price
      AND category = :category
      AND in_stock = @stock_flag
";

var breakdown = new QueryBreakdown(sql);

breakdown.AddParameter(":min_price", 99.99m);
breakdown.AddParameter(":category", "Electronics");
breakdown.AddParameter("@stock_flag", true);

Parameter Dictionary

Get all parameters as a dictionary:

var breakdown = new QueryBreakdown(sql);
breakdown.AddParameter("$1", 100);
breakdown.AddParameter("$2", "Active");

var paramDict = breakdown.GetParameterDictionary();
foreach (var (name, value) in paramDict)
{
    Console.WriteLine($"{name} = {value}");
}
// Output:
// $1 = 100
// $2 = Active

---

QueryBreakdownCollection

Analyze multiple queries and generate comprehensive reports.

Basic Usage

using Strata.SqlTools.Breakdowns.PostgreSql;

var collection = new QueryBreakdownCollection();

// Add multiple queries
collection.Add(new QueryBreakdown(@"
    SELECT id, name FROM users WHERE age > $1
"));

collection.Add(new QueryBreakdown(@"
    SELECT * FROM orders WHERE user_id = $1 AND status = $2
"));

collection.Add(new QueryBreakdown(@"
    SELECT product_name, price FROM products WHERE category = :category
"));

// Get summaries
var summaries = collection.GetQuerySummaries();
foreach (var summary in summaries)
{
    Console.WriteLine(summary);
}

Parameter Usage Report

The GetParameterUsageReport() method provides detailed information about parameter usage across all queries:

var report = collection.GetParameterUsageReport();

Console.WriteLine($"Total Queries: {report.TotalQueries}");
Console.WriteLine($"Total Parameters: {report.TotalParameters}");
Console.WriteLine($"Unique Parameters: {report.UniqueParameterNames.Count}");

Console.WriteLine("\nPositional Parameters:");
foreach (var (param, count) in report.PositionalParameterUsage)
{
    Console.WriteLine($"  {param}: used {count} times");
}

Console.WriteLine("\nNamed Parameters:");
foreach (var (param, count) in report.NamedParameterUsage)
{
    Console.WriteLine($"  {param}: used {count} times");
}

Example Output:

Total Queries: 3
Total Parameters: 4
Unique Parameters: 3

Positional Parameters:
  $1: used 2 times
  $2: used 1 times

Named Parameters:
  :category: used 1 times

Collection Analysis Methods

var collection = new QueryBreakdownCollection();
// ... add queries ...

// Get total selected columns across all queries
int totalColumns = collection.GetTotalSelectedColumns();

// Get all unique table references
var tables = collection.GetUniqueTableReferences();
Console.WriteLine($"Tables: {string.Join(", ", tables)}");

// Get query summaries
var summaries = collection.GetQuerySummaries();

---

Markdown Visualization

The Strata.SqlTools.Markdown package includes PostgreSQL-specific generators.

Installation

dotnet add package Strata.SqlTools.Markdown

QueryBreakdownGenerator

Generate Mermaid diagrams for individual queries:

using Strata.SqlTools.Markdown.PostgreSql;

var breakdown = new QueryBreakdown(@"
    SELECT u.id, u.name, o.total
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.age > $1
    ORDER BY o.total DESC
");

var generator = new QueryBreakdownGenerator();

// Generate flowchart diagram
string diagram = generator.GenerateMermaidDiagram(breakdown, "User Orders Query");

Example Output:

flowchart TD
    Start([Start]) --> Select[SELECT u.id, u.name, o.total]
    Select --> From[FROM users u]
    From --> Join[JOIN orders o]
    Join --> Where[WHERE u.age > $1]
    Where --> OrderBy[ORDER BY o.total DESC]
    OrderBy --> End([End])

SqlStatementGenerator

Generate sequence and ER diagrams:

var sqlGenerator = new SqlStatementGenerator();

// Sequence diagram showing query execution
string sequenceDiagram = sqlGenerator.GenerateSequenceDiagram(
    breakdown,
    "Query Execution Flow"
);

// Entity-relationship diagram
string erDiagram = sqlGenerator.GenerateEntityRelationshipDiagram(
    breakdown,
    "Database Schema"
);

QueryBreakdownCollectionGenerator

Generate visualizations for collections of queries:

using Strata.SqlTools.Markdown.PostgreSql;

var collection = new QueryBreakdownCollection();
// ... add queries ...

var collectionGenerator = new QueryBreakdownCollectionGenerator();

// Generate summary with all query diagrams
string summary = collectionGenerator.GenerateCollectionSummary(
    collection,
    "Database Queries"
);

// Generate parameter usage visualization
string paramDiagram = collectionGenerator.GenerateParameterUsageDiagram(
    collection,
    "Parameter Analysis"
);

// Generate table reference diagram
string tableDiagram = collectionGenerator.GenerateTableReferenceDiagram(
    collection,
    "Table Dependencies"
);

---

Statement Parsing

StatementParser

Utilities for normalizing and cleaning SQL statements:

using Strata.SqlTools.Statements.PostgreSql;

string sql = @"
    -- This is a comment
    SELECT /* inline comment */ id, name
    FROM users
    WHERE age > 21;
";

// Remove comments
string cleaned = StatementParser.RemoveComments(sql);

// Normalize whitespace
string normalized = StatementParser.NormalizeWhitespace(sql);

StatementReader

Token-based SQL parsing:

using Strata.SqlTools.Statements.PostgreSql;
using Strata.SqlTools.Enums.SQL;

var reader = new StatementReader(sql);

while (reader.Read())
{
    Console.WriteLine($"Token: {reader.TokenType}, Value: '{reader.TokenValue}'");
}

Example Output:

Token: Keyword, Value: 'SELECT'
Token: Identifier, Value: 'id'
Token: Symbol, Value: ','
Token: Identifier, Value: 'name'
Token: Keyword, Value: 'FROM'
Token: Identifier, Value: 'users'
...

---

Advanced Usage

Building Queries Programmatically

var breakdown = new QueryBreakdown("*", "users");

// Add WHERE clauses
breakdown.AddWhereClause("age > $1");
breakdown.AddWhereClause("is_active = $2", "AND");

// Add ORDER BY
breakdown.OrderByClause = "name ASC, created_date DESC";

// Add GROUP BY
breakdown.GroupByClause = "department";
breakdown.HavingClause = "COUNT(*) > 5";

// Add parameters
breakdown.AddParameter("$1", 21);
breakdown.AddParameter("$2", true);

// Generate SQL
string sql = breakdown.GetSql();
Console.WriteLine(sql);

Output:

SELECT *
FROM users
WHERE age > $1 AND is_active = $2
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY name ASC, created_date DESC

Cloning and Modifying Queries

var original = new QueryBreakdown(@"
    SELECT * FROM users WHERE age > $1
");

// Clone the query
var clone = (QueryBreakdown)original.Clone();

// Modify the clone
clone.AddWhereClause("email IS NOT NULL", "AND");
clone.SelectClause = "id, name, email";

// Original remains unchanged
Console.WriteLine(original.GetSql());
Console.WriteLine(clone.GetSql());

Merging Queries

var query1 = new QueryBreakdown("id, name", "users");
query1.AddWhereClause("age > $1");

var query2 = new QueryBreakdown("*", "users");
query2.AddWhereClause("is_active = $1");

// Merge query2 into query1
query1.Merge(query2);

// Result includes WHERE clauses from both
Console.WriteLine(query1.GetSql());

---

Common Table Expressions (CTEs)

PostgreSQL supports WITH clauses:

var mainQuery = new QueryBreakdown("*", "filtered_users");

// Define a CTE
var cteQuery = new QueryBreakdown("id, name, age", "users");
cteQuery.AddWhereClause("age >= $1");

// Add CTE to main query
mainQuery.AddWithClause("filtered_users", cteQuery);

// Generate SQL
string sql = mainQuery.GetSql();
Console.WriteLine(sql);

Output:

WITH filtered_users AS (
    SELECT id, name, age
    FROM users
    WHERE age >= $1
)
SELECT *
FROM filtered_users

---

Parameter Best Practices

1. Use Positional Parameters for Simple Queries

// Good: Simple, sequential positional parameters
var query = new QueryBreakdown(@"
    SELECT * FROM users
    WHERE age > $1 AND department = $2
");
query.AddParameter("$1", 21);
query.AddParameter("$2", "Engineering");

2. Use Named Parameters for Complex Queries

// Good: Named parameters for clarity
var query = new QueryBreakdown(@"
    SELECT * FROM orders
    WHERE customer_id = :customer_id
      AND order_date BETWEEN :start_date AND :end_date
      AND status = :status
");

query.AddParameter(":customer_id", customerId);
query.AddParameter(":start_date", startDate);
query.AddParameter(":end_date", endDate);
query.AddParameter(":status", "Pending");

3. Validate Parameter Count

var breakdown = new QueryBreakdown(sql);
var parameters = breakdown.GetParameters();

// Ensure all parameters have values
foreach (var param in parameters)
{
    if (!breakdown.GetParameterDictionary().ContainsKey(param.Name))
    {
        throw new InvalidOperationException($"Missing value for parameter: {param.Name}");
    }
}

---

Testing

Unit Testing with PostgreSQL Queries

[Test]
public void QueryBreakdown_PostgreSqlSyntax_ParsesCorrectly()
{
    var sql = @"
        SELECT id, name
        FROM users
        WHERE age > $1
          AND status = $2
    ";

    var breakdown = new QueryBreakdown(sql);

    Assert.That(breakdown.SelectClause, Is.EqualTo("id, name"));
    Assert.That(breakdown.FromClause, Is.EqualTo("users"));
    Assert.That(breakdown.WhereClause, Does.Contain("$1"));
    Assert.That(breakdown.WhereClause, Does.Contain("$2"));
}

[Test]
public void ParameterUsageReport_MultipleQueries_CountsCorrectly()
{
    var collection = new QueryBreakdownCollection();
    collection.Add(new QueryBreakdown("SELECT * FROM users WHERE id = $1"));
    collection.Add(new QueryBreakdown("SELECT * FROM orders WHERE user_id = $1"));

    var report = collection.GetParameterUsageReport();

    Assert.That(report.TotalQueries, Is.EqualTo(2));
    Assert.That(report.PositionalParameterUsage["$1"], Is.EqualTo(2));
}

---

PostgreSQL-Specific SQL Features

Array Support

var breakdown = new QueryBreakdown(@"
    SELECT * FROM users
    WHERE tags && $1::text[]
");

breakdown.AddParameter("$1", new[] { "admin", "moderator" });

JSON/JSONB Operators

var breakdown = new QueryBreakdown(@"
    SELECT data->'name' as name
    FROM documents
    WHERE data @> $1::jsonb
");

breakdown.AddParameter("$1", "{\"status\": \"active\"}");

RETURNING Clause

// INSERT with RETURNING
var breakdown = new QueryBreakdown(@"
    INSERT INTO users (name, email)
    VALUES ($1, $2)
    RETURNING id, created_at
");

breakdown.AddParameter("$1", "John Doe");
breakdown.AddParameter("$2", "john@example.com");

---

Related Documentation

• SqlUtilities.Core.md - Core library documentation
• SqlUtilities.SqlServer.md - SQL Server comparison
• SqlUtilities.Snowflake.md - Snowflake comparison

---

API Reference

Key Classes

Class	Purpose
QueryBreakdown	Parse and manipulate PostgreSQL SELECT queries
QueryBreakdownCollection	Manage collections of queries with analysis
StatementParser	SQL parsing utilities
StatementReader	Token-based SQL reader
StatementExpressionParser	Parse SQL into expression trees

Namespaces

• Strata.SqlTools.Breakdowns.PostgreSql - Query breakdown classes
• Strata.SqlTools.Statements.PostgreSql - Statement parsing
• Strata.SqlTools.Visitors.PostgreSql - SQL visitor patterns
• Strata.SqlTools.ExpressionFactory.PostgreSql - Expression factories
• Strata.SqlTools.Markdown.PostgreSql - Markdown generators

---

Version: 1.0.0 Last Updated: February 2026 Package: Strata.SqlTools.PostgreSql