Re: Is there a reason why Postgres doesn't have Byte or tinyint? - Mailing list pgsql-general

On Tue, Jan 10, 2012 at 03:49, Mike Christensen <mike@kitchenpc.com> wrote:
>>>>>>>>>>>> According to the manuals, Postgres has smallint (2 byte), integer (4
>>>>>>>>>>>> bytes) and bigint (8 bytes)..  I use a lot of structures with "bytes"
>>>>>>>>>>>> in my code and it's kinda annoying to cast DB output from Int16 to
>>>>>>>>>>>> Byte every time, especially since there's no explicit cast in .NET and
>>>>>>>>>>>> you have to use System.Convert().
>>>>>>>>>>>>
>>>>>>>>>>>> Is there a work-around, or do people just cast or use Int16 in their
>>>>>>>>>>>> data structures?  Just wondering..  I know on modern computers it
>>>>>>>>>>>> probably doesn't make any difference anyway..
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Is this just about programmer convenience or is it about space efficiency
>>>>>>>>>>> in
>>>>>>>>>>> the database?  BYTEA might help you.  Or try declaring a DOMAIN over
>>>>>>>>>>> SMALLINT that limits allowed values to the range of a byte. -- Darren
>>>>>>>>>>> Duncan
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> This is purely programmer convenience.
>>>>>>>>>>
>>>>>>>>>> Basically, I want Npgsql to marshal the value as a .NET Byte type, if
>>>>>>>>>> I can find a way to do that I'm happy.  Perhaps it's more of a Npgsql
>>>>>>>>>> question, though I'm curious as to why Postgres doesn't have an
>>>>>>>>>> intrinsic tinyint or byte type.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Maybe Postgres doesn't need a Byte type predefined because it gives you the
>>>>>>>>> means to define the type yourself, such as using DOMAIN.
>>>>>>>>>
>>>>>>>>> Generally speaking, I believe it is more important for a type system to
>>>>>>>>> provide the means for arbitrary user-defined types which can be used in all
>>>>>>>>> the places as built-in-defined types, than to have large numbers of
>>>>>>>>> built-in-defined types.
>>>>>>>>
>>>>>>>> Precisely.  postgresql's extensable nature allows you to build your
>>>>>>>> own types as well.  If it's popular enough it'll make it into contrib,
>>>>>>>> then maybe core.  My guess is that there's some non-trivial cost to
>>>>>>>> maintaining each core type, and since a byte type isn't required by
>>>>>>>> the SQL spec, it would take some effort to get a standard one included
>>>>>>>> in the core.
>>>>>>>
>>>>>>> That makes sense.
>>>>>>>
>>>>>>> I guess my question is more of a NpgSql question then.  Is there a way
>>>>>>> to create a custom PG type, and have npgsql serialize that type in a
>>>>>>> dataset to a .NET Byte type?
>>>>>>>
>>>>>>> I'd probably be better off posting on the npgsql mailing list, but
>>>>>>> perhaps someone here knows as well..
>>>>>>>
>>>>>>
>>>>>>
>>>>>> Hi!
>>>>>>
>>>>>> Npgsql already has support to map .Net byte data type to int2 and vice-versa.
>>>>>>
>>>>>> You can see all the supported type mappings in this file:
>>>>>>
http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/npgsql/Npgsql2/src/NpgsqlTypes/NpgsqlTypesHelper.cs?rev=1.41&content-type=text/x-cvsweb-markup
>>>>>>
>>>>>> Check method PrepareDefaultTypesMap().
>>>>>>
>>>>>> I hope it helps.
>>>>>
>>>>> Thanks!  I'll have to mess around with this a bit more..  From what
>>>>> I've seen so far, functions that have int2 out parameters will return
>>>>> Int16 through the DataReader..  Maybe I'm doing something wrong..
>>>>>
>>>>> Mike
>>>>
>>>>
>>>> I think you aren't doing anything wrong. int2 postgresql datatypes are
>>>> supposed to be mapped to .net int16 types.
>>>>
>>>> Do you have a simple example?
>>>>
>>>> What type were you expecting in the datareader? Maybe there is a
>>>> missing mapping in Npgsql.
>>>>
>>>> Thanks in advance.
>>>
>>> I'm pretty sure your code is working the way it's designed.  If I pass
>>> /in/ a .NET Byte type, it's compatible with an Int2 (16bit int).  It's
>>> basically "cast-up" to the smallest intrinsic type Postgres supports.
>>> However, data coming back out from PG is where I run into the issue.
>>> What I'm doing is calling a function that has the following signature:
>>>
>>> CREATE FUNCTION KPC_GetMealPlans(IN _category MealPlanCategoryEnum,
>>> OUT id uuid, OUT title character varying, OUT imageurl character
>>> varying, OUT rating smallint, OUT numratings integer, out crediturl
>>> character varying, OUT recipecount integer, out ingredientcount
>>> integer)
>>>  RETURNS SETOF record AS
>>>  $BODY$
>>>  BEGIN
>>>    -- All sorts of stuff way too mind-blowing for this email
>>>  END;
>>>
>>> Then I call it as so:
>>>
>>> IDbCommand cmd = session.Connection.CreateCommand();
>>> cmd.CommandType = CommandType.StoredProcedure;
>>> cmd.CommandText = "KPC_GetMealPlans";
>>> //...bind parameter
>>> return cmd.ExecuteReader();
>>>
>>> This will return an iDataReader, which I loop through.  That
>>> IDataReader will return an Int16 type for the Rating OUT parameter,
>>> which I'm pretty sure is by default.  However, I would like it to
>>> return a Byte, because I use a Byte for all my ratings (which are a
>>> number of stars for the recipe, between 0 and 5)..
>>>
>>> If I understand correctly, I can create a Postgres domain called
>>> "Rating" as well which would be even cooler.  However, how would I
>>> then tell Npgsql to marshal that back as either a Byte or even my own
>>> Rating .NET type?
>>
>>
>>
>>
>> Got it.
>>
>> Sorry if I rushed in my response. In fact Npgsql does the conversion
>> from .Net byte to int2 but not the other way around :(
>>
>> I have in my plans since a long time ago to implement custom type
>> conversions so users could solve problems like yours with custom types
>> on postgresql database and .net. But for while I didn't worked on
>> anything about that.
>>
>> I see 2 possible solutions for you right now:
>>
>> 1. Add your custom types directly in Npgsql code by adding custom
>> typeconverters and compile Npgsql.
>>
>> 2. Try to use a Bit datatype. Npgsql has a BitString datatype which
>> maps to postgresql bit fields which could give you the information you
>> want. But I don't know if it would be too much overkill for your
>> rating system.
>> BitString datatype already have a lot of helpers methods though
>> including one which translates the bitstring to a byte value which I
>> think would be helpful to you.
>
> Thanks so much for your help!
>
> I bet the BitString (this is basically a representation of a bitmask,
> I take it) would work perfectly for representing a 0-5 rating, plus
> maybe a bit at the end for representing the half star or something.
> However, I think it's a bit overkill at the moment when the
> alternative is just cast an Int16 to a Byte, or just use an Int16 to
> represent the rating in the first place.  Creating a customized
> version of the source equally so, though I'd love to dig into the
> source when I have some free time (oh wait, KitchenPC /is/ my free
> time)..
>
> I cannot say +1 enough to the idea of implementing custom type
> conversions though - Even if it's a quick and dirty solution.  I would
> definitely love the idea of Npgsql converting database ENUMs to my own
> C# matching enums, or DOMAINs to instances of my own C# classes (such
> as the Rating class).  I already do a little bit of this through
> NHibernate, and it works pretty well, but it only works through the
> ORM and not when I query directly.  If you ever have a free weekend of
> a long flight to get something working, I'll owe you a drink and be
> the first in line to thoroughly test it for you!
>
> Mike


Well, the idea of custom type conversions are exactly the Domain
support to your own c# classes :)

The idea would be possible to register custom types with Npgsql
alongside their converters. I don't know if there was a way to create
a generic converter, but being able to register custom converters is
already a big step in the direction of supporting postgresql custom
domain types.

Today Npgsql has support to send you enum value to the database. You
can see an example of that in the EnumSupport method of our test
suite. I reproduce it here for easy reading:

    [Test]
        public void EnumSupport()
        {
            NpgsqlCommand command = new NpgsqlCommand("insert into
tableb(field_int2) values (:a)", TheConnection);

            command.Parameters.Add(new NpgsqlParameter("a",
NpgsqlDbType.Smallint));

            command.Parameters[0].Value = EnumTest.Value1;


            Int32 rowsAdded = command.ExecuteNonQuery();

            Assert.AreEqual(1, rowsAdded);
        }


It is a simple support but it could help you in your task.

I think the other way around would need more work as Npgsql would need
to know how the value from database map to which enum value. But for
while, you could make the conversion manually in your client code to
your enum.

I hope it helps.


--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior