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. The listed .NET types below are fully supported by the dataflow.

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 Type Display Name DbType (or NpgsqlDbType) enum SQL Type Example
System.Collections.BitArray NpgsqlDbType.Varbit bit(2), varbit(3), varbit, bit varying(3), bit varying, and without AdbColumnSchema
System.Boolean, System.Boolean? Boolean bit, bit(1), boolean, and without AdbColumnSchema
System.Byte[] Binary bytea
System.DateTime, System.DateTime? DateTime2 timestamp, timestamp without time zone. Without AdbColumnSchema also: date.
System.DateTime, System.DateTime? Date With AdbColumnSchema: date
System.DateTimeOffset, System.DateTimeOffset? DateTimeOffset timestamptz, timestamp with time zone
System.Decimal, System.Decimal? Decimal money, numeric(10,2)
System.Collections.Generic.Dictionary<String,String>, System.Collections.Generic.IDictionary<String,String> NpgsqlDbType.Hstore hstore
System.Double, System.Double? Double double precision
System.Guid, System.Guid? Guid uuid
System.Int16, System.Int16? Int16 smallint
System.Int32, System.Int32? Int32 integer
System.Int64, System.Int64? Int64 bigint
System.Net.IPAddress NpgsqlDbType.Inet inet
System.ValueTuple<System.Net.IPAddress,System.Int32>, System.ValueTuple<System.Net.IPAddress,System.Int32>? NpgsqlDbType.Cidr cidr
System.Single, System.Single? Single real
System.Net.NetworkInformation.PhysicalAddress NpgsqlDbType.MacAddr8 macaddr, macaddr8
System.String String char(32), character(32), character varying(50), citext, name, text, varchar(50), and without AdbColumnSchema
System.String NpgsqlDbType.Json, NpgsqlDbType.Jsonb, NpgsqlDbType.Xml With AdbColumnSchema: json, jsonb, xml, respectively
System.TimeSpan, System.TimeSpan? NpgsqlDbType.Interval interval, time, time without time zone
System.UInt32, System.UInt32? UInt32 With AdbColumnSchema: cid, oid, xid

Provider-Specific .NET Types

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

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.

Functions and Stored Procedures

PostgreSQL supports functions and stored procedures, although both return data as a result set. Note that CommandType.StoredProcedure should normally not be set when calling a stored procedure, please see the Npgsql documentation for details.

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