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

>>>>>>>>>>> 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