UDataset.PostgreSQL 0.13.0

UDataset.PostgreSQL

UDataset.PostgreSQL is PostgreSQL implementation for UDataset cross-database data access framework. It provides PostgreSQL-specific implementations of core UDataset interfaces, enabling seamless data operations with PostgreSQL databases.

Features

• High-Performance Bulk Operations: Utilizes PostgreSQL's COPY command for optimized bulk insert operations
• Full PostgreSQL Support: Complete implementation of all UDataset features for PostgreSQL
• Rich Data Types: Supports PostgreSQL's extensive data type system including JSON, arrays, JSONB, custom types, and more
• Database Management: Full support for database creation, modification, and renaming operations
• Advanced PostgreSQL Features: Leverages PostgreSQL-specific features like sequences, triggers, extensions, and CTEs
• ACID Transactions: Full transaction support with savepoints and nested transactions
• Connection Pooling: Efficient connection pooling with Npgsql's built-in pooling
• Full-Text Search: Native support for PostgreSQL's full-text search capabilities

Installation

dotnet add package UDataset.Core
dotnet add package UDataset.PostgreSQL

Quick Start

1. Register Provider

using UDataset.Core;
using UDataset.PostgreSQL;

// Register PostgreSQL provider at application startup
PostgreSQLBootstrapper.Register();

2. Create Connection

// Basic connection string
string connectionString = "Host=localhost;Database=mydatabase;Username=myuser;Password=mypass;";

// With connection pooling and SSL
string pooledConnectionString = "Host=localhost;Database=mydatabase;Username=myuser;Password=mypass;Pooling=true;MinimumPoolSize=5;MaximumPoolSize=100;SSL Mode=Require;";

IConnection connection = ProviderFactory.CreateConnection("PostgreSQL", connectionString);

3. Basic Operations

// Create table
var schemaManager = ProviderFactory.CreateSchemaManager("PostgreSQL", connectionString);
var userTable = new Table("Users")
    .WithGuidPK("UserId") // PostgreSQL supports native GUID
    .WithVersionControl("Version"); // Enables optimistic locking
userTable.Attributes.Add(new Column("Username", DataType.String) { Properties = { [ColumnProperty.Length] = 100, [ColumnProperty.Unique] = true } });
userTable.Attributes.Add(new Column("Email", DataType.String) { Properties = { [ColumnProperty.Length] = 255 } });
userTable.Attributes.Add(new Column("Age", DataType.Int));
userTable.Attributes.Add(new Column("IsActive", DataType.Boolean));
userTable.Attributes.Add(new Column("Tags", DataType.Json)); // JSON type
schemaManager.Create(userTable);

// Insert data
var user = new Row("Users")
{
    ["Username"] = "john_doe",
    ["Email"] = "john@example.com",
    ["Age"] = 30,
    ["IsActive"] = true,
    ["Tags"] = "{ \"tags\": [\"developer\", \"admin\"] }"
};
var createdUser = await connection.Create(user);

// Update with optimistic locking
var userToUpdate = new Row("Users", createdUser.Id);
userToUpdate.Version = createdUser.Version; // Required for optimistic locking
userToUpdate["Age"] = 31;
var updatedUser = await connection.Update(userToUpdate);

// Query data
var query = new QueryExpression("Users");
query.Filter = "IsActive = @isActive AND Age > @minAge";
query.Parameters["isActive"] = true;
query.Parameters["minAge"] = 25;
query.AddOrderBy("Username", SortDirection.Ascending);
var activeUsers = await connection.Query(query);

PostgreSQL-Specific Features

JSON and JSONB

PostgreSQL has excellent JSON support with JSONB for binary storage:

// Create table with JSON column
var configTable = new Table("Configurations");
configTable.Attributes.Add(new Column("Settings", DataType.Json));
schemaManager.Create(configTable);

// Query JSON data (PostgreSQL supports JSON operators)
var query = new QueryExpression("Configurations");
// UDataset handles JSON queries through PostgreSQL's native JSON functions

GUID Primary Keys

PostgreSQL has native GUID support with database-generated values:

// UDataset uses gen_random_uuid() for GUID generation
var table = new Table("Orders")
    .WithGuidPK("OrderId"); // GUIDs generated by database
schemaManager.Create(table);

// Bulk inserts work seamlessly - database generates GUIDs
var bulkOrders = new List<Row>();
for (int i = 0; i < 100; i++)
{
    bulkOrders.Add(new Row("Orders"));
}
await connection.Create(bulkOrders); // All GUIDs generated by database

Bulk Operations with COPY

PostgreSQL's COPY command for high-performance bulk inserts:

// UDataset automatically uses COPY for bulk operations
var largeDataset = new List<Row>();
// ... populate with thousands of records
await connection.Create(largeDataset); // Uses COPY command internally

Array Data Types

PostgreSQL supports array columns:

var table = new Table("Products");
// UDataset handles PostgreSQL array types through type system

Use Cases

PostgreSQL is ideal for:

• Complex Data Models: Applications with complex relationships and data types
• Geospatial Applications: Using PostGIS for spatial data
• Full-Text Search: Applications requiring advanced search capabilities
• Data Warehousing: Analytical workloads with materialized views
• JSON Applications: Applications with heavy JSON data storage and querying
• Enterprise Applications: Large-scale enterprise applications with high reliability
• Cloud Applications: Amazon RDS, Azure Database for PostgreSQL, Google Cloud SQL

Compatibility

• PostgreSQL 12 and later
• Amazon RDS for PostgreSQL
• Azure Database for PostgreSQL
• Google Cloud SQL for PostgreSQL
• .NET 8.0
• Entity Framework Core compatibility (when used alongside)

Performance Characteristics

• Bulk Inserts: Optimized COPY command for high-throughput bulk operations
• Query Optimization: Sophisticated query planner and optimizer
• Index Types: B-tree, Hash, GiST, GIN, BRIN indexes
• Connection Pooling: Efficient connection pooling with Npgsql
• Write Performance: Good performance with proper indexing and configuration

Advanced Features

Full-Text Search

// PostgreSQL's tsvector and tsquery for full-text search
// UDataset supports full-text search through QueryExpression
var searchQuery = new QueryExpression("Documents");
searchQuery.Filter = "to_tsvector(Content) @@ to_tsquery(@searchTerm)";
searchQuery.Parameters["searchTerm"] = "postgresql";
var results = await connection.Query(searchQuery);

Common Table Expressions (CTEs)

// UDataset supports CTEs through QueryExpression
// Complex queries can be built using subqueries and joins

Window Functions

// PostgreSQL's advanced window functions are supported
var query = new QueryExpression("Sales");
query.AddSelect("ROW_NUMBER() OVER (ORDER BY Amount DESC)", "Rank");
query.AddSelect("*");

Limitations

• Large Objects: BLOB operations may have performance overhead
• Vacuum: Requires periodic VACUUM operations for maintenance
• Connection Limits: May require tuning for high-concurrency scenarios
• JSONB Write Performance: JSONB updates can be slower than native columns

Requirements

• .NET 8.0
• Npgsql 10.0.1

Dependencies

• UDataset.Core
• Npgsql 10.0.1
• Dapper 2.1.66
• System.Data.Common
• System.Text.Json (for JSON operations)

PostgreSQL SQL Syntax Transformations

UDataset automatically transforms common SQL functions to PostgreSQL equivalents:

Connection String Examples

// Basic
"Host=localhost;Database=mydb;Username=user;Password=pass;"

// With SSL
"Host=localhost;Database=mydb;Username=user;Password=pass;Ssl Mode=Require;"

// With connection pooling
"Host=localhost;Database=mydb;Username=user;Password=pass;Pooling=true;MinimumPoolSize=5;MaximumPoolSize=100;"

// With timeout
"Host=localhost;Database=mydb;Username=user;Password=pass;Timeout=30;CommandTimeout=60;"

// Unix socket
"Host=/var/run/postgresql;Database=mydb;Username=user;Password=pass;"

Extensions Support

PostgreSQL's powerful extension system is supported:

• PostGIS: Geospatial data and queries
• pg_trgm: Trigram matching for fuzzy search
• hstore: Key-value store
• uuid-ossp: UUID generation functions
• pgcrypto: Cryptographic functions

For general usage examples and advanced features, please refer to UDataset.Core documentation and architecture guide.

Version History

0.11.0 (2026-04-16)

• Added DM (Dameng) and KingbaseES database support to UDataset framework
• Note: KingbaseES support is experimental and not recommended for production use