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>(). - Provider-specific types are returned by
NpgsqlDataReader.GetProviderSpecificValue().
| 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[] |
| character varying | string | char[] |
| character | string | char[] |
| citext | string | char[] |
| json | string | char[] |
| jsonb | string | char[] |
| xml | string | char[] |
| uuid | Guid | |
| bytea | byte[] | |
| timestamp without time zone | DateTime (Unspecified) | |
| timestamp with time zone | DateTime (Utc1) | 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 | (IPAddress, int) | NpgsqlInet |
| inet | IPAddress | NpgsqlInet, (IPAddress, int) |
| 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) |
1 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. See the breaking change note for more info.
2 In versions prior to 6.0 (or when Npgsql.EnableLegacyTimestampBehavior is enabled), reading a timestamp with time zone as a DateTimeOffset returns a local offset based on the timezone of the server where Npgsql is running.
3 PostgreSQL intervals with month or year components cannot be read as TimeSpan. Consider using NodaTime's Period type, or NpgsqlInterval.
The Default .NET type column specifies the data type NpgsqlDataReader.GetValue() will return.
NpgsqlDataReader.GetProviderSpecificValue will return a value of a data type specified in the Provider-specific type column, or the Default .NET type if there is no specialization.
Finally, the third column specifies other CLR types which Npgsql supports for the PostgreSQL data type. These can be retrieved by calling NpgsqlDataReader.GetBoolean(), GetByte(), GetDouble() etc. or via GetFieldValue<T>().
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 | Text | String, StringFixedLength, AnsiString, AnsiStringFixedLength | |
| character varying | string, char[], char | Varchar | ||
| character | string, char[], char | Char | ||
| citext | string, char[], char | Citext | ||
| json | string, char[], char | Json | ||
| jsonb | string, char[], char | Jsonb | ||
| xml | string, char[], char | Xml | ||
| uuid | Guid | Uuid | ||
| bytea | byte[] | ArraySegment<byte> | 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.
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.