postgresql 和.NET类型对照表
| PostgreSQL type | 默认的 .NET 类型 | 特定提供的类型 | 其他 .NET 类型 |
|---|---|---|---|
| bool | bool | ||
| int2 | short | byte, sbyte, int, long, float, double, decimal, string | |
| int4 | int | byte, short, long, float, double, decimal, string | |
| int8 | long | long, byte, short, int, float, double, decimal, string | |
| float4 | float | double | |
| float8 | double | ||
| numeric | decimal | byte, short, int, long, float, double, string | |
| money | decimal | ||
| text | string | char[] | |
| varchar | string | char[] | |
| bpchar | string | char[] | |
| citext | string | char[] | |
| json | string | char[] | |
| jsonb | string | char[] | |
| xml | string | char[] | |
| point | NpgsqlPoint | string | |
| lseg | NpgsqlLSeg | string | |
| path | NpgsqlPath | ||
| polygon | NpgsqlPolygon | ||
| line | NpgsqlLine | string | |
| circle | NpgsqlCircle | string | |
| box | NpgsqlBox | string | |
| bit(1) | bool | BitArray | |
| bit(n) | BitArray | ||
| varbit | BitArray | ||
| hstore | IDictionary<string, string=""> | string | |
| uuid | Guid | string | |
| cidr | NpgsqlInet | string | |
| inet | IPAddress | NpgsqlInet | string |
| macaddr | PhysicalAddress | string | |
| tsquery | NpgsqlTsQuery | ||
| tsvector | NpgsqlTsVector | ||
| date | DateTime | NpgsqlDate | |
| interval | TimeSpan | NpgsqlTimeSpan | |
| timestamp | DateTime | NpgsqlDateTime | |
| timestamptz | DateTime | NpgsqlDateTime | DateTimeOffset |
| time | TimeSpan | ||
| timetz | DateTimeOffset | DateTimeOffset, DateTime, TimeSpan | |
| bytea | byte[] | ||
| 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 subtypes | NpgsqlRange | ||
| enum types | TEnum | ||
| array types | Array (of child element type) |
向后端发送参数时的类型映射
有三个规则,确定PostgreSQL类型发送一个参数:
1，如果设置了参数的NpgsqlDbType,使用它。
2，如果设置了参数的DbType，就会使用它。
3，如果以上两个都没有设置，那么后端类型将从CLR值类型推断出来。
注意,DateTime和NpgsqlDateTime,属性决定是否使用时间戳或timestamptz。
注意,当NpgsqlDbType或DbType设置为一个原始类型(布尔值、数字和字符串),其他基本类型是接受因为他们都实现IConvertible接口,这就是Npgsql使用价值目标类型转换。
| NpgsqlDbType | DbType | PostgreSQL type | 接受的 .NET 数据类型 |
|---|---|---|---|
| Boolean | Boolean | bool | bool, IConvertible |
| Smallint | Int16 | int2 | short, IConvertible |
| Integer | Int32 | int4 | int, IConvertible |
| Bigint | Int64 | int8 | long, IConvertible |
| Real | Single | float4 | float, IConvertible |
| Double | Double | float8 | double, IConvertible |
| Numeric | Decimal, VarNumeric | numeric | decimal, IConvertible |
| Money | Currency | money | decimal, IConvertible |
| Text | String, StringFixedLength, AnsiString, AnsiStringFixedLength | text | string, char[], char, IConvertible |
| Varchar | varchar | string, char[], char, IConvertible | |
| Char | char | string, char[], char, IConvertible | |
| Citext | citext | string, char[], char, IConvertible | |
| Json | json | string, char[], char, IConvertible | |
| Jsonb | jsonb | string, char[], char, IConvertible | |
| Xml | xml | string, char[], char, IConvertible | |
| Point | point | NpgsqlPoint | |
| LSeg | lseg | NpgsqlLSeg | |
| Path | path | NpgsqlPath | |
| Polygon | polygon | NpgsqlPolygon | |
| Line | line | NpgsqlLine | |
| Circle | circle | NpgsqlCircle | |
| Box | box | NpgsqlBox | |
| Bit | bit | BitArray, bool, string | |
| Varbit | varbit | BitArray, bool, string | |
| Hstore | hstore | IDictionary<string, string=""> | |
| Uuid | uuid | Guid, string | |
| Cidr | cidr | IPAddress, NpgsqlInet | |
| Inet | inet | IPAddress, NpgsqlInet | |
| MacAddr | macaddr | PhysicalAddress | |
| TsQuery | tsquery | NpgsqlTsQuery | |
| TsVector | tsvector | NpgsqlTsVector | |
| Date | Date | date | DateTime, NpgsqlDate, IConvertible |
| Interval | interval | TimeSpan, NpgsqlTimeSpan, string | |
| Timestamp | DateTime, DateTime2 | timestamp | DateTime, DateTimeOffset, NpgsqlDateTime, IConvertible |
| TimestampTZ | DateTimeOffset | timestamptz | DateTime, DateTimeOffset, NpgsqlDateTime, IConvertible |
| Time | Time | time | TimeSpan, string |
| TimeTZ | timetz | DateTimeOffset, DateTime, TimeSpan | |
| Bytea | Binary | bytea | byte[], ArraySegment |
| Oid | oid | uint, IConvertible | |
| Xid | xid | uint, IConvertible | |
| Cid | cid | uint, IConvertible | |
| Oidvector | oidvector | uint[] | |
| Name | name | string, char[], char, IConvertible | |
| InternalChar | (internal) char | byte, IConvertible | |
| Geometry | geometry | PostgisGeometry | |
| Composite | composite types | T | |
| Range | (other NpgsqlDbType) | range types | NpgsqlRange | |
| Enum | enum types | TEnum | |
| Array | (other NpgsqlDbType) | array types | Array, IList, IList |
自从mysql被收购狗，postgresql这个关系型数据库已经成为大众IT行业认养的首选。
我自己被这个里面的部分类型在.NET映射时弄的晕头转向。现在在国外网站找到这个对照类型表，供大家参考。
-----------------------------------------禁止转载！！！---------------------------------------------------
大家可进群，和更多的小伙伴一起学习，点击加群：
