#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0

    Help - How to define the UNSIGNED attribute


    Hi everybody,
    I am switching from mysql to postgresql 7.3 and I do not know how to define an unsigned int field .
    Any pointers will be much appreciated!
    Thanks
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Postgres does NOT support unsigned values.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0

    Thanks


    I appreciate your response rod k. Now that I know better I wonder if modifying the system catalogs ( pg_cast, pg_type ) or making use of user defined types will make any sense.
    I have some apps that I would like to port to postgres in order to take some burden of the applications' code needed to "grant" some data integrity to the db under mysql. I am going through the documentation and I am not sure I am getting anywhere. Should I give it a rest?
    Thanks again
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You could define new datatypes for unsigned integer data (AND you'd have to define the operators for them, too). I doubt it's necessary tho. The only place it would be, is if you needed values > 2^63 and where you didn't need values > 2^64 which isn't very likely.
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Rod K is right on the money, although I will offer a couple other suggestions:

    1. If you are wanting to use the 'unsigned' attribute for data constraint purposes, you can simply define a CHECK constraint for any field that prohibits entering negative values.

    2. If you are using PostgreSQL 7.3 or better, you can create a DOMAIN defining a named column with the above-mentioned CHECK constraint, so that domain can be used as an unsigned type anywhere in the database, without worrying about manually including the CHECK constraint everywhere.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0
    Thanks guys, I feel that I am getting closer, I am certainly getting the feeling of what can be done. I appreciate your inputs. I still need to play with those 8th,16th,32nd bits so I will keep over this some more and post back any comments I had.
  12. #7
  13. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    I still need to play with those 8th,16th,32nd bits
    Why?
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0
    Well, these are meaningfull bits for us. Must of the software we make deals with pos devices with older processors and bioses and apis designed for the state of the art at the time. So through time, we have developed code that deals with for example virtual decimal points in 16 or 32 bit data, or amounts represented by 2 32 bit ints, authentication code, etc. The missing unsigned attribute shakes the carpet under my feet since I forsee a deep review of code.
    I admit, I might be totally lost, since I have intalled Postgres last week and that's the time I have been in touch with it.

    The pointers you have made give me some grounds as to keep looking deeper. But still I think missing the unsigned attribute is somehow a "weakness" ( shoud be a better word ) since your int data is restricted to 31 bits and I understand there is a performance penalty in bigints - according to docs.
  16. #9
  17. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    I understand there is a performance penalty in bigints
    Depends on the OS you are using.

    I still don't see this as an issue. You are referring to legacy software that uses bit data, and are concerned that the DB does as well, yet you mention using MySQL integer types to store the data, which is also a translation from the original value.

    You could do a couple of things. Convert the bit values to/from their twos complement (thereby safely converting int to/from the binary value), or use a FLOAT or NUMERIC (if float doesn't provide the precision you need).
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0
    Well, it can be done of course, but somehow it does not feel right. I’ll have to take a star from the product J ( still a great feature packed dbm , no doubt ) .

    I think my main concern is the cost of switching - and probably more the cost of switching without solid knowledge of the db.

    Suppose you have timestamps packed into 32 bit int data and you play with those. You ask the dbm to do something with those timestamps and then you, as they are, take them into your frontend and, as they are, you pass them to some other place. Yes you can still do it but it will need some care here and there. I don´t know, but to me this lack of the unsigned attribute felt a little like when you read in some product’s doc, “well you really don’t need subqueries since you can do this and this,,you don’t really need transactions or foreign keys since you can do this and this and this…”

    Hey guys, really, I apprecite the time you gave to this issue. Thanks a lot.

IMN logo majestic logo threadwatch logo seochat tools logo