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

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