PostgreSQL

Requirements

PostgreSQL support relies on the Npgsql ADO.NET provider. The LLBLGen Pro designer includes Npgsql v4.0.12, which is the last .NET Framework specific ADO.NET provider that works without problems. As Npgsql isn't shipped with an msi installer anymore, the driver is included in the LLBLGen Pro designer. For the generated code for the various target frameworks you have to reference the Npgsql version / variant that works with your chosen framework and target platform. It's best to do that via the Npgsql nuget package.

To be able to create a model from an existing PostgreSQL schema, you have to have access to a PostgreSQL instance, version v7.4 or higher.

Supported features

Feature Supported
Minimal database version v7.4
Database types All types supported by Npgsql
User defined types No
Foreign key constraints Yes
Primary key constraints Yes
Unique constraints Yes
Check constraints No
Default values Yes
Unique indexes No
Tables Yes
Views Yes
Stored Procedures Yes
Table Valued Functions Yes
(System versioned) Temporal tables No
Synonyms No
Cascade delete rules Yes
Cascade update rules Yes
Identity fields Yes
System sequences Yes
Schema based sequences Yes
Multiple catalogs per project No
Multiple schemas per catalog Yes
Linked servers No
Info

Functions which return a table definition or use SETOF are included in both the stored procedures and the table-valued functions. Functions which return a refcursor are ignored as the feature isn't implemented properly in Npgsql (as of v3.2.7). We'll add this feature again if refcursors are properly fetched again in Npgsql.

Type mappings

Below you'll find the two type mapping tables used by the PostgreSQL driver of LLBLGen Pro. One is used for Database First development, the other is used for Model First development. In case of Model First development, you can add your own type shortcuts to define a different target database type, in case the default mappings aren't what you need in a particular case.

Global model-first type overruling

For Model first, there are often multiple database types defined per .NET Type and a given filter. To prefer one over the other on a global level, please look at the following settings in the Relational model data element construction section of the Project Settings:

  • Prefer natural character types
  • Prefer decimal over currency types
  • Prefer variable length types

Database first type mappings

You can control the .NET type during reverse engineering in database first development by defining Type Conversions in the Project Settings, which are based on Type Converters.

Database type .NET Type
Array Array of the type of the element the array is in, e.g. int[]
Bit System.Boolean
Boolean System.Boolean
Bool System.Boolean
Box System.Object
Bytea System.Byte[]
Character Varying System.String
Char System.String
Circle System.Object
Citext System.String
Date System.DateTime
Double precision System.Double
Float4 System.Single
Float8 System.Double
Geometry System.Object
Inet System.System.Net.IPAddress
Int2 System.Int16
Int4 System.Int32
Int8 System.Int64
Interval System.TimeSpan
Jsonb System.String
Json System.String
LSeg System.Object
Line System.Object
Money System.Decimal
Numeric System.Decimal
Path System.Object
Point System.Object
Polygon System.Object
Real System.Single
RefCursor System.Object
Single precision System.Single
Text System.String
Time without time zone System.TimeSpan
Time without timezone System.TimeSpan
Timestamp with time zone System.DateTime
Timestamp with timezone System.DateTime
Timestamptz System.DateTime
Timestamp System.DateTime
Timetz System.DateTimeOffset
Time System.TimeSpan
User-defined System.String
Uuid System.System.Guid
Varchar System.String
Xml System.string

Model first type mappings

If specified, the filter is combined with the .NET type to determine the database type. The elements length, precision and scale are the max. length, precision and scale of an entity field with the particular .NET Type. For instance a field with type System.String, max. length set to 50, will with all defaults enabled, a database type of NVarChar(50).

If a controlling setting is specified, that setting has to be set to the specified value (in the Relational model data element construction section of the Project Settings) to make the designer select that database type.

.NET Type Filter Database type Controlling setting and value
System.Bool Bool
System.Net.IPAddress Inet
System.Byte[] 0 < length < 2000 bytea(length)
System.Byte[] length==0 or length > 2000 bytea
System.DateTime DateTime
System.DateTimeOffset Timetz
System.Decimal 0 < precision <=19 and 0 < scale <= 2 Money Prefer decimal over currency types set to false
System.Decimal 0 < precision <=19 and 0 < scale <= 2 Numeric(precision, scale) Prefer decimal over currency types set to true
System.Decimal Numeric(precision, scale)
System.Double Float8
System.Guid Uuid
System.Int16 Int2
System.Int32 Int4
System.Int64 Int8
System.Object Geometry
System.Single Float4
System.String length==0 Char(1073741824) Prefer variable length types set to false
System.String length==0 VarChar(1073741824) Prefer variable length types set to true
System.String length > 0 Char(length) Prefer variable length types set to false
System.String length > 0 VarChar(length) Prefer variable length types set to true
System.TimeSpan Time

Array support

Single dimensional arrays are supported on PostgreSQL, using database first modeling. The .NET type for the field mapped onto an array typed table/view field will be determined from the array element: if the table field is of type Array of Int4, then the .NET type will be an int32[].

Arrays on PostgreSQL are supported for Entity Framework Core 3+ and LLBLGen Pro Runtime Framework.

Supported target frameworks

The following target frameworks are supported with SQL Server:

  • LLBLGen Pro Runtime Framework
  • Entity Framework
  • Entity Framework Core
  • NHibernate