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	✔	Signatures and parameters, so callers are generated
Function result models	✔	Read from pg_proc, so nothing is executed to discover them. See below
Array columns	✔	See below
Synonyms	n/a	PostgreSQL has none
Temporal tables	n/a

Function result models

Return models are produced for functions that return a set - RETURNS TABLE(...), RETURNS SETOF ..., or a record - which the generator treats as table-valued functions. They are read straight out of pg_proc, so unlike SQL Server nothing is executed to discover the shape and no EXECUTE permission is needed. PostgreSQL has no equivalent of SET FMTONLY, so reading them any other way would mean running the function for real, side effects and all.

A PostgreSQL PROCEDURE (CREATE PROCEDURE, called with CALL) returns no result set, so it gets a caller but no return model. That is PostgreSQL's own semantics, not a gap in the generator.

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

The schema is prepended verbatim, keeping whatever case PostgreSQL stores - which after identifier folding is usually lower case. Only the table name is PascalCased.

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