Re: Is there a reason why Postgres doesn't have Byte or tinyint?

On Tue, Jan 10, 2012 at 00:36, 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.

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