Search Results for

    Show / Hide Table of Contents

    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
    • 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:
      • connectionstrings.com
      • Npgsql documentation

    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

    • Worker System
      • Configuration
    • SQL Database Access
      • Adb Supported Databases
      • Adb Information
      • AdbProvider
    In This Article
    Back to top Copyright © 2020 Envobi Ltd