Thread: Porting MySQL data types to PostgreSQL

On Jul 24, 2007, at 12:47 , Gautam Sampathkumar wrote:

> Does this mean I'd have to essentially double the space occupied by
> most
> database columns e.g convert mysql integer to postgresql bigint?

Only if your unsigned 4-byte integers actually exceed 2,147,483,647.
I suppose you could also use views and rules to automatically offset
the values by the appropriate amount (e.g., subtract -2,147,483,648
on insert and update, add 2,147,483,648 on select), but that sounds
like more trouble than it'd be worth.

Michael Glaesemann
grzm seespotcode net

[Please reply to the list so that others may benefit from and
participate in the discussion, and please don't top post as it makes
the discussion more difficult to follow.]

On Jul 25, 2007, at 20:55 , Gautam Sampathkumar wrote:


>           Thank you for your answer. Do you know why a choice was
> made to
> not include support for unsigned in PostgreSQL?
>

If I'm reading my draft copy of the SQL:2003 standard, SQL only
specifies SMALLINT, INTEGER, and BIGINT integer types. There are no
unsigned integer types in the standard. While PostgreSQL does have
datatypes which extend the SQL standard, either no one has felt the
need for an unsigned data type or it has not been accepted by core.
You could check the mailing list archives for details if you wish.

Michael Glaesemann
grzm seespotcode net

On Tue, 2007-07-24 at 10:47 -0700, Gautam Sampathkumar wrote:
> Hi,
>    I am in the process of porting a MySQL database to PostgreSQL.
> I was wondering why PostgreSQL does not support unsigned data types?
>
> Does this mean I'd have to essentially double the space occupied by
> most database columns e.g convert mysql integer to postgresql bigint?
>

Most applications don't need an unsigned int, because either the
expected range is within 0-2 billion; or the expected range exceeds 0-4
billion.

Either way, you can use a CHECK (my_attribute >= 0) to enforce the
constraint.

You might want to just check the table to see if there are any values
that exceed 2 billion. If so, you might think about using bigint anyway,
because you don't want to overflow.

If you really do need an unsigned type, this is a good use of
postgresql's extensible type system. You can just create an unsigned
type for yourself.

Regards,
    Jeff Davis

On Jul 26, 2007, at 11:06 AM, Jeff Davis wrote:
> If you really do need an unsigned type, this is a good use of
> postgresql's extensible type system. You can just create an unsigned
> type for yourself.

If you do that please start a project on pgfoundry so others can
contribute and benefit. In fact, if you do start one let me know and
I'll try and help out.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

In article <B396176E-A0FC-43CF-8C67-38CB1AF6A520@decibel.org>,
Jim Nasby <decibel@decibel.org> wrote:
% On Jul 26, 2007, at 11:06 AM, Jeff Davis wrote:
% > If you really do need an unsigned type, this is a good use of
% > postgresql's extensible type system. You can just create an unsigned
% > type for yourself.
%
% If you do that please start a project on pgfoundry so others can
% contribute and benefit. In fact, if you do start one let me know and
% I'll try and help out.

One problem with this idea is the treatment of implicit casts between
numeric types in TypeCategory(). For implicit casts to work, the type's
OID has to be listed in that function (i.e., it has to be a built-in type).
--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com

ptjm@interlog.com (Patrick TJ McPhee) writes:
> One problem with this idea is the treatment of implicit casts between
> numeric types in TypeCategory(). For implicit casts to work, the type's
> OID has to be listed in that function (i.e., it has to be a built-in type).

That's not the case.  There probably are some things that won't work
nicely if TypeCategory() doesn't recognize the type as numeric category,
but to claim that implicit casts won't work at all is wrong.

            regards, tom lane

In article <19363.1185892343@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
% ptjm@interlog.com (Patrick TJ McPhee) writes:
% > One problem with this idea is the treatment of implicit casts between
% > numeric types in TypeCategory(). For implicit casts to work, the type's
% > OID has to be listed in that function (i.e., it has to be a built-in type).
%
% That's not the case.  There probably are some things that won't work
% nicely if TypeCategory() doesn't recognize the type as numeric category,
% but to claim that implicit casts won't work at all is wrong.

I didn't say they won't work at all, but I do say that they won't work
completely. I had to play around with it before I remembered where things
broke down. Suppose you have a type called unsigned, written in C, with an
implicit cast from int4 to unsigned. Then

 SELECT 23::unsigned
 UNION
 SELECT 0;

will work if unsigned has one of the numeric OIDs known to TypeCategory(),
but not if it was defined normally using CREATE TYPE.

You can characterise this as working, just not nicely, but it's still
a problem for anyone trying to implement unsigned, or any other kind of
numeric value, as a user-defined type.

--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com

On Wed, Aug 01, 2007 at 05:19:33AM -0000, Patrick TJ McPhee wrote:
> In article <19363.1185892343@sss.pgh.pa.us>,
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> % ptjm@interlog.com (Patrick TJ McPhee) writes:
> % > One problem with this idea is the treatment of implicit casts between
> % > numeric types in TypeCategory(). For implicit casts to work, the type's
> % > OID has to be listed in that function (i.e., it has to be a built-in type).
> %
> % That's not the case.  There probably are some things that won't work
> % nicely if TypeCategory() doesn't recognize the type as numeric category,
> % but to claim that implicit casts won't work at all is wrong.
>
> I didn't say they won't work at all, but I do say that they won't work
> completely. I had to play around with it before I remembered where things
> broke down. Suppose you have a type called unsigned, written in C, with an
> implicit cast from int4 to unsigned. Then
>
>  SELECT 23::unsigned
>  UNION
>  SELECT 0;
>
> will work if unsigned has one of the numeric OIDs known to TypeCategory(),
> but not if it was defined normally using CREATE TYPE.
>
> You can characterise this as working, just not nicely, but it's still
> a problem for anyone trying to implement unsigned, or any other kind of
> numeric value, as a user-defined type.

Be that as it may, I suspect that if someone puts forward a working set
of uint2/4/8 it'd be considered for inclusion.
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Decibel! <decibel@decibel.org> writes:
> Be that as it may, I suspect that if someone puts forward a working set
> of uint2/4/8 it'd be considered for inclusion.

The datatypes themselves are utterly trivial.  The hard part, if you
want them to be part of the numeric hierarchy, is figuring out what the
type promotion and implicit casting rules ought to be.  For example,
it's far from clear what the initially-assumed type of an integral
constant ought to be.  I experimented once with allowing small integer
constants to be typed as int2 rather than int4, and it was amazing how
much stuff fell over just from that --- see the archives from maybe five
years ago for details, but the problem was basically that the parser
started finding a lot of unexpected and unwanted coercion paths, leading
to either wrong results or "can't resolve ambiguous operator" errors.
Throwing various sizes of uint into the picture would make things a
whole lot worse, not least because the semantics would actually change
depending on which assumption you made.

C can sort of deal with this because it's got a fixed set of types and
a fixed set of operators, but even in C it's frighteningly easy to get
burned by the compiler assuming it should do signed comparison where you
intended unsigned or vice versa.  In an extensible system like Postgres
the potential for mayhem is a lot worse.

I don't say these things are insoluble, but I do say it's a whole lot
harder than most people who ask for unsigned types realize.

            regards, tom lane