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

Postgres does NOT support unsigned values.

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

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.

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.

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.

Why?

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.

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

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.