Carrigan.SqlTools.Generators.PostgreSql 0.10.0

<a id="carrigan.sqltools.generators.postgresql"></a>

Carrigan.SqlTools.Generators.PostgreSql

Carrigan.SqlTools.Generators.PostgreSql is a .NET library that adds PostgreSQL-specific SQL generation to Carrigan.SqlTools while still giving you control when you need it.

It automatically generates PostgreSQL SELECT, INSERT, UPDATE, and DELETE statements using reflection. The PostgreSQL generator also provides a safe, object-oriented API for building more advanced PostgreSQL queries.

A companion package, Carrigan.SqlTools.Clients.PostgreSql, provides the PostgreSQL client layer for executing generated queries, mapping rows to objects, and handling decryption of encrypted properties.

The transitive dependency Carrigan.Core provides interfaces and property attributes used for custom property-level encryption and other shared helper behavior.

This package includes PostgreSQL-focused Roslyn analyzers bundled with the package to assist with safe usage patterns.

This project is not affiliated with or endorsed by the PostgreSQL project.

This library may generate or execute SQL. Review generated SQL before use in production systems. Always test against non-production databases first.

Use caution with schema, migration, and data-modifying operations. The authors are not responsible for data loss, downtime, or unintended database changes.

---

Table of Contents

• Features
• Installation
• Getting Started Examples
  • Select All Rows
  • Select by Id
  • Insert
  • Insert with Auto Id
  • Update by Id
  • Update by Id (selected columns)
  • Delete
  • Delete by Id (multiple keys)
• More Complex Examples
  • Select with Joins and Order By
  • Select with Two Part Order By
  • Delete with Using and Where
  • Select Count With Where
  • Update with From and Where
  • GroupBy Example
  • Having Example
• SqlExpression Examples
  • Aggregate Expression Examples
    • Average Examples
    • Avg Examples
    • Count Examples
    • Max Examples
    • Min Examples
    • Sum Examples
  • Conversion Operations
    • Cast Example
  • Date Time Operations
    • CurrentDate Example
    • CurrentTimeStamp Example
    • DateTrunc Example
    • Extract Example
    • MakeDate Example
    • MakeInterval Example
  • Math Operations
    • Abs Example
    • Add Examples
    • Ceiling Example
    • Divide Examples
    • Floor Example
    • Minus Examples
    • Mod Examples
    • Multiply Examples
    • Power Example
    • Negate Examples
    • Sign Example
    • SquareRoot Example
    • Subtract Example
    • Truncate Example
  • Null Operations
    • Coalesce Example
    • NullIf Example
  • Predicate Operations
    • And Examples
    • Between Example
    • Column Example
    • ColumnValue Example
    • ColumnEqualsColumn Example
    • Equal Example
    • GreaterThan Example
    • GreaterThanEquals Example
    • IsNotNull Example
    • IsNull Example
    • LessThan Example
    • LessThanEquals Example
    • Like Example
    • NotBetween Example
    • Not Example
    • NotEqual Example
    • Or Examples
    • Parameter Example
  • String Operations
    • Concat Example
    • IndexOf Example
    • Left Example
    • Length Example
    • Lower Example
    • LTrim Examples
    • Repeat Example
    • Replace Examples
    • Right Example
    • Round Examples
    • RTrim Examples
    • SplitPart Example
    • StrPos Example
    • Substring Example
    • Trim Examples
    • Upper Example
• Attribute Examples
  • Table, Column and Key
  • Identifier and Primary Key
  • Procedure and Parameter
• Running Queries (Async & Non-Async)
  • Async: ExecuteNonQueryAsync / ExecuteScalarAsync / ExecuteReaderAsync<T>
  • Non-Async: ExecuteNonQuery / ExecuteScalar / ExecuteReader<T>
• Simple ADO.NET Example With SqlQuery
• Data Type Mappings
  • Default PostgreSQL Types
  • Supported CLR Types
  • PostgreSQL Type Override Attributes
  • Analyzer Warnings
  • Arrays
• ExampleEncryptor (AesGcm-based) and IDecrypters
• License

---

Features

• Automatic PostgreSQL CRUD generation Build PostgreSQL SELECT, INSERT, UPDATE, and DELETE queries with reflection.

• PostgreSQL dialect rendering Generates double-quoted identifiers, native positional parameters such as $1, RETURNING for insert return values, PostgreSQL UPDATE ... FROM, PostgreSQL DELETE ... USING, and PostgreSQL LIMIT/OFFSET paging.

• Carrigan.Core integration Interfaces and property-level attributes enable property-level encryption and decryption.

• Manual query builder Safely construct advanced SQL with JOIN, predicates such as Equal, GreaterThan, IsNull, Like, And, Or, Xor, and Not, GROUP BY, HAVING, ORDER BY, and PostgreSQL pagination through LimitOffset.

• PostgreSQL-specific predicate behavior LIKE remains case-sensitive by default. Case-insensitive matching uses PostgreSQL ILIKE when requested.

• Dictionary → object mapping support Use the invocation system to populate typed models from database rows.

• Focused on PostgreSQL SQL output targets PostgreSQL syntax and PostgreSQL type names.

• Execution helpers included Includes Carrigan.SqlTools.Clients.PostgreSql for async and non-async helpers that run generated queries through Npgsql.

Table of Contents

---

Installation

Install the PostgreSQL convenience package when you want both PostgreSQL SQL generation and PostgreSQL execution helpers:

dotnet add package Carrigan.SqlTools.PostgreSql

If you only need one side of the dialect support, you can install the generator and client packages separately:

dotnet add package Carrigan.SqlTools.Generators.PostgreSql
dotnet add package Carrigan.SqlTools.Clients.PostgreSql

Table of Contents

---

Getting Started Examples

Use Carrigan.SqlTools.PostgreSql.SqlGenerator<T> to produce a SqlQuery with QueryText, CommandType, and Parameters.

All examples use the following using statements to keep code clean.

using Carrigan.SqlTools.Attributes;
using Carrigan.SqlTools.Base.Tests.Helpers;
using Carrigan.SqlTools.Base.Tests.TestEntities;
using Carrigan.SqlTools.JoinTypes;
using Carrigan.SqlTools.OrderByClause;
using Carrigan.SqlTools.PredicatesLogic;
using Carrigan.SqlTools.PostgreSql;
using Carrigan.SqlTools.Sets;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.Tags;
using System.Text;

// Example data models
public class Customer
{
    [PrimaryKey] // note: PrimaryKey takes precedence over Key for the SQL generator.
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public string Phone { get; set; } = "";
}

public class Order
{
    [PrimaryKey] // Required for key-based SQL generation methods.
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int PaymentMethodId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
}

// Generators
public SqlGenerator<Customer> customerGenerator = new();
public SqlGenerator<Order> orderGenerator = new();

Table of Contents

Select All Rows

SqlQuery query = customerGenerator.SelectAll();

SELECT "Customer".* FROM "Customer"

SelectBuilder<Customer> selectBuilder = new();
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* FROM "Customer"

Table of Contents

Select by Id

Key attribute required, and composite keys are supported by specifying multiple keys.

Customer entity = new() { Id = 42 };
SqlQuery query = customerGenerator.SelectById(entity);

SELECT "Customer".*
FROM "Customer"
WHERE ("Customer"."Id" = $1)

Table of Contents

Insert

Customer entity = new()
{
    Id = 42,
    Name = "Hank",
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555"
};

InsertBuilder<Customer> insertBuilder = new()
{
    Records = [entity]
};

SqlQuery query = customerGenerator.Insert(insertBuilder);

INSERT INTO "Customer" ("Id", "Name", "Email", "Phone")
VALUES ($1, $2, $3, $4);

Table of Contents

Insert with Auto Id

Key attribute required, and Id columns must have a default value.

Customer entity = new()
{
    Name = "Hank",
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555"
};

SqlQuery query = customerGenerator.InsertAutoId(entity);

INSERT INTO "Customer" ("Name", "Email", "Phone")
VALUES ($1, $2, $3)
RETURNING "Id";

Table of Contents

Update by Id

Key attribute required, and composite keys are supported by specifying multiple keys.

Customer entity = new()
{
    Id = 42,
    Name = "Hank",
    Email = "Hank@example.com",
    Phone = "+1(555)555-5555"
};

SqlQuery query = customerGenerator.UpdateById(entity);

UPDATE "Customer"
SET "Name" = $1, "Email" = $2, "Phone" = $3
WHERE "Id" = $4;

Table of Contents

<a id="update-by-id-selected-columns"></a>

Update by Id (selected columns)

Key attribute required, and composite keys are supported by specifying multiple keys.

ColumnCollection<T> validates property names and throws an error when a property name is not valid for the model.

ColumnCollection<Customer> columns = new(nameof(Customer.Email));
Customer entity = new() { Id = 42, Name = "Hank", Email = "Hank@example.gov" };
SqlQuery query = customerGenerator.UpdateById(entity, columns);

UPDATE "Customer"
SET "Email" = $1
WHERE "Id" = $2;

ColumnCollection<T> validates property names and throws an error when a property name is not valid for the model.

UpdateBuilder<Customer> updateBuilder = new()
{
    Where = new ColumnValue<Customer>(nameof(Customer.Id), 42),
    UpdateColumns = new ColumnCollection<Customer>(nameof(Customer.Email)),
    Values = new() { Email = "Hank@example.gov" }
};
SqlQuery query = updateBuilder.AsSqlQuery();

UPDATE "Customer"
SET "Email" = $1
WHERE ("Customer"."Id" = $2)

Table of Contents

Delete

Key attribute required, and composite keys are supported by specifying multiple keys.

Customer entity = new() { Id = 42 };
SqlQuery query = customerGenerator.Delete(entity);

DELETE
FROM "Customer"
WHERE ("Customer"."Id" = $1)

Note: ColumnCollection<T> validates the names of the properties, and throws an error if the property isn't valid

DeleteBuilder<Customer> deleteBuilder = new()
{
    Where = new ColumnValue<Customer>(nameof(Customer.Id), 42),
};
SqlQuery query = deleteBuilder.AsSqlQuery();

DELETE
FROM "Customer"
WHERE ("Customer"."Id" = $1)

Table of Contents

<a id="delete-by-id-multiple-keys"></a>

Delete by Id (multiple keys)

Key attribute required, and composite keys are supported by specifying multiple keys.

Customer[] entities = [new() { Id = 1 }, new() { Id = 2 }];
SqlQuery query = customerGenerator.DeleteById(entities);

DELETE FROM "Customer"
WHERE (("Customer"."Id" = $1)
   OR ("Customer"."Id" = $2))

Note: ColumnCollection<T> validates the names of the properties, and throws an error if the property isn't valid

DeleteBuilder<Customer> deleteBuilder = new()
{
    Where = new Or
    (
        new ColumnValue<Customer>(nameof(Customer.Id), 1),
        new ColumnValue<Customer>(nameof(Customer.Id), 2)
    )
};
SqlQuery query = deleteBuilder.AsSqlQuery();

DELETE FROM "Customer"
WHERE (("Customer"."Id" = $1)
   OR ("Customer"."Id" = $2))

Table of Contents

---

More Complex Examples

Use SqlGenerator<T> to produce a SqlQuery with QueryText, CommandType, and Parameters.

All examples use the following using statements to keep code clean.

using Carrigan.SqlTools.AggregateLogic;
using Carrigan.SqlTools.Expressions;
using Carrigan.SqlTools.GroupByClause;
using Carrigan.SqlTools.JoinTypes;
using Carrigan.SqlTools.OrderByClause;
using Carrigan.SqlTools.PredicatesLogic;
using Carrigan.SqlTools.Sets;
using Carrigan.SqlTools.SqlGenerators;
using Carrigan.SqlTools.SqlServer;
using Carrigan.SqlTools.Tags;

// Example data models
public class Customer
{
    [PrimaryKey]
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public string Phone { get; set; } = "";
}

public class Order
{
    [PrimaryKey]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int PaymentMethodId { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
}

// Generators
public SqlGenerator<Customer> customerGenerator = new();
public SqlGenerator<Order> orderGenerator = new();

Select with Joins and Order By

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid for the model.

OrderBy<Order> validates property names and throws an error when a property name is not valid for Order.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));
InnerJoin<Order> join = new(predicate);

OrderBy<Order> orderByOrderDate = new(nameof(Order.OrderDate));

SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join,
    OrderBys = orderByOrderDate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer"
INNER JOIN "Order"
   ON ("Customer"."Id" = "Order"."CustomerId")
ORDER BY "Order"."OrderDate" ASC

Table of Contents

Select with Two Part Order By

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid for the model.

OrderBy<T> validates property names and throws an error when a property name is not valid for the specified model.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));

InnerJoin<Order> join = new(predicate);

OrderBy<Order> orderByOrderDate = new(nameof(Order.OrderDate));
OrderBy<Customer> orderByCustomerId = new(nameof(Customer.Id), SortDirectionEnum.Descending);
OrderBys orderBys = new(orderByCustomerId, orderByOrderDate);

SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join,
    OrderBys = orderBys
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer"
INNER JOIN "Order"
   ON ("Customer"."Id" = "Order"."CustomerId")
ORDER BY "Customer"."Id" DESC,
         "Order"."OrderDate" ASC

Table of Contents

Delete with Using and Where

PostgreSQL joined deletes use DELETE FROM target USING source ... WHERE .... The target table is not repeated as a joined FROM source.

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid for the model.

ColumnValue<T> validates property names and throws an error when a property name is not valid for the model.

ColumnEqualsColumn<Customer, Order> predicate = new(nameof(Customer.Id), nameof(Order.CustomerId));
ColumnValue<Customer> customerEmail = new(nameof(Customer.Email), "spam@example.com");

DeleteBuilder<Order> deleteBuilder = new()
{
    Usings = [TableTag.Get<Customer>()],
    Where = new And(predicate, customerEmail)
};

SqlQuery query = orderGenerator.Delete(deleteBuilder);

DELETE FROM "Order" USING "Customer"
WHERE (("Customer"."Id" = "Order"."CustomerId")
  AND ("Customer"."Email" = $1))

Table of Contents

Select Count With Where

Column<T> validates property names and throws an error when a property name is not valid for the model.

Column<Order> totalCol = new(nameof(Order.Total));
Parameter minTotal = new(500m, "Total");
GreaterThan greaterThan = new(totalCol, minTotal);

SelectBuilder<Order> selectBuilder = new()
{
    Selects = new SelectTag(new Count(), "OrderCount"),
    Where = greaterThan
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT(*) AS "OrderCount"
FROM "Order"
WHERE ("Order"."Total" > $1)

Table of Contents

Update with From and Where

PostgreSQL joined updates use UPDATE target SET ... FROM source ... WHERE .... The update target is not repeated in the FROM clause unless you are intentionally doing a self-join with an alias.

ColumnCollection<T> validates property names and throws an error when a property name is not valid for the model.

ColumnEqualsColumn<LeftT, RightT> validates property names and throws an error when a property name is not valid for the model.

ColumnValue<T> validates property names and throws an error when a property name is not valid for the model.

Order entity = new() { Id = 10, Total = 123.45m };

ColumnCollection<Order> columnCollection = new(nameof(Order.Total));

ColumnEqualsColumn<Order, Customer> predicate = new(nameof(Order.CustomerId), nameof(Customer.Id));
ColumnValue<Customer> customerEmailEquals = new(nameof(Customer.Email), "spam@example.com");

UpdateBuilder<Order> updateBuilder = new()
{
    Values = entity,
    UpdateColumns = columnCollection,
    From = [TableTag.Get<Customer>()],
    Where = new And(predicate, customerEmailEquals)
};

SqlQuery query = orderGenerator.Update(updateBuilder);

UPDATE "Order"
SET "Total" = $1
FROM "Customer"
WHERE (("Order"."CustomerId" = "Customer"."Id")
  AND ("Customer"."Email" = $2))

Table of Contents

GroupBy Example

Column<Grades> gradePoint = new(nameof(Grades.GradePoint));

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        SelectTagGenerator.Get<Grades>(nameof(Grades.StudentId)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.CourseCode)),
        new SelectTag(new Average(gradePoint), "AverageGradePoint"),
        new SelectTag(new Sum(gradePoint), "TotalGradePoints"),
        new SelectTag(new Min(gradePoint), "MinimumGradePoint"),
        new SelectTag(new Max(gradePoint), "MaximumGradePoint"),
        new SelectTag(new Count(gradePoint), "GradePointCount")
    ),
    GroupBys = new GroupBys<Grades>(nameof(Grades.StudentId))
        .Append<Grades>(nameof(Grades.CourseCode))
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT 
    "Grades"."StudentId", 
    "Grades"."CourseCode",
    AVG("Grades"."GradePoint") AS "AverageGradePoint",
    SUM("Grades"."GradePoint") AS "TotalGradePoints", 
    MIN("Grades"."GradePoint") AS "MinimumGradePoint", 
    MAX("Grades"."GradePoint") AS "MaximumGradePoint",
    COUNT("Grades"."GradePoint") AS "GradePointCount" 
FROM "Grades" 
GROUP BY
    "Grades"."StudentId",
    "Grades"."CourseCode"

Table of Contents

---

Having Example

Average semesterGpa = new(new Column<Grades>(nameof(Grades.GradePoint)));

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        SelectTagGenerator.Get<Grades>(nameof(Grades.StudentId)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.AcademicYear)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.SemesterNumber)),
        new SelectTag(semesterGpa, "SemesterGPA")
    ),
    GroupBys = GroupBys.New<Grades>
    (
        nameof(Grades.StudentId),
        nameof(Grades.AcademicYear),
        nameof(Grades.SemesterNumber)
    ),
    Having = new GreaterThan(semesterGpa, new Parameter(3.5, "HonorRollGpa"))
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT
    "Grades"."StudentId",
    "Grades"."AcademicYear",
    "Grades"."SemesterNumber",
    AVG("Grades"."GradePoint") AS "SemesterGPA"
FROM "Grades"
GROUP BY
    "Grades"."StudentId",
    "Grades"."AcademicYear",
    "Grades"."SemesterNumber"
HAVING (AVG("Grades"."GradePoint") > $1)

Table of Contents

---

SqlExpression Examples

Aggregate Expression Examples

Column<Grades> gradePoint = new(nameof(Grades.GradePoint));

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        SelectTagGenerator.Get<Grades>(nameof(Grades.StudentId)),
        SelectTagGenerator.Get<Grades>(nameof(Grades.CourseCode)),
        new SelectTag(new Average(gradePoint), "AverageGradePoint"),
        new SelectTag(new Sum(gradePoint), "TotalGradePoints"),
        new SelectTag(new Min(gradePoint), "MinimumGradePoint"),
        new SelectTag(new Max(gradePoint), "MaximumGradePoint"),
        new SelectTag(new Count(gradePoint), "GradePointCount")
    ),
    GroupBys = new GroupBys<Grades>(nameof(Grades.StudentId))
        .Append<Grades>(nameof(Grades.CourseCode))
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT 
    "Grades"."StudentId", 
    "Grades"."CourseCode", 
    AVG("Grades"."GradePoint") AS "AverageGradePoint", 
    SUM("Grades"."GradePoint") AS "TotalGradePoints",
    MIN("Grades"."GradePoint") AS "MinimumGradePoint", 
    MAX("Grades"."GradePoint") AS "MaximumGradePoint", 
    COUNT("Grades"."GradePoint") AS "GradePointCount" 
FROM "Grades" 
GROUP BY 
    "Grades"."StudentId",
    "Grades"."CourseCode"

---

Average Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Average(new Column<Grades>(nameof(Grades.GradePoint))),
            "OverallAverageGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG("Grades"."GradePoint") AS "OverallAverageGradePoint" FROM "Grades"

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Average(new Column<Grades>(nameof(Grades.GradePoint)), true),
            "OverallAverageGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG(DISTINCT "Grades"."GradePoint") AS "OverallAverageGradePoint" FROM "Grades"

Table of Contents

---

Avg Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Avg(new Column<Grades>(nameof(Grades.GradePoint))),
            "OverallAvgGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG("Grades"."GradePoint") AS "OverallAvgGradePoint" FROM "Grades"

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Avg(new Column<Grades>(nameof(Grades.GradePoint)), true),
            "OverallAvgGradePoint"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT AVG(DISTINCT "Grades"."GradePoint") AS "OverallAvgGradePoint" FROM "Grades"

Table of Contents

---

Count Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Count(new Column<Grades>(nameof(Grades.GradePoint))),
            "GradePointCount"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT("Grades"."GradePoint") AS "GradePointCount" FROM "Grades"

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Count(new Column<Grades>(nameof(Grades.GradePoint)), true),
            "GradePointCount"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT(DISTINCT "Grades"."GradePoint") AS "GradePointCount" FROM "Grades"

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Count(),
            "GradeRecordCount"
        )
    )
};

SqlQuery query = selectBuilder.AsSqlQuery();

SELECT COUNT(*) AS "GradeRecordCount" FROM "Grades"

Table of Contents

---

Max Examples

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Max(new Column<Grades>(nameof(Grades.GradePoint))),
                    "MaximumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MAX("Grades"."GradePoint") AS "MaximumGradePoint" FROM "Grades"

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Max(new Column<Grades>(nameof(Grades.GradePoint)), true),
                    "MaximumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MAX(DISTINCT "Grades"."GradePoint") AS "MaximumGradePoint" FROM "Grades"

Table of Contents

---

Min Examples

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Min(new Column<Grades>(nameof(Grades.GradePoint))),
                    "MinimumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MIN("Grades"."GradePoint") AS "MinimumGradePoint" FROM "Grades"

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Min(new Column<Grades>(nameof(Grades.GradePoint)), true),
                    "MinimumGradePoint"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT MIN(DISTINCT "Grades"."GradePoint") AS "MinimumGradePoint" FROM "Grades"

Table of Contents

---

Sum Examples

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Sum(new Column<Grades>(nameof(Grades.GradePoint))),
                    "TotalGradePoints"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT SUM("Grades"."GradePoint") AS "TotalGradePoints" FROM "Grades"

        SelectBuilder<Grades> selectBuilder = new()
        {
            Selects = new SelectTags
            (
                new SelectTag
                (
                    new Sum(new Column<Grades>(nameof(Grades.GradePoint)), true),
                    "TotalGradePoints"
                )
            )
        };

        SqlQuery query = selectBuilder.AsSqlQuery();

SELECT SUM(DISTINCT "Grades"."GradePoint") AS "TotalGradePoints" FROM "Grades"

Table of Contents

---

Conversion Operations

Cast Example

Cast expression = new(new Parameter("123"), PostgreSqlTypesProvider.AsNumeric(false, true));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CAST($1 AS NUMERIC) AS "Value" FROM "Customer"

Table of Contents

---

Date Time Operations

CurrentDate Example

CurrentDate currentDate = new();
ColumnBase columnBase = new Column<Customer>(nameof(Customer.Name));
SelectBuilder<Customer> selectBuilder = new()
{
    Selects = new SelectTags(new SelectTag(currentDate, "Date"), new SelectTag(columnBase, "Name"))
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CURRENT_DATE AS "Date", "Customer"."Name" AS "Name" FROM "Customer"

Table of Contents

---

CurrentTimeStamp Example

CurrentTimeStamp currentTimeStamp = new();
ColumnBase columnBase = new Column<Customer>(nameof(Customer.Name));
SelectBuilder<Customer> selectBuilder = new()
{
    Selects = new SelectTags(new SelectTag(currentTimeStamp, "TimeStamp"), new SelectTag(columnBase, "Name"))
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CURRENT_TIMESTAMP AS "TimeStamp", "Customer"."Name" AS "Name" FROM "Customer"

Table of Contents

---

DateTrunc Example

DateTrunc expression = new(DateTruncDateTimePartEnum.Month, new Parameter(new DateTime(2026, 9, 23)));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT DATE_TRUNC('month', $1) AS "Value" FROM "Customer"

Table of Contents

---

Extract Example

Extract expression = new(ExtractDateTimePartEnum.Year, new Parameter(new DateTime(2026, 9, 23)));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT EXTRACT(year FROM $1) AS "Value" FROM "Customer"

Table of Contents

---

MakeDate Example

MakeDate expression = new(new Parameter(2026), new Parameter(9), new Parameter(23));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT MAKE_DATE($1, $2, $3) AS "Value" FROM "Customer"

Table of Contents

---

MakeInterval Example

MakeInterval expression = new(MakeIntervalDateTimePartEnum.Day, new Parameter(2));
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT MAKE_INTERVAL(days => $1) AS "Value" FROM "Customer"

Table of Contents

Math Operations

Abs Example

Abs abs = new(new Column<Order>(nameof(Order.Total)));
SelectTags selects = new(new SelectTag(abs, "AbsValue"));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT ABS("Order"."Total") AS "AbsValue" FROM "Order"

Table of Contents

---

Add Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Add
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(1)
            ),
            "ArthemicResult"
        )
    )
};
SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ("Grades"."CreditHours" + $1) AS "ArthemicResult" FROM "Grades"

Table of Contents

---

Ceiling Example

Ceiling expression = new(new Column<Order>(nameof(Order.Total)));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT CEILING("Order"."Total") AS "Value" FROM "Order"

Table of Contents

---

Divide Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Divide
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(2)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ("Grades"."CreditHours" / @Parameter_1) AS "ArthemicResult" FROM "Grades"

Table of Contents

---

Floor Example

Floor expression = new(new Column<Order>(nameof(Order.Total)));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT FLOOR("Order"."Total") AS "Value" FROM "Order"

Table of Contents

---

Minus Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Minus
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(1)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ("Grades"."CreditHours" - @Parameter_1) AS "ArthemicResult" FROM "Grades"

Table of Contents

---

Mod Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Mod
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(2)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ("Grades"."CreditHours" % @Parameter_1) AS "ArthemicResult" FROM "Grades"

Table of Contents

---

Multiply Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Multiply
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(2)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ("Grades"."CreditHours" * @Parameter_1) AS "ArthemicResult" FROM "Grades"

Table of Contents

---

Negate Examples

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Negate
            (
                new Column<Grades>(nameof(Grades.CreditHours))
            ),
            "ArthemicResult"
        )
    )
};

SELECT (-"Grades"."CreditHours") AS "ArthemicResult" FROM "Grades"

Table of Contents

---

Power Example

Power expression = new(new Column<Order>(nameof(Order.Total)), 2);

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT POWER(\"Order\".\"Total\", @Parameter_1) AS \"Value\" FROM \"Order\"

Table of Contents

---

Sign Example

Sign expression = new(new Column<Order>(nameof(Order.Total)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT SIGN("Order"."Total") AS "Value" FROM "Order"

Table of Contents

---

SquareRoot Example

SquareRoot expression = new(new Column<Order>(nameof(Order.Total)));

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT SQRT("Order"."Total") AS "Value" FROM "Order"

Table of Contents

---

Subtract Example

SelectBuilder<Grades> selectBuilder = new()
{
    Selects = new SelectTags
    (
        new SelectTag
        (
            new Subtract
            (
                new Column<Grades>(nameof(Grades.CreditHours)),
                new Parameter(1)
            ),
            "ArthemicResult"
        )
    )
};

SqlQuery sqlQuery = selectBuilder.AsSqlQuery();

SELECT ("Grades"."CreditHours" - $1) AS "ArthemicResult" FROM "Grades"

Table of Contents

---

Truncate Example

Truncate expression = new(new Column<Order>(nameof(Order.Total)), 2);

SelectBuilder<Order> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = orderGenerator.Select(selectBuilder

SELECT TRUNC("Order"."Total", $1) AS "Value" FROM "Order"

Table of Contents

---

Null Operations

Coalesce Example

Coalesce coalesce = new(new Column<Customer>(nameof(Customer.Phone)), new Column<Customer>(nameof(Customer.Email)));
SelectTags selects = new(new SelectTag(coalesce, "Coalescence"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT COALESCE("Customer"."Phone", "Customer"."Email") AS "Coalescence" FROM "Customer"

Table of Contents

---

NullIf Example

NullIf nullIf = new(new Column<Customer>(nameof(Customer.Phone)), new Parameter(string.Empty));
SelectTags selects = new(new SelectTag(nullIf, "NullIf"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT NULLIF("Customer"."Phone", $1) AS "NullIf" FROM "Customer"

Table of Contents

---

Predicate Operations

And Examples

ColumnValue<Customer> equalName = new(nameof(Customer.Name), "Hank");
ColumnValue<Customer> equalEmail = new(nameof(Customer.Email), "Hank@example.com");
ColumnValue<Customer> equalPhone = new(nameof(Customer.Phone), "+1(555)555-5555");
And and = new(equalName, equalEmail, equalPhone);

SelectBuilder<Customer> selectBuilder = new()
{
    Where = and
};

SqlQuery query = customerGenerator.Select(selectBuilder);

This method of having an And with only one predicate is supported for uses cases when you might only have one predicate, or might have more. This way you don't have to test for an the only one edge case. Instead And intelligently ignores itself.

SELECT [Customer].* 
FROM [Customer] 
WHERE (([Customer].[Name] = @Name_1) 
  AND ([Customer].[Email] = @Email_2) 
  AND ([Customer].[Phone] = @Phone_3))

SELECT "Customer".*
FROM "Customer"
WHERE (("Customer"."Name" = $1) 
  AND ("Customer"."Email" = $2)
  AND ("Customer"."Phone" = $3))
And and = new(equalName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = and
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer" 
WHERE ("Customer"."Name" = $1)

Table of Contents

---

Between Example

Parameter minimumTotal = new(100.00m, "MinimumTotal");
Parameter maximumTotal = new(500.00m, "MaximumTotal");
Column<Order> columnTotal = new(nameof(Order.Total));
Between predicate = new(columnTotal, minimumTotal, maximumTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT "Order".* 
FROM "Order" 
WHERE ("Order"."Total" BETWEEN $1 AND $2)

Table of Contents

---

Column Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
WHERE ("Customer"."Name" = $1)

Table of Contents

---

ColumnValue Example

ColumnValue<Customer> columnValue = new(nameof(Customer.Name), "Hank");
SelectBuilder<Customer> selectBuilder = new()
{
    Where = columnValue
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer"
WHERE ("Customer"."Name" = $1)

Table of Contents

---

ColumnEqualsColumn Example

Note: ColumnEqualsColumn<leftT, rightT> validates the names of the properties, and throws an error if the property isn't valid

//Note: 
ColumnEqualsColumn<Customer, Order> columnValue = new(nameof(Customer.Id), nameof(Order.CustomerId));
LeftJoin<Order> join = new(columnValue);
SelectBuilder<Customer> selectBuilder = new()
{
    Joins = join
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
LEFT JOIN "Order"
  ON ("Customer"."Id" = "Order"."CustomerId")

Table of Contents

---

Equal Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer"
WHERE ("Customer"."Name" = $1)

Table of Contents

---

GreaterThan Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
GreaterThan predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT "Order".*
FROM "Order" 
WHERE ("Order"."Total" > $1)

Table of Contents

---

GreaterThanEquals Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
GreaterThanEqual predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT "Order".* 
FROM "Order" 
WHERE ("Order"."Total" >= $1)

Table of Contents

---

IsNotNull Example

Column<Customer> columnName = new(nameof(Customer.Name));
IsNotNull notNull = new(columnName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = notNull
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
WHERE ("Customer"."Name" IS NOT NULL)

Table of Contents

---

IsNull Example

Column<Customer> columnName = new(nameof(Customer.Name));
IsNull isNull = new(columnName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = isNull
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
WHERE ("Customer"."Name" IS NULL)

Table of Contents

---

LessThan Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
LessThan predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT "Order".*
FROM "Order" 
WHERE ("Order"."Total" < $1)

Table of Contents

---

LessThanEquals Example

Parameter parameterTotal = new(1776.00m, "Total");
Column<Order> columnTotal = new(nameof(Order.Total));
LessThanEqual predicate = new(columnTotal, parameterTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT "Order".* 
FROM "Order"
WHERE ("Order"."Total" <= $1)

Table of Contents

---

Like Example

Parameter parameterEmail = new("%@example.com", "Email");
Column<Customer> columnEmail = new(nameof(Customer.Email));
Like predicate = new(columnEmail, parameterEmail);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer" 
WHERE ("Customer"."Email" LIKE $1)

Table of Contents

---

NotBetween Example

Parameter minimumTotal = new(100.00m, "MinimumTotal");
Parameter maximumTotal = new(500.00m, "MaximumTotal");
Column<Order> columnTotal = new(nameof(Order.Total));
NotBetween predicate = new(columnTotal, minimumTotal, maximumTotal);
SelectBuilder<Order> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = orderGenerator.Select(selectBuilder);

SELECT "Order".*
FROM "Order"
WHERE ("Order"."Total" NOT BETWEEN $1 AND $2)

Table of Contents

---

Not Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equal = new(columnName, parameterName);
Not not = new(equal);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = not
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
WHERE (NOT ("Customer"."Name" = $1))

Table of Contents

---

NotEqual Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
NotEqual predicate = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = predicate
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer" 
WHERE ("Customer"."Name" <> $1)

Table of Contents

---

Or Examples

        ColumnValue<Customer> equalName = new(nameof(Customer.Name), "Hank");
        ColumnValue<Customer> equalEmail = new(nameof(Customer.Email), "Hank@example.com");
        ColumnValue<Customer> equalPhone = new(nameof(Customer.Phone), "+1(555)555-5555");
        Or or = new(equalName, equalEmail, equalPhone);

        SelectBuilder<Customer> selectBuilder = new()
        {
            Where = or
        };

        SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
WHERE (("Customer"."Name" = $1)
   OR ("Customer"."Email" = $2) 
   OR ("Customer"."Phone" = $3))

This method of having an Or with only one predicate is supported for uses cases when you might only have one predicate, or might have more. This way you don't have to test for an the only one edge case. Instead OR intelligently ignores itself.

ColumnValue<Customer> equalName = new(nameof(Customer.Name), "Hank");
Or or = new(equalName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = or
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
WHERE ("Customer"."Name" = $1)

Table of Contents

---

Parameter Example

Parameter parameterName = new("Hank", "Name");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".*
FROM "Customer" 
WHERE ("Customer"."Name" = $1)

Parameter<Customer> parameterName = new(nameof(Customer.Name), "Hank");
Column<Customer> columnName = new(nameof(Customer.Name));
Equal equalName = new(columnName, parameterName);
SelectBuilder<Customer> selectBuilder = new()
{
    Where = equalName
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT "Customer".* 
FROM "Customer" 
WHERE ("Customer"."Name" = $1)

Table of Contents

---

String Operations

Concat Example

Coalesce coalesce = new(new Column<Customer>(nameof(Customer.Phone)), new Column<Customer>(nameof(Customer.Email)));
SelectTags selects = new(new SelectTag(coalesce, "Coalescence"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT CONCAT("Customer"."Phone", "Customer"."Email") AS "Value" FROM "Customer"

Table of Contents

---

IndexOf Example

IndexOf expression = new(new Column<Customer>(nameof(Customer.Name)), "a");

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT STRPOS("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

Left Example

Length expression = new(new Column<Customer>(nameof(Customer.Name)));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LEFT("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

Length Example

Length expression = new(new Column<Customer>(nameof(Customer.Name)));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LENGTH("Customer"."Name") AS "Value" FROM "Customer"

Table of Contents

---

Lower Example

Lower expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LOWER("Customer"."Name") AS "Value" FROM "Customer"

Table of Contents

---

LTrim Examples

LTrim expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LTRIM("Customer"."Name") AS "Value" FROM "Customer"

Note: When a trim string is specified, it is encapsulated in a parameter to help prevent SQL injection.

LTrim expression = new(new Column<Customer>(nameof(Customer.Name)), " x");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT LTRIM("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

Repeat Example

Repeat expression = new(new Column<Customer>(nameof(Customer.Name)), 2);

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT REPEAT("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

Replace Examples

Replace expression = new(new Column<Customer>(nameof(Customer.Name)), new Parameter("'"), new Parameter(" "));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT REPLACE("Customer"."Name", $1, $2) AS "Value" FROM "Customer"

Note: When a replace string is specified, it is encapsulated in a parameter to help prevent SQL injection.

Replace expression = new(new Column<Customer>(nameof(Customer.Name)), "'", " ");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT REPLACE("Customer"."Name", $1, $2) AS "Value" FROM "Customer"

Table of Contents

---

Right Example

Right expression = new(new Column<Customer>(nameof(Customer.Name)), 3);

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT RIGHT("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

Round Examples

Round expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT ROUND("Customer"."Name") AS "Value" FROM "Customer"

Note: When a precision value is specified, it is encapsulated in a parameter to help prevent SQL injection.

ROUND expression = new(new Column<Customer>(nameof(Customer.Name)), " x");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT ROUND("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

RTrim Examples

RTrim expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT RTRIM("Customer"."Name") AS "Value" FROM "Customer"

Note: When a trim string is specified, it is encapsulated in a parameter to help prevent SQL injection.

RTrim expression = new(new Column<Customer>(nameof(Customer.Name)), " x");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT RTRIM("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

SplitPart Example

SplitPart expression = new(new Column<Customer>(nameof(Customer.Name)), " ", 2);
SelectBuilder<Customer> selectBuilder = new() { Selects = expression.AsSelectTag("Value") };
SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT SPLIT_PART("Customer"."Name", $1, $2) AS "Value" FROM "Customer"

Table of Contents

---

StrPos Example

StrPos expression = new(new Column<Customer>(nameof(Customer.Name)), "a");

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT STRPOS("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

Substring Example

Substring expression = new(new Column<Customer>(nameof(Customer.Name)), 2, 3);

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = expression.AsSelectTag("Value")
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT SUBSTRING("Customer"."Name", $1, $2) AS "Value" FROM "Customer"

Table of Contents

---

Trim Examples

Trim expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT TRIM("Customer"."Name") AS "Value" FROM "Customer"

Note: When a trim string is specified, it is encapsulated in a parameter to help prevent SQL injection.

Trim expression = new(new Column<Customer>(nameof(Customer.Name)), " x");
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT TRIM("Customer"."Name", $1) AS "Value" FROM "Customer"

Table of Contents

---

Upper Example

Upper expression = new(new Column<Customer>(nameof(Customer.Name)));
SelectTags selects = new(new SelectTag(expression, "Value"));

SelectBuilder<Customer> selectBuilder = new()
{
    Selects = selects
};

SqlQuery query = customerGenerator.Select(selectBuilder);

SELECT UPPER("Customer"."Name") AS "Value" FROM "Customer"

Table of Contents

---

Attribute Examples

Use SqlGenerator<T> to produce a SqlQuery with QueryText, CommandType, and Parameters.

You can use the [Table] attribute from System.ComponentModel.DataAnnotations.Schema to override the table name. When no table attribute or IdentifierAttribute is present, the table name is assumed to be the same as the class name. You can also specify a schema name.

All examples use using statements to keep code clean.

using Carrigan.SqlTools.Attributes;
using Carrigan.SqlTools.Base.Tests.Helpers;
using Carrigan.SqlTools.Base.Tests.TestEntities;
using Carrigan.SqlTools.PostgreSql;
using Carrigan.SqlTools.SqlGenerators;

// Generators
public SqlGenerator<PhoneModel> phoneGenerator = new();
public SqlGenerator<EmailModel> emailGenerator = new();
public SqlGenerator<ProcedureExec> procedureExecGenerator = new();

<a id="table-column-and-key"></a>

Table, Column and Key

[Table("Phone", Schema="schema")]
internal class PhoneModel
{
    [Key]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    [Column("Phone")]
    public string? PhoneNumber { get; set; }
}

PhoneModel phone = new()
{
    Id = 2718,
    CustomerId = 3141,
    PhoneNumber = "07700 900461"
};

SqlQuery query = phoneGenerator.UpdateById(phone);

UPDATE "schema"."Phone"
SET "CustomerId" = $1, "Phone" = $2
WHERE "Id" = $3;

Table of Contents

Identifier and Primary Key

[Identifier("Email", "schema")]
internal class EmailModel
{
    [PrimaryKey]
    public int Id { get; set; }
    public int CustomerId { get; set; }
    [Identifier("Email")]
    public string? EmailAddress { get; set; }
}

EmailModel email = new()
{
    Id = 10,
    CustomerId = 313,
    EmailAddress = "Exterminate@GenericTinCanLand.gov"
};

SqlQuery query = emailGenerator.UpdateById(email);

UPDATE "schema"."Email"
SET "CustomerId" = $1, "Email" = $2
WHERE "Id" = $3;

Table of Contents

Procedure and Parameter

[Identifier("UpdateThing", "schema")]
internal class ProcedureExec
{
    [Parameter("SomeValue")]
    public string? ValueColumn { get; set; }
}

ProcedureExec procedureExec = new()
{
    ValueColumn = "DangIt"
};

SqlQuery query = procedureExecGenerator.Procedure(procedureExec);

"schema"."UpdateThing"

Table of Contents

---

<a id="running-queries-async--non-async"></a>

Running Queries (Async & Non-Async)

The PostgreSQL execution helpers live in Carrigan.SqlTools.Clients.PostgreSql and use Npgsql.

using System.Collections.Generic;
using Carrigan.Core.Interfaces;
using Carrigan.SqlTools.Clients.PostgreSql;
using Carrigan.SqlTools.PostgreSql;
using Carrigan.SqlTools.SqlGenerators;
using Npgsql;

<a id="async-executenonqueryasync--executescalarasync--executereaderasynct"></a>

Async: ExecuteNonQueryAsync / ExecuteScalarAsync / ExecuteReaderAsync<T>

// Build a query (example: SelectAll)
SqlQuery query = customerGenerator.SelectAll();

NpgsqlConnection connection = new(<your connection string>);
NpgsqlTransaction? transaction = null;

// Implement IDecrypters when encrypted fields need to be read.
IDecrypters? decrypters = null;

// Execute a reader query asynchronously.
IEnumerable<Customer> rows =
    await CommandsAsync.ExecuteReaderAsync<Customer>(query, transaction, connection, decrypters);

// Execute a data-changing command asynchronously.
int affected =
    await CommandsAsync.ExecuteNonQueryAsync(query, transaction, connection);

// Execute a scalar command asynchronously.
object? result =
    await CommandsAsync.ExecuteScalarAsync(query, transaction, connection);

ExampleEncryptor (AesGcm-based) and IDecrypters

Table of Contents

<a id="non-async-executenonquery--executescalar--executereadert"></a>

Non-Async: ExecuteNonQuery / ExecuteScalar / ExecuteReader<T>

Customer[] toDelete = [new Customer { Id = 7 }];
SqlQuery query = customerGenerator.DeleteById(toDelete);

NpgsqlConnection connection = new("Host=localhost;Database=AppDb;Username=app;Password=password");
NpgsqlTransaction? transaction = null;

IDecrypters? decrypters = null;

int affected = Commands.ExecuteNonQuery(query, transaction, connection);
object? scalar = Commands.ExecuteScalar(query, transaction, connection);
IEnumerable<Customer> customers =
    Commands.ExecuteReader<Customer>(query, transaction, connection, decrypters);

Table of Contents

---

<a id="simple-ado.net-example-with-sqlquery"></a>

Simple ADO.NET Example With SqlQuery

If you prefer raw ADO.NET, you can still use SqlQuery for the SQL text, command type, and parameters. The generated PostgreSQL query text uses positional parameters such as $1, $2, and $3.

using System;
using System.Collections.Generic;
using System.Data;
using Carrigan.SqlTools.PostgreSql;
using Carrigan.SqlTools.SqlGenerators;
using Npgsql;

// 1) Generate the query.
SqlQuery query = customerGenerator.SelectById(new Customer { Id = 42 });

// 2) Use Npgsql directly.
using NpgsqlConnection connection = new("Host=localhost;Database=AppDb;Username=app;Password=password");
connection.Open();

using NpgsqlCommand command = connection.CreateCommand();
command.CommandText = query.QueryText;
command.CommandType = query.CommandType;

// Add parameters in generated order.
// Positional placeholders such as $1 and $2 bind by parameter order.
foreach (KeyValuePair<string, object?> parameter in query.Parameters)
{
    command.Parameters.Add(new NpgsqlParameter { Value = parameter.Value ?? DBNull.Value });
}

using NpgsqlDataReader reader = command.ExecuteReader();
// ... map rows, or use the Carrigan.SqlTools client helpers ...

Table of Contents

---

Data Type Mappings

Default PostgreSQL Types

C# CLR Type	Default PostgreSQL Type
Guid	UUID
string	TEXT
char	CHAR(1)
byte[]	BYTEA
bool	BOOLEAN
byte	SMALLINT
sbyte	SMALLINT
short	SMALLINT
ushort	INTEGER
int	INTEGER
uint	BIGINT
long	BIGINT
ulong	NUMERIC(20, 0)
float	REAL
double	DOUBLE PRECISION
decimal	NUMERIC
DateTime	TIMESTAMP WITHOUT TIME ZONE
DateOnly	DATE
TimeOnly	TIME WITHOUT TIME ZONE
TimeSpan	INTERVAL
DateTimeOffset	TIMESTAMP WITH TIME ZONE
XDocument	XML
XmlDocument	XML
object	TEXT fallback for type-based mapping; UNKNOWN for null value-based mapping

Table of Contents

Supported CLR Types

The PostgreSQL dialect supports nullable forms and array forms for the supported scalar types where applicable. byte[] is treated as PostgreSQL BYTEA, not as an array of small integers. byte[][] is supported as an array of BYTEA values.

Supported categories include:

• UUID values: Guid, Guid?, and arrays.
• Text values: string, char, char?, and arrays.
• Binary values: byte[] and byte[][].
• Boolean values: bool, bool?, and arrays.
• Integer values: signed and unsigned integer types, nullable forms, and arrays.
• Numeric values: float, double, decimal, nullable forms, and arrays.
• Date and time values: DateTime, DateOnly, TimeOnly, TimeSpan, DateTimeOffset, nullable forms, and arrays.
• XML values: XDocument, XmlDocument, and arrays.
• Fallback values: object and object[].

Table of Contents

PostgreSQL Type Override Attributes

PostgreSQL override attributes derive from SqlTypeAttribute and can be applied to model properties to override the generated PostgreSQL type metadata.

Attribute	PostgreSQL Type	Constructor Options	Intended CLR Types
PostgreSqlCharAttribute	CHAR or VARCHAR	StorageTypeEnum.Fixed or StorageTypeEnum.Var; optional length	char, string; XML types are allowed but produce analyzer warnings
PostgreSqlDateAttribute	DATE	none	DateOnly; DateTime is allowed but produces an analyzer warning
PostgreSqlTimeAttribute	TIME WITHOUT TIME ZONE	optional fractional seconds precision 0 through 6	TimeOnly; DateTime is allowed but produces an analyzer warning
PostgreSqlTimestampAttribute	TIMESTAMP WITHOUT TIME ZONE	optional fractional seconds precision 0 through 6	DateTime; DateOnly and TimeOnly are allowed but produce analyzer warnings
PostgreSqlTimestampUtcAttribute	TIMESTAMP WITH TIME ZONE	optional fractional seconds precision 0 through 6	DateTimeOffset
PostgreSqlFloatAttribute	FLOAT	optional precision 1 through 53	double; float and decimal are allowed but may produce precision warnings
PostgreSqlNumericAttribute	NUMERIC	optional precision 1 through 255; optional precision and scale	decimal; float and double are allowed but may produce precision warnings
PostgreSqlMoneyAttribute	MONEY	none	decimal, double, and float; produces a discouraged-type analyzer warning

Table of Contents

Analyzer Warnings

The PostgreSQL analyzer reports:

• An error when a PostgreSQL SQL type attribute is applied to an incompatible CLR property type.
• A precision warning when a floating-point CLR type is mapped to exact numeric SQL or when an exact CLR type is mapped to floating-point SQL.
• A semantic warning when a date-only or time-only CLR property is mapped to a full timestamp type, or when XML values are forced through character mappings.
• A discouraged-type warning when PostgreSqlMoneyAttribute is used. PostgreSQL supports MONEY, but NUMERIC is usually safer for new schemas.

No PostgreSQL-specific type override attribute is currently classified as obsolete by the analyzer.

Table of Contents

Arrays

The PostgreSQL dialect supports PostgreSQL array declarations by rendering [] after the PostgreSQL type name.

Examples:

INTEGER[] NOT NULL
NUMERIC(18, 2)[] NOT NULL
TIMESTAMP(6) WITH TIME ZONE[] NOT NULL

Array type detection is based on CLR array property types and supported SQL type attributes. byte[] is special-cased as BYTEA; it is not treated as an array declaration. byte[][] represents an array of BYTEA values.

Table of Contents

---

<a id="exampleencryptor-aesgcm-based-and-idecrypters"></a>

ExampleEncryptor (AesGcm-based) and IDecrypters

Below is an example encryptor that uses an AesGcm-style API. Replace key handling with your own secure storage.

//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.

using System;
using System.Security.Cryptography;
using System.Text;
using Carrigan.Core.Interfaces;

public sealed class ExampleNonceGenerator : INonceGenerator
{
    public byte[] GenerateNonce()
    {
        byte[] nonce = new byte[12]; // 96-bit nonce
        RandomNumberGenerator.Fill(nonce);
        return nonce;
    }
}

public sealed class ExampleEncryptor : IEncryption
{
    private readonly byte[] _key;
    private const int TagSize = 16;

    public int? Version => 1;

    public byte[] KeyBytes => _key;

    public ExampleEncryptor(byte[] key)
    {
        if (key == null || key.Length != 32)
            throw new ArgumentException("Key must be 32 bytes.");

        _key = key;
    }

    public string? Encrypt(string? plainText)
    {
        if (plainText == null)
            return null;

        byte[] pt = Encoding.UTF8.GetBytes(plainText);
        byte[] ct = new byte[pt.Length];
        byte[] nonce = new byte[12];
        byte[] tag = new byte[TagSize];
        RandomNumberGenerator.Fill(nonce);

        using (AesGcm gcm = new AesGcm(_key, TagSize))
        {
            gcm.Encrypt(nonce, pt, ct, tag);
        }

        byte[] combined = new byte[nonce.Length + ct.Length + tag.Length];
        Buffer.BlockCopy(nonce, 0, combined, 0, nonce.Length);
        Buffer.BlockCopy(ct, 0, combined, nonce.Length, ct.Length);
        Buffer.BlockCopy(tag, 0, combined, nonce.Length + ct.Length, tag.Length);

        return Convert.ToBase64String(combined);
    }

    public string? Decrypt(string? cipherText)
    {
        if (cipherText == null)
            return null;

        byte[] combined = Convert.FromBase64String(cipherText);
        byte[] nonce = new byte[12];
        byte[] tag = new byte[TagSize];

        int cipherLen = combined.Length - nonce.Length - tag.Length;
        byte[] ct = new byte[cipherLen];

        Buffer.BlockCopy(combined, 0, nonce, 0, nonce.Length);
        Buffer.BlockCopy(combined, nonce.Length, ct, 0, ct.Length);
        Buffer.BlockCopy(combined, nonce.Length + ct.Length, tag, 0, tag.Length);

        byte[] pt = new byte[ct.Length];

        using (AesGcm gcm = new AesGcm(_key, TagSize))
        {
            gcm.Decrypt(nonce, ct, tag, pt);
        }

        return Encoding.UTF8.GetString(pt);
    }
}

// Minimal IDecrypters implementation mapping key versions to encryptors/decryptors

//THIS IS JUST A SIMPLE EXAMPLE OF A ENCRYPTION CLASS
//I AM NOT A CRYPTOGRAPHIC EXPERT, DO NOT USE THIS EXAMPLE IN A REAL SYSTEM.

public sealed class MyDecrypters : IDecrypters
{
    private readonly System.Collections.Generic.Dictionary<int, IEncryption> _map =
        new System.Collections.Generic.Dictionary<int, IEncryption>()
        {
            { 1, new ExampleEncryptor(RandomNumberGenerator.GetBytes(32)) }
        };

    public System.Collections.Generic.IEnumerable<int> Keys => _map.Keys;

    public IEncryption? Decryptor(int version)
    {
        return _map.TryGetValue(version, out IEncryption? encryptor) ? encryptor : null;
    }
}

Plug MyDecrypters into ExecuteReaderAsync<T> or ExecuteReader<T> to transparently decrypt properties annotated in your models.

Table of Contents

---

License

Carrigan.SqlTools.PostgreSql Copyright © 2025-2026 Carrigan Software Solutions LLC

Licensed under the Apache License, Version 2.0: http://www.apache.org/licenses/LICENSE-2.0

Table of Contents

---

Disclaimers

This project is not affiliated with or endorsed by the PostgreSQL project.

This library may generate or execute SQL. Review generated SQL before use in production systems. Always test against non-production databases first.

Use caution with schema, migration, and data-modifying operations. The authors are not responsible for data loss, downtime, or unintended database changes.