PostgreSQL

PostgreSQL

Upgrading from v3? Undo the old setup 🎉

v3 needed Npgsql.dll installed into the GAC and a <DbProviderFactories> entry added to machine.config, because the reader ran inside the Visual Studio T4 host. v4 needs neither. The efrpg tool carries Npgsql as an ordinary NuGet dependency, so you can gacutil /u Npgsql and delete the machine.config entry. Nothing will break. See Upgrading from v3 to v4.

Settings

Settings.DatabaseType     = DatabaseType.PostgreSQL;
Settings.TemplateType     = TemplateType.EfCore10;   // or EfCore9, EfCore8
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.ConnectionString = "Server=127.0.0.1;Port=5432;Database=Northwind;User Id=testuser;Password=***;";

Your project needs the Npgsql.EntityFrameworkCore.PostgreSQL NuGet package. The generated OnConfiguring calls optionsBuilder.UseNpgsql(...).

More connection string shapes are on the Connection strings page.

Permissions

CONNECT on the database and USAGE on the schemas you want to read. information_schema and the catalogue are readable by default, so a plain read-only role is enough:

CREATE ROLE efrpg_reader LOGIN PASSWORD '***';
GRANT CONNECT ON DATABASE northwind TO efrpg_reader;
GRANT USAGE  ON SCHEMA public, sales TO efrpg_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public, sales TO efrpg_reader;

What gets read

Feature	Supported	Notes
Tables, views, columns	✔
Primary and foreign keys	✔	Including composite
Indexes	✔
serial / bigserial	✔	Treated as identity columns, so EF Core emits ValueGeneratedOnAdd() rather than ValueGeneratedNever()
GENERATED ... AS IDENTITY	✔
Generated columns	✔
Column defaults	✔
Column comments	✔	COMMENT ON COLUMN is read and emitted as an XML doc comment. See Extended Property Names Feature
Sequences	✔	Sequences created implicitly to back a serial or identity column are correctly excluded
Triggers	✔
Stored procedures and functions	✔
Stored procedure result models	✔	Built from the catalogue, so nothing is executed to discover them
Array columns	✔	See below
Synonyms	n/a	PostgreSQL has none
Temporal tables	n/a

Case sensitivity - the thing that catches everyone out

PostgreSQL folds unquoted identifiers to lower case. CREATE TABLE MyTable really creates mytable. Tools that quote everything (CREATE TABLE "MyTable") preserve the case, and then you have to quote it forever afterwards.

This matters in two places:

Filters match the real database name. A filter of RegexIncludeFilter("^MyTable$") will not match mytable. Use RegexOptions.IgnoreCase:

FilterSettings.TableFilters.Add(new RegexIncludeFilter(
    new Regex("^mytable$", RegexOptions.IgnoreCase, TimeSpan.FromMilliseconds(200))));

Class names. Leave Settings.UsePascalCase = true (the default) and order_line_item becomes OrderLineItem, which is what you want. Set it to false only if you genuinely want C# properties called order_line_item.

Schemas

The default schema is public, and every schema your user can see is read in one pass. Class names get the schema prefixed unless it is the default:

• public.hello becomes Hello
• sales.hello becomes Sales_Hello

Restrict what is read with a schema filter:

FilterSettings.SchemaFilters.Add(new RegexIncludeFilter("^public$|^sales$"));

Turn the prefixing off with Settings.PrependSchemaName = false.

Array columns

PostgreSQL array columns come through as real C# arrays:

Column type	Generated property
integer[]	int[]
text[]	string[]
numeric[]	decimal[]
uuid[]	Guid[]
timestamptz[]	DateTime[]
bytea[]	byte[][]

information_schema collapses every array to the single type name ARRAY, which is useless, so the reader digs the element type out of the catalogue and reports it PostgreSQL's own way (int4[], bpchar[], timestamptz[]). If an element type cannot be resolved at all, the column falls back to string[].

Type mapping highlights

PostgreSQL	C#
uuid	Guid
json, jsonb	string - see JSON column support to map it to a real type instead
interval	TimeSpan
timestamp, timestamptz	DateTime
time with time zone	DateTimeOffset
hstore	Dictionary<string, string>
citext	string
inet, cidr	NpgsqlInet
macaddr	PhysicalAddress
geometry	PostgisGeometry
bit, varbit	BitArray
money, numeric	decimal

The Npgsql-specific types need the relevant namespace adding:

Settings.AdditionalNamespaces = new List<string> { "NpgsqlTypes", "System.Net.NetworkInformation", "System.Collections" };

See also

• Connection strings
• Filtering
• Settings Reference
• Npgsql connection string parameters