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

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

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.

Mike Christensen 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.

-- Darren Duncan

On Sun, Jan 8, 2012 at 12:35 AM, Darren Duncan <darren@darrenduncan.net> wrote:

Mike Christensen 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.

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

Mike

On Sun, Jan 8, 2012 at 06:54, 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&amp;content-type=text/x-cvsweb-markup

Check method PrepareDefaultTypesMap().

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

On Sun, Jan 8, 2012 at 10:12 AM, Francisco Figueiredo Jr.
<francisco@npgsql.org> wrote:

On Sun, Jan 8, 2012 at 06:54, 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&amp;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

On Sun, Jan 8, 2012 at 21:31, Mike Christensen <mike@kitchenpc.com> wrote:

On Sun, Jan 8, 2012 at 10:12 AM, Francisco Figueiredo Jr.
<francisco@npgsql.org> wrote:

On Sun, Jan 8, 2012 at 06:54, 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&amp;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.

--
Regards,

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

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&amp;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?

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&amp;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

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&amp;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

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&amp;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