Adb Npgsql Provider 
You can use this database (among others) in your ETL:
- Databases: PostgreSQL®, current versions (5 years), (external documentation)
- Both on-premises and cloud, including Azure PostgreSQL and other cloud platforms
- Features:
- Includes all optional provider services
- AdbNpgsqlTableInformationService supports tables (user, system, temporary, and partitioned) and views (user, system, and materialized)
- Includes provider-specific types
- Includes all optional provider services
- Adb provider: AdbNpgsqlProvider
- .NET provider: "Npgsql", a free
Npgsql
NuGet package retrieved automatically (external documentation)
- Type enumeration: NpgsqlTypes.NpgsqlDbType
- Type namespace: NpgsqlTypes
- Client namespace: Npgsql
- Connection strings:
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