Thread: How can I get a column INT4 to be UNSIGNED ?
Hello, I would like to get a column of INT4, but instead having a range of allowed values from -2147483648 to 2147483647, i would to get an UNSIGNED range from 0 to 4294967295... So, is it possible to create a column with an UNSIGNED INT4 type ? If the answer is yes : what is the right syntax because I don't find the right syntax to do that... :-/ Thanks a lot in advance for your help ! :-) ------------------------------------------------ Bruno BAGUETTE - bruno.baguette@netcourrier.com
"Bruno Baguette" <bruno.baguette@netcourrier.com> writes:
> So, is it possible to create a column with an UNSIGNED INT4 type ?
No.
You could use OID, which just happens to be 4 bytes unsigned. But a
cleaner approach IMHO would be to use int8 and put a CHECK constraint
on it to limit the range of values.
regards, tom lane
On Fri, Apr 11, 2003 at 10:05:49AM -0400, Tom Lane wrote: > "Bruno Baguette" <bruno.baguette@netcourrier.com> writes: > > So, is it possible to create a column with an UNSIGNED INT4 type ? > > No. Is there a reason for not supporting unsigned types? -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "Before you were born your parents weren't as boring as they are now. They got that way paying your bills, cleaning up your room and listening to you tell them how idealistic you are." -- Charles J. Sykes' advice to teenagers
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Is there a reason for not supporting unsigned types?
Other than "it's not in any SQL standard", you mean?
Right now I'd resist adding such types because the numeric type
resolution rules are already a hairy mess. If we ever get those
straightened out to the point where unadorned constants are reliably
interpreted "the right way", we could take another look to see if
unsigned types could be added without plunging everything back into
chaos. I wouldn't hold my breath for it though.
regards, tom lane
> Is there a reason for not supporting unsigned types? Maybe I'm off base here, but couldn't you put in a constraint to achieve the same effect? In other words require inserted data in that column to be > -1 ? -Kyle
On Fri, 11 Apr 2003, Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > > Is there a reason for not supporting unsigned types? > > Other than "it's not in any SQL standard", you mean? > > Right now I'd resist adding such types because the numeric type > resolution rules are already a hairy mess. If we ever get those > straightened out to the point where unadorned constants are reliably > interpreted "the right way", we could take another look to see if > unsigned types could be added without plunging everything back into > chaos. I wouldn't hold my breath for it though. Actually, I think unsigned ints are mentioned all through the sql 92 docs, but only as an underlying subtype, never as its own, isn't it?
If he wants the full range of an INT4 unsigned, he needs to go to INT8 with the constraint you mentioned. The underlying col type of INT4 only supports HALF of the range of an INT4 unsigned, in the positive domain, that is. Kyle wrote: > > Is there a reason for not supporting unsigned types? > > Maybe I'm off base here, but couldn't you put in a constraint to achieve > the same effect? In other words require inserted data in that column to > be > -1 ? > > -Kyle > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Fri, 2003-04-11 at 16:09, Dennis Gearon wrote: > If he wants the full range of an INT4 unsigned, he needs to go to INT8 with the > constraint you mentioned. The underlying col type of INT4 only supports HALF of > the range of an INT4 unsigned, in the positive domain, that is. When domains allow CHECK constraints, that would be simplified, wouldn't it? -- +----------------------------------------------------------------+ | Ron Johnson, Jr. mailto:ron.l.johnson@cox.net | | Jefferson, LA USA http://members.cox.net/ron.l.johnson | | | | "A C program is like a fast dance on a newly waxed dance floor | | by people carrying razors." | | Waldi Ravens | +----------------------------------------------------------------+
Ron Johnson wrote:
> On Fri, 2003-04-11 at 16:09, Dennis Gearon wrote:
> > If he wants the full range of an INT4 unsigned, he needs to go to INT8 with the
> > constraint you mentioned. The underlying col type of INT4 only supports HALF of
> > the range of an INT4 unsigned, in the positive domain, that is.
>
> When domains allow CHECK constraints, that would be simplified, wouldn't
> it?
I thought we did support it:
test=> CREATE DOMAIN unsigned AS int4 CHECK ( VALUE > 0);
CREATE DOMAIN
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Ron Johnson wrote:
>> When domains allow CHECK constraints, that would be simplified, wouldn't
>> it?
> I thought we did support it:
> test=> CREATE DOMAIN unsigned AS int4 CHECK ( VALUE > 0);
> CREATE DOMAIN
It's new for 7.4, though.
regards, tom lane