Adb Npgsql Provider

You can use this database (among others) in your ETL:

Data Type Mappings

This section lists which database types map to or from which .NET types when querying a PostgreSQL® server and working with its database parameters.

Unless otherwise noted, all the below .NET types can be round-tripped to the database, i.e. written out with one of the database target workers, and read back with AdbDataReaderSource<TOutput>.

Note

Mappings tagged "with AdbColumnSchema" below depend on whether or not an AdbColumnSchema is supplied to SetMappedType(AdbParameter, Type, AdbColumnSchema, ParameterDirection). Of the out of box workers, this is only supplied by AdbInsertTarget<TInputError>.

Provider-Independent .NET Types

.NET types below with Shallow or SingleShallowThenDeep copy policy support multi-copy, the others (with SingleShallow) do not.

.NET Type Display Name DbType (or NpgsqlDbType) enum SQL Type Example Copy Policy
Reading database integer into any enum or enum? note1 smallint, int, bigint Shallow
System.Collections.BitArray NpgsqlDbType.Varbit bit(2), varbit(3), varbit, bit varying(3), bit varying SingleShallow
System.Boolean, System.Boolean? Boolean bit, bit(1), boolean Shallow
System.Byte[] Binary bytea SingleShallowThenDeep
System.DateTime, System.DateTime? DateTime2 timestamp, timestamp without time zone. Without AdbColumnSchema also: date. Shallow
System.DateTime, System.DateTime? Date With AdbColumnSchema: date Shallow
System.DateTimeOffset, System.DateTimeOffset? DateTimeOffset timestamptz, timestamp with time zone Shallow
System.Decimal, System.Decimal? Decimal money, numeric(10,2) Shallow
System.Collections.Generic:
Dictionary<String,String>, IDictionary<String,String>		 NpgsqlDbType.Hstore hstore SingleShallow
System.Double, System.Double? Double double precision Shallow
System.Guid, System.Guid? Guid uuid Shallow
System.Int16, System.Int16? Int16 smallint Shallow
System.Int32, System.Int32? Int32 integer Shallow
System.Int64, System.Int64? Int64 bigint Shallow
System.Net.IPAddress NpgsqlDbType.Inet inet SingleShallow
System:
ValueTuple<Net.IPAddress,Int32>, ValueTuple<Net.IPAddress,Int32>?		 NpgsqlDbType.Cidr cidr SingleShallow
System.Memory<System.Byte>, System.Memory<System.Byte>? note2 NpgsqlDbType.Bytea bytea SingleShallowThenDeep
System.Single, System.Single? Single real Shallow
System.Net.NetworkInformation
.PhysicalAddress		 NpgsqlDbType.MacAddr8 macaddr, macaddr8 Shallow
System.String String char(32), character(32), character varying(50), citext, name, text, varchar(50) Shallow
System.String NpgsqlDbType.Json, NpgsqlDbType.Jsonb, NpgsqlDbType.Xml With AdbColumnSchema: json, jsonb, xml, respectively Shallow
System.TimeSpan, System.TimeSpan? NpgsqlDbType.Interval interval, time, time without time zone Shallow
System.UInt32, System.UInt32? UInt32 With AdbColumnSchema: cid, oid, xid Shallow
  • note1: See Add a Conversion Property for how to also write enum and enum? to database integer columns with this provider.
  • note2: Only supported on .NET5+.

Provider-Specific .NET Types

.NET types below with Shallow or SingleShallowThenDeep copy policy support multi-copy, the others (with SingleShallow) do not.

Note that NpgsqlTypes.NpgsqlTsQuery and NpgsqlTypes.NpgsqlTsVector are Always Column.

Note

The provider-specific data types are very useful for:

  • Avoiding conversion issues due to differences in collation, precision, and range
  • Providing SQL style null handling
    • While nullable types (e.g int?) can be very useful, note that the .NET null handling is different from SQL null handling; see Handling Null Values for details

Also note that using provider-specific types can have slightly higher or lower performance vs. using DbType data types, e.g. by avoiding data conversion, or due to providing more functionality.

.NET type display name
NpgsqlTypes		 NpgsqlDbType enum SQL Type Example Copy Policy
NpgsqlTypes.NpgsqlBox, NpgsqlTypes.NpgsqlBox? NpgsqlDbType.Box box Shallow
NpgsqlTypes.NpgsqlCircle, NpgsqlTypes.NpgsqlCircle? NpgsqlDbType.Circle circle Shallow
NpgsqlTypes.NpgsqlDate, NpgsqlTypes.NpgsqlDate? NpgsqlDbType.Date date Shallow
NpgsqlTypes.NpgsqlDateTime, NpgsqlTypes.NpgsqlDateTime? NpgsqlDbType.Timestamp timestamp, timestamp without time zone, and without AdbColumnSchema Shallow
NpgsqlTypes.NpgsqlLine, NpgsqlTypes.NpgsqlLine? NpgsqlDbType.Line line Shallow
NpgsqlTypes.NpgsqlLSeg, NpgsqlTypes.NpgsqlLSeg? NpgsqlDbType.LSeg lseg Shallow
NpgsqlTypes.NpgsqlPath, NpgsqlTypes.NpgsqlPath? NpgsqlDbType.Path path SingleShallowThenDeep
NpgsqlTypes.NpgsqlPoint, NpgsqlTypes.NpgsqlPoint? NpgsqlDbType.Point point Shallow
NpgsqlTypes.NpgsqlPolygon, NpgsqlTypes.NpgsqlPolygon? NpgsqlDbType.Polygon polygon Shallow
NpgsqlTypes.NpgsqlTimeSpan, NpgsqlTypes.NpgsqlTimeSpan? NpgsqlDbType.Interval interval Shallow
NpgsqlTypes.NpgsqlTsQuery NpgsqlDbType.TsQuery tsquery SingleShallow
NpgsqlTypes.NpgsqlTsVector NpgsqlDbType.TsVector tsvector SingleShallow

Identifiers

  • Quoting character: double quote ", e.g. "My Table"
  • Max length: 63
  • PostgreSQL external documentation: Identifiers

Identifier Casing

PostgreSQL folds unquoted identifiers to lower case (while most other databases uses upper case and/or are truly case insensitive).

Note that dataflow column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match. See Dataflow Column Mapping for details.

Parameters

Use named parameters of the format @MyParameterName. You can use GetPlaceholder(String) to get the placeholder in a portable manner.

Stored Procedures

Here we execute a stored procedure with parameters. Even building an executable program (or utility method) for executing this single query can be very worthwhile since actionETL adds configuration, error handling, and logging.

We create the command builder outside the worker system to make it easy to return a parameter value to the caller. More commonly, commands and connections are created inside the worker system, as and when they are needed.

Note
  • PostgreSQL stored procedures return data as a result set. You can however return values in output parameters in PostgreSQL version 11 onwards, as shown in this example.
  • This example uses Input (the default) and InputOutput direction parameters. Output and ReturnValue parameters are not supported.
  • Note that CommandType.StoredProcedure should normally not be set when calling a stored procedure, please see the Npgsql documentation for details.
using actionETL;
using actionETL.Adb;
using actionETL.Adb.NpgsqlExternal;
using System.Data;

public static partial class ExecuteStoredProcedureNpgsql
{
    public static DateTime GetMaxDateTime()
    {
        // Get a command builder using "actionetl.aconfig.json" connection details.
        var cb = AdbNpgsqlProvider.Get()
            .CreateCommandBuilderFromDefaultAConfig("NpgsqlTester");
        cb.CommandText = "CALL getmaxdatetime (@countryId, @category, @maxDateTime)";

        // Many methods return an instance and allow method chaining.
        // Whenever possible, set the database type explicitly.
        cb.Parameters.Add("countryId").SetDbValue(42, DbType.Int32);

        // Database type not set, provider has to guess the type (which is less good)
        cb.Parameters.Add("category").SetValue("Batteries");

        // Save the parameter reference, since we retrieve the returned value later.
        // Npgsql supports ParameterDirection.InputOutput but not ParameterDirection.Output.
        var maxDateTime = cb.Parameters.Add("maxDateTime", ParameterDirection.InputOutput)
            .SetDbType(DbType.DateTime).SetValue(null);

        new WorkerSystem()
            // Curly braces and semi-colon not needed in 1-line lambda
            .Root(ws => _ = new AdbExecuteNonQueryWorker(ws, "Call getmaxdatetime", cb))
            .Start()
            .ThrowOnFailure();

        // Value is of type object, and must be cast
        return (DateTime)maxDateTime.DbValue;
    }
}

/* The example assumes the following stored procedure already exists:
    CREATE PROCEDURE getmaxdatetime (
        countryId integer, 
        category text,
        INOUT maxDateTime timestamp
        )
    LANGUAGE PLPGSQL
    AS $$
    BEGIN
        -- Use input parameters...

        maxDateTime := '2017-03-13';
    END;
    $$
 */

Adb Information

The Adb Information example program dumps the below information with this provider querying a SQL Server. See that example for more information.

=== AdbConnectionBuilder ===
Provider=Npgsql:PostgreSQL:


=== AdbConnection ===
DataSourceName=tcp://127.0.0.1:5432
DatabaseName=actionetl_tester
NumberOfColumnIdentifierParts=4
NumberOfColumnRestrictions=4
ServerVersion=12.1


=== AdbConnection.Information ===
CompositeIdentifierSeparator=.
CompositeIdentifierSeparatorPattern=\.
DataSourceProductName=Npgsql
DataSourceProductVersion=12.1
GetPlaceholder("MyName")=@MyName
GroupByBehavior=Unrelated
IdentifierCase=Insensitive
IdentifierPattern=(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\"[^\"\0]|\"\"+\"$)
IdentifierQuotePrefix="
IdentifierQuotePrefixes="
IdentifierQuoteSuffix="
IdentifierQuoteSuffixes="
OrderByColumnsInSelect=False
ParameterMarkerFormat={0}
ParameterMarkerPattern=@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)
ParameterNameMaxLength=63
ParameterNamePattern=^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)
QuotedIdentifierCase=Sensitive
QuotedIdentifierPattern="(([^\"]|\"\")*)"
QuoteIdentifierPart("My Table")="My Table"
StatementSeparatorPattern=;
StringLiteralPattern='(([^']|'')*)'
SupportedJoinOperators=Inner, LeftOuter, RightOuter, FullOuter
UsesPositionalParameters=False


=== AdbConnection.TableInformation.CreateTableIdentifier("My Schema".MyTable) ===
OriginalCompositeName="My Schema".MyTable
OriginalTableName=MyTable
OriginalSchemaName="My Schema"
OriginalCatalogName=
QuotedCompositeName="My Schema"."MyTable"
QuotedTableName="MyTable"
QuotedSchemaName="My Schema"
QuotedCatalogName=
UnquotedTableName=MyTable
UnquotedSchemaName=My Schema
UnquotedCatalogName=


=== AdbConnection.SchemaInformationCollection ===
Note: The metadata schemas can be retrieved with AdbConnection.GetSchema().
The returned information is provided from the .NET provider as-is. Some collections
can be inaccurate, and different values can also be needed for different purposes,
which is why database specific actionETL providers can be created that replace
individual values with the desired ones. See the vendor provider documentation for
details on the returned information.

CollectionName=MetaDataCollections
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=DataSourceInformation
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Restrictions
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=DataTypes
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0

CollectionName=Databases
IsCommon=False
NumberOfRestrictions=1
NumberOfIdentifierParts=1

CollectionName=Tables
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3

CollectionName=Columns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4

CollectionName=Views
IsCommon=False
NumberOfRestrictions=3
NumberOfIdentifierParts=3

CollectionName=Users
IsCommon=False
NumberOfRestrictions=1
NumberOfIdentifierParts=1

CollectionName=Indexes
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4

CollectionName=IndexColumns
IsCommon=False
NumberOfRestrictions=5
NumberOfIdentifierParts=5

See Also