PostgreSQL Features

PostgreSQL Rich Data Types

The [PgSql*] specific attributes lets you use attributes to define PostgreSQL rich data types, e.g:

public class MyPostgreSqlTable
{
    [PgSqlJson]
    public List<Poco> AsJson { get; set; }

    [PgSqlJsonB]
    public List<Poco> AsJsonB { get; set; }

    [PgSqlTextArray]
    public string[] AsTextArray { get; set; }

    [PgSqlIntArray]
    public int[] AsIntArray { get; set; }

    [PgSqlBigIntArray]
    public long[] AsLongArray { get; set; }
}

By default, all arrays of .NET's built-in numeric, string and DateTime types will be stored in PostgreSQL array types:

public class Table
{
    public Guid Id { get; set; }

    public int[] Ints { get; set; }
    public long[] Longs { get; set; }
    public float[] Floats { get; set; }
    public double[] Doubles { get; set; }
    public decimal[] Decimals { get; set; }
    public string[] Strings { get; set; }
    public DateTime[] DateTimes { get; set; }
    public DateTimeOffset[] DateTimeOffsets { get; set; }
}

You can opt in to annotate other collections like List<T> to also be stored in array types by annotating them with [Pgsql*] attributes, e.g:

public class Table
{
    public Guid Id { get; set; }

    [PgSqlIntArray]
    public List<int> ListInts { get; set; }
    [PgSqlBigIntArray]
    public List<long> ListLongs { get; set; }
    [PgSqlFloatArray]
    public List<float> ListFloats { get; set; }
    [PgSqlDoubleArray]
    public List<double> ListDoubles { get; set; }
    [PgSqlDecimalArray]
    public List<decimal> ListDecimals { get; set; }
    [PgSqlTextArray]
    public List<string> ListStrings { get; set; }
    [PgSqlTimestamp]
    public List<DateTime> ListDateTimes { get; set; }
    [PgSqlTimestampTz]
    public List<DateTimeOffset> ListDateTimeOffsets { get; set; }
}

Alternatively if you always want List<T> stored in Array types, you can register them in the PostgreSqlDialect.Provider:

PostgreSqlDialect.Provider.RegisterConverter<List<string>>(new PostgreSqlStringArrayConverter());
PostgreSqlDialect.Provider.RegisterConverter<List<int>>(new PostgreSqlIntArrayConverter());
PostgreSqlDialect.Provider.RegisterConverter<List<long>>(new PostgreSqlLongArrayConverter());
PostgreSqlDialect.Provider.RegisterConverter<List<float>>(new PostgreSqlFloatArrayConverter());
PostgreSqlDialect.Provider.RegisterConverter<List<double>>(new PostgreSqlDoubleArrayConverter());
PostgreSqlDialect.Provider.RegisterConverter<List<decimal>>(new PostgreSqlDecimalArrayConverter());
PostgreSqlDialect.Provider.RegisterConverter<List<DateTime>>(new PostgreSqlDateTimeTimeStampArrayConverter());
PostgreSqlDialect.Provider.RegisterConverter<List<DateTimeOffset>>(new PostgreSqlDateTimeOffsetTimeStampTzArrayConverter());

PostgreSQL Params

The PgSql.Param() API provides a resolve the correct populated NpgsqlParameter and NpgsqlDbType from a C# Type which can be used to query custom PostgreSQL Data Types in APIs that accept IDbDataParameter parameters, e.g:

public class FunctionResult
{
    public int[] Val { get; set; }
}

var p = PgSql.Param("paramValue", testVal);
var sql = "SELECT * FROM my_func(@paramValue)";
var rows = db.Select<FunctionResult>(sql, new [] { p });

Hstore support

To use hstore, its extension needs to be enabled in your PostgreSQL RDBMS by running:

CREATE EXTENSION hstore;

Which can then be enabled in OrmLite with:

PostgreSqlDialect.Instance.UseHstore = true;

Where it will now store string Dictionaries in Hstore columns:

public class TableHstore
{
    public int Id { get; set; }

    public Dictionary<string,string> Dictionary { get; set; }
    public IDictionary<string,string> IDictionary { get; set; }
}

db.DropAndCreateTable<TableHstore>();

db.Insert(new TableHstore
{
    Id = 1,
    Dictionary = new Dictionary<string, string> { {"A", "1"} },
    IDictionary = new Dictionary<string, string> { {"B", "2"} },
});

Where they can than be queried in postgres using Hstore SQL Syntax:

db.Single(db.From<PostgreSqlTypes>().Where("dictionary -> 'A' = '1'")).Id //= 1

Thanks to @cthames for this feature.

JSON data types

If you instead wanted to store arbitrary complex types in PostgreSQL's rich column types to enable deep querying in postgres, you'd instead annotate them with [PgSqlJson] or [PgSqlJsonB], e.g:

public class TableJson
{
    public int Id { get; set; }

    [PgSqlJson]
    public ComplexType ComplexTypeJson { get; set; }

    [PgSqlJsonB]
    public ComplexType ComplexTypeJsonb { get; set; }
}

db.Insert(new TableJson
{
    Id = 1,
    ComplexTypeJson = new ComplexType {
        Id = 2, SubType = new SubType { Name = "JSON" }
    },
    ComplexTypeJsonb = new ComplexType {
        Id = 3, SubType = new SubType { Name = "JSONB" }
    },
});

Where they can then be queried on the server with JSON SQL Syntax and functions:

var result = db.Single<TableJson>("table_json->'SubType'->>'Name' = 'JSON'");

Custom SQL using PostgreSQL Arrays

The PgSql.Array() provides a typed API for generating PostgreSQL Array Expressions, e.g:

PgSql.Array(1,2,3)     //= ARRAY[1,2,3]
var strings = new[]{ "A","B","C" };
PgSql.Array(strings)   //= ARRAY['A','B','C']

Which you can safely use in Custom SQL Expressions that use PostgreSQL's native ARRAY support:

q.And($"{PgSql.Array(anyTechnologyIds)} && technology_ids")
q.And($"{PgSql.Array(labelSlugs)} && labels");

If you want and empty collection to return null instead of an empty ARRAY[] you can use the nullIfEmpty overload:

PgSql.Array(new string[0], nullIfEmpty:true)      //= null
PgSql.Array(new[]{"A","B","C"}, nullIfEmpty:true) //= ARRAY['A','B','C']