Supported Types and their Mappings
The following lists the built-in mappings when reading and writing CLR types to PostgreSQL types.
Note that in addition to the below, enum and composite mappings are documented in a separate page. Note also that several plugins exist to add support for more mappings (e.g. spatial support for PostGIS), these are listed in the Types menu.
Read mappings
The following shows the mappings used when reading values.
- The default type is returned when using
NpgsqlCommand.ExecuteScalar(),NpgsqlDataReader.GetValue()and similar methods. - You can read as other types by calling
NpgsqlDataReader.GetFieldValue<T>().
| PostgreSQL type | Default .NET type | Non-default .NET types |
|---|---|---|
| boolean | bool | |
| smallint | short | byte, sbyte, int, long, float, double, decimal |
| integer | int | byte, short, long, float, double, decimal |
| bigint | long | long, byte, short, int, float, double, decimal |
| real | float | double |
| double precision | double | |
| numeric | decimal | byte, short, int, long, float, double, BigInteger (6.0+) |
| money | decimal | |
| text | string | char[], byte[]1 |
| character varying | string | char[], byte[]1 |
| character | string | char[], byte[]1 |
| citext | string | char[], byte[]1 |
| json | string | char[], byte[]1 |
| jsonb | string | char[], byte[]1 |
| xml | string | char[], byte[]1 |
| uuid | Guid | |
| bytea | byte[]1 | |
| timestamp without time zone | DateTime (Unspecified) | |
| timestamp with time zone | DateTime (Utc2) | DateTimeOffset (Offset=0)2 |
| date | DateTime | DateOnly (6.0+) |
| time without time zone | TimeSpan | TimeOnly (6.0+) |
| time with time zone | DateTimeOffset | |
| interval | TimeSpan3 | NpgsqlInterval |
| cidr | NpgsqlCidr4 | |
| inet | IPAddress | NpgsqlInet |
| macaddr | PhysicalAddress | |
| tsquery | NpgsqlTsQuery | |
| tsvector | NpgsqlTsVector | |
| bit(1) | bool | BitArray |
| bit(n) | BitArray | |
| bit varying | BitArray | |
| point | NpgsqlPoint | |
| lseg | NpgsqlLSeg | |
| path | NpgsqlPath | |
| polygon | NpgsqlPolygon | |
| line | NpgsqlLine | |
| circle | NpgsqlCircle | |
| box | NpgsqlBox | |
| hstore | Dictionary<string, string> | |
| oid | uint | |
| xid | uint | |
| cid | uint | |
| oidvector | uint[] | |
| name | string | char[] |
| (internal) char | char | byte, short, int, long |
| geometry (PostGIS) | PostgisGeometry | |
| record | object[] | |
| composite types | T | |
| range types | NpgsqlRange<TElement> | |
| multirange types (PG14) | NpgsqlRange<TElement>[] | |
| enum types | TEnum | |
| array types | Array (of element type) |
1Types which can be read as byte[] can also be written as ReadOnlyMemory<byte> and Stream, and if they could be big, consider using CommandBehavior.SequentialAccess and NpgsqlDataReader.GetStream() to stream the row and column instead of buffering them. Note that if you're reading string data (e.g. jsonb) as binary, it's your responsibility to perform UTF8 decoding (or whatever encoding is configured for your database).
2 In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), reading a timestamp with time zone returns a Local DateTime instead of Utc, and reading it as a DateTimeOffset returns a local offset based on the timezone of the server where Npgsql is running. See the breaking change note for more info.
3 PostgreSQL intervals with month or year components cannot be read as TimeSpan. Consider using NodaTime's Period type, or NpgsqlInterval.
4 Prior to version 8.0, the default mapping for cidr was ValueTuple<IPAddress, int>.
Write mappings
There are three rules that determine the PostgreSQL type sent for a parameter:
- If the parameter's
NpgsqlDbTypeis set, it is used. - If the parameter's
DataTypeis set, it is used. - If the parameter's
DbTypeis set, it is used. - If none of the above is set, the backend type will be inferred from the CLR value type.
| PostgreSQL type | Default .NET types | Non-default .NET types | NpgsqlDbType | DbType |
|---|---|---|---|---|
| boolean | bool | Boolean | Boolean | |
| smallint | short, byte, sbyte | Smallint | Int16 | |
| integer | int | Integer | Int32 | |
| bigint | long | Bigint | Int64 | |
| real | float | Real | Single | |
| double precision | double | Double | Double | |
| numeric | decimal, BigInteger (6.0+) | Numeric | Decimal, VarNumeric | |
| money | decimal | Money | Currency | |
| text | string, char[], char | byte[], ReadOnlyMemory<byte> | Text | String, StringFixedLength, AnsiString, AnsiStringFixedLength |
| character varying | string, char[], char, byte[]3 | Varchar | ||
| character | string, char[], char, byte[]3 | Char | ||
| citext | string, char[], char, byte[]3 | Citext | ||
| json | string, char[], char, byte[]3 | Json | ||
| jsonb | string, char[], char, byte[]3 | Jsonb | ||
| xml | string, char[], char, byte[]3 | Xml | ||
| uuid | Guid | Uuid | ||
| bytea | byte[] | ArraySegment<byte>, Stream (7.0+) | Bytea | Binary |
| timestamp with time zone | DateTime (Utc)1, DateTimeOffset | TimestampTz | DateTime2, DateTimeOffset | |
| timestamp without time zone | DateTime (Local/Unspecified)1 | Timestamp | DateTime2 | |
| date | DateOnly (6.0+) | DateTime | Date | Date |
| time without time zone | TimeOnly (6.0+) | TimeSpan | Time | Time |
| time with time zone | DateTimeOffset | TimeTz | ||
| interval | TimeSpan | NpgsqlInterval | Interval | |
| cidr | ValueTuple<IPAddress, int>, IPAddress | Cidr | ||
| inet | IPAddress | ValueTuple<IPAddress, int> | Inet | |
| macaddr | PhysicalAddress | MacAddr | ||
| tsquery | NpgsqlTsQuery | TsQuery | ||
| tsvector | NpgsqlTsVector | TsVector | ||
| bit | bool, BitArray, string | Bit | ||
| bit varying | BitArray | bool, BitArray, string | Varbit | |
| point | NpgsqlPoint | Point | ||
| lseg | NpgsqlLSeg | LSeg | ||
| path | NpgsqlPath | Path | ||
| polygon | NpgsqlPolygon | Polygon | ||
| line | NpgsqlLine | Line | ||
| circle | NpgsqlCircle | Circle | ||
| box | NpgsqlBox | Box | ||
| hstore | IDictionary<string, string> | Hstore | ||
| oid | uint | Oid | ||
| xid | uint | Xid | ||
| cid | uint | Cid | ||
| oidvector | uint[] | Oidvector | ||
| name | string, char[], char | Name | ||
| (internal) char | byte | InternalChar | ||
| composite types | Pre-mapped type | Composite | ||
| range types | NpgsqlRange<TSubtype> | Range | NpgsqlDbType | ||
| enum types | Pre-mapped type | Enum | ||
| array types | T[], List<T> | Array | NpgsqlDbType |
1 UTC DateTime is written as timestamp with time zone, Local/Unspecified DateTimes are written as timestamp without time zone. In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DateTime is always written as timestamp without time zone.
2In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), DbType.DateTime is mapped to timestamp without time zone.
3Types which can be read as byte[] can also be written as ReadOnlyMemory<byte> and Stream.
Notes when using Range and Array, bitwise-or NpgsqlDbType.Range or NpgsqlDbType.Array with the child type. For example, to construct the NpgsqlDbType for a int4range, write NpgsqlDbType.Range | NpgsqlDbType.Integer. To construct the NpgsqlDbType for an int[], write NpgsqlDbType.Array | NpgsqlDbType.Integer.
For information about enums, see the Enums and Composites page.