data types mappings

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Vojtech Babic
Posts: 2
Joined: Fri 25 Sep 2009 12:12

data types mappings

Post by Vojtech Babic » Fri 25 Sep 2009 12:19

I would like to know if there is mappings summary table.

For ex.
pgSql .net
------------------
bool Boolean

thanks...

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 28 Sep 2009 16:20

PostgreSQL to .NET types mapping table:
[PostgreSQL:BigInt] --> [.NET:long]
[PostgreSQL:Bit, VarBit] --> [.NET:BitArray]
[PostgreSQL:Uuid] --> [.NET:Guid]
[PostgreSQL:Char, VarChar, Text, Cldr, Inet, MacAddr] --> [.NET:String]
[PostgreSQL:Boolean] --> [.NET:bool]
[PostgreSQL:ByteA] --> [.NET:byte[]]
[PostgreSQL:Oid] --> [.NET:int]
[PostgreSQL:Int2Vector] --> [.NET:Int16[]]
[PostgreSQL:OidVector, OidVector801, oidVector] --> [.NET:int[]]
[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]
[PostgreSQL:Interval, Interval12] --> [.NET:TimeSpan]
[PostgreSQL:Real(MONO), Money, Double] --> [.NET:double]
[PostgreSQL:Int] --> [.NET:int]
[PostgreSQL:Time, TimeTZ] --> [.NET:TimeSpan]
[PostgreSQL:Real(not MONO)] --> [.NET:float]
[PostgreSQL:Numeric] --> [.NET:Decimal]
[PostgreSQL:SmallInt] --> [.NET:short]
[PostgreSQL:Row] --> [.NET:object]

.NET to PostgreSQL types mapping table:
[.NET:String] --> [PostgreSQL:VarChar]
[.NET:Int64, UInt32] --> [PostgreSQL:BigInt]
[.NET:Boolean] --> [PostgreSQL:Boolean]
[.NET:Double] --> [PostgreSQL:Double]
[.NET:Int32, UInt16] --> [PostgreSQL:Int]
[.NET:DateTime] --> [PostgreSQL:TimeStamp]
[.NET:Single] --> [PostgreSQL:Real]
[.NET:Decimal, UInt64] --> [PostgreSQL:Numeric]
[.NET:Int16, Byte, SByte] --> [PostgreSQL:SmallInt]
[.NET:byte[], PgSqlBlob, char[]] --> [PostgreSQL:ByteA]
[.NET:TimeSpan] --> [PostgreSQL:TimeTZ]
[.NET:PgSqlBox] --> [PostgreSQL:Box]
[.NET:PgSqlCircle] --> [PostgreSQL:Circle]
[.NET:PgSQlLSeg] --> [PostgreSQL:LSeg]
[.NET:PgSqlCursor] --> [PostgreSQL:VarChar]
[.NET:PgSqlPath] --> [PostgreSQL:Path]
[.NET:PgSqlPoint] --> [PostgreSQL:Point]
[.NET:PgSqlPolygon] --> [PostgreSQL:Polygon]
[.NET:PgSqlInterval] --> [PostgreSQL:Interval]
[.NET:PgSqlTimeStamp] --> [PostgreSQL:TimeStamp]
[.NET:PgSqlText] --> [PostgreSQL:VarChar]
[.NET:BitArray] --> [PostgreSQL:VarBit]
[.NET:Guid] --> [PostgreSQL:Uuid]
[.NET:PgSqlRow] --> [PostgreSQL:Row]
[.NET:PgSqlLargeObject] --> [PostgreSQL:LargeObject]

Vojtech Babic
Posts: 2
Joined: Fri 25 Sep 2009 12:12

Post by Vojtech Babic » Wed 30 Sep 2009 11:22

Great :D
Thanks

fredbest
Posts: 4
Joined: Thu 15 Apr 2010 02:19

Post by fredbest » Wed 21 Apr 2010 01:45

I have a Date type in Postgresql function parameter.
how do I call the function from .net ???
DateTime ???


[quote="Shalex"][b]PostgreSQL to .NET types mapping table[/b]:
[PostgreSQL:BigInt] --> [.NET:long]
[PostgreSQL:Bit, VarBit] --> [.NET:BitArray]
[PostgreSQL:Uuid] --> [.NET:Guid]
[PostgreSQL:Char, VarChar, Text, Cldr, Inet, MacAddr] --> [.NET:String]
[PostgreSQL:Boolean] --> [.NET:bool]
[PostgreSQL:ByteA] --> [.NET:byte[]]
[PostgreSQL:Oid] --> [.NET:int]
[PostgreSQL:Int2Vector] --> [.NET:Int16[]]
[PostgreSQL:OidVector, OidVector801, oidVector] --> [.NET:int[]]
[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]
[PostgreSQL:Interval, Interval12] --> [.NET:TimeSpan]
[PostgreSQL:Real(MONO), Money, Double] --> [.NET:double]
[PostgreSQL:Int] --> [.NET:int]
[PostgreSQL:Time, TimeTZ] --> [.NET:TimeSpan]
[PostgreSQL:Real(not MONO)] --> [.NET:float]
[PostgreSQL:Numeric] --> [.NET:Decimal]
[PostgreSQL:SmallInt] --> [.NET:short]
[PostgreSQL:Row] --> [.NET:object]

[b].NET to PostgreSQL types mapping table[/b]:
[.NET:String] --> [PostgreSQL:VarChar]
[.NET:Int64, UInt32] --> [PostgreSQL:BigInt]
[.NET:Boolean] --> [PostgreSQL:Boolean]
[.NET:Double] --> [PostgreSQL:Double]
[.NET:Int32, UInt16] --> [PostgreSQL:Int]
[.NET:DateTime] --> [PostgreSQL:TimeStamp]
[.NET:Single] --> [PostgreSQL:Real]
[.NET:Decimal, UInt64] --> [PostgreSQL:Numeric]
[.NET:Int16, Byte, SByte] --> [PostgreSQL:SmallInt]
[.NET:byte[], PgSqlBlob, char[]] --> [PostgreSQL:ByteA]
[.NET:TimeSpan] --> [PostgreSQL:TimeTZ]
[.NET:PgSqlBox] --> [PostgreSQL:Box]
[.NET:PgSqlCircle] --> [PostgreSQL:Circle]
[.NET:PgSQlLSeg] --> [PostgreSQL:LSeg]
[.NET:PgSqlCursor] --> [PostgreSQL:VarChar]
[.NET:PgSqlPath] --> [PostgreSQL:Path]
[.NET:PgSqlPoint] --> [PostgreSQL:Point]
[.NET:PgSqlPolygon] --> [PostgreSQL:Polygon]
[.NET:PgSqlInterval] --> [PostgreSQL:Interval]
[.NET:PgSqlTimeStamp] --> [PostgreSQL:TimeStamp]
[.NET:PgSqlText] --> [PostgreSQL:VarChar]
[.NET:BitArray] --> [PostgreSQL:VarBit]
[.NET:Guid] --> [PostgreSQL:Uuid]
[.NET:PgSqlRow] --> [PostgreSQL:Row]
[.NET:PgSqlLargeObject] --> [PostgreSQL:LargeObject][/quote]

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 21 Apr 2010 12:37

Yes, you should use the DateTime .NET type:
[PostgreSQL:Date, TimeStamp, TimeStampTZ] --> [.NET:DateTime]

bozr
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Post by bozr » Mon 21 Jun 2010 13:05

Dear devart developers,

I get a type mapping problem when using smallint in a function argument.
My function is declared as follows:

Code: Select all

CREATE OR REPLACE FUNCTION sp_plt_dummy("@StatusId" smallint DEFAULT 0)
  RETURNS void AS $$
BEGIN
END;
$$ LANGUAGE 'plpgsql'
My application code looks like this:

Code: Select all

DbProviderFactory factory = DbProviderFactories.GetFactory("Devart.Data.PostgreSql");
DbConnection connection = ProviderFactory.CreateConnection();
connection.ConnectionString = "Host=localhost;Database=G3;User Id=me;Password=secret;Protocol=2;";
connection.Open();

...

DbCommand dummy = connection.CreateCommand();
dummy.CommandText = "sp_PLT_Dummy";
dummy.CommandType = CommandType.StoredProcedure;
DbParameter parameter = dummy.CreateParameter();
parameter.ParameterName = "@StatusId";
parameter.DbType = DbType.Int16;
dummy.Parameters.Add(parameter);
dummy.Prepare();

...

dummy.Parameters["@StatusId"].Value = 1;
dummy.ExecuteNonQuery();
This generates a PgSqlException with the message "function sp_plt_dummy(integer) does not exist at character 15" and stack trace:

Code: Select all

   at Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
   at Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
   at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at DbTest.PltTest.ProcessLot() in C:\Repositories\G3_DbTest\DbTest\Tests\PltTest.cs:line 300
It seems that DbType.Int16 cannot be mapped to a smallint function argument?
Any help is greatly appreciated.

P.S. I currently use the latest free edition (4.95.140.0) and EnterprizeDB's postgresql 8.4.4

[edit] If I change the postgresql function's parameter type to "int" it works flawlessly.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 22 Jun 2010 13:56

Thank you for the bug report. We have reproduced it. I will notify you when the issue with the smallint function parameters is solved.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 23 Jun 2010 15:34

We have fixed the problem. Look forward to the next build of dotConnect for PostgreSQL. I will post here when it is available for download. This issue is associated with protocol 2.0. As a workaround, you can change the "Protocol=2;" entry of your connection string to "Protocol=3;" or put "select sp_PLT_Dummy(1::smallint)" to your PgSqlCommand.CommandText.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Thu 29 Jul 2010 13:17

We've released the 4.95.152 build with the fix. The new build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

For more information on improvements and fixes available in the 4.95.152 version, please refer to
http://www.devart.com/forums/viewtopic.php?t=18591

bozr
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Post by bozr » Thu 29 Jul 2010 14:58

Thanks a bunch!

The smallint issue is indeed fixed now. However I run into another one :-)
My function definition:

Code: Select all

CREATE OR REPLACE FUNCTION sp_plt_dummy2("@Timestamp" timestamp)
  RETURNS void AS $$
BEGIN
END;
$$ LANGUAGE 'plpgsql' 
The C# code:

Code: Select all

DbProviderFactory factory = DbProviderFactories.GetFactory("Devart.Data.PostgreSql");
DbConnection connection = ProviderFactory.CreateConnection();
connection.ConnectionString = "Host=localhost;Database=G3;User Id=me;Password=secret;Protocol=2;Unicode=true";
connection.Open();

...

DbCommand dummy2 = connection.CreateCommand();
dummy2.CommandText = "sp_plt_dummy2";
dummy2.CommandType = CommandType.StoredProcedure;
DbParameter parameter = dummy2.CreateParameter();
parameter.ParameterName = "@Timestamp";
parameter.DbType = DbType.DateTime;
dummy2.Parameters.Add(parameter);
dummy2.Prepare();

...

dummy2.Parameters["@Timestamp"].Value = DateTime.Now;
dummy2.ExecuteNonQuery(); 
dotConnect complains that it cannot find the function "sp_plt_dummy2".
When I change the function parameter type of sp_plt_dummy2 to "timestamp with time zone" it works as expected.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 03 Aug 2010 11:25

Thank you for your report. We are investigating the issue. It seems like it persists only with protocol 2.0. As a workaround please use the Protocol=3; connection string parameter.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 04 Aug 2010 14:08

The problem is fixed. I will post here when the corresponding build is available for download.

mvccms-jon
Posts: 3
Joined: Sat 14 Apr 2012 15:21
Contact:

Post by mvccms-jon » Sat 14 Apr 2012 15:26

Is double really the right choice for money?

http://www.postgresql.org/docs/9.1/stat ... money.html

Values of the numeric, int, and bigint data types can be cast to money. Conversion from the real and double precision data types can be done by casting to numeric first, for example:

SELECT '12.34'::float8::numeric::money;
However, this is not recommended. Floating point numbers should not be used to handle money due to the potential for rounding errors.

I know MSSQL maps money to decimal and this seems to make more sense to me.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 18 Apr 2012 11:37

We will investigate the possibility to map Money (PostgreSQL) -> Decimal (.NET) and post here about the results.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 24 Apr 2012 09:40

The behaviour will be changed starting from the next public build of dotConnect for PostgreSQL: the Money PostgreSQL data type will be mapped to System.Decimal. We will post here when it is available for download.

Post Reply