Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    104

    Unanswered: can DECIMAL columns be unsigned

    Can DECIMAL columns be unsigned?
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not in ANSI SQL

    were you thinking of perhaps a specific database system?
    Code:
    CREATE TABLE test_unsigned_decimal
    ( foo DECIMAL(9,3) UNSIGNED
    );
    INSERT INTO test_unsigned_decimal VALUES  
     ( 9.37 ) , ( +1.23 ), ( -4.56 )
    ;
    SELECT * 
      FROM test_unsigned_decimal
    ;
    the above works quite nicely in mysql

    before testing it for yourself, see if you can guess what the stored values will be

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Check constraints can prevent signed values.
    Triggers can be used to remove any sign from the value.
    You can store signed values and display them as unsigned with a simple query.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2009
    Posts
    104

    guess

    Well, I'll guess that they'll all be positive. But, I suspect a trick. Maybe the value with the + sign will be negative?

    I can see where unsigned decimals would be problematic. I don't plan to use them but noticed no one (in the books I have) had explicitly said they did or didn't function.

    Now I'll test it.
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    textbooks that tried to cover all the stuff you can't do would be quite thick

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2009
    Posts
    104

    thick like my head

    I got my own little error message. In all seriousness, thanks for the patience and the help. And... I'll probably need more help before long. But, for now, I'm rollin'.
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    Well, I'll guess that they'll all be positive. But, I suspect a trick. Maybe the value with the + sign will be negative?
    no they won't, not really a trick, and no it won't, respectively

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2009
    Posts
    104
    Yes, the error message told me I can't insert the negative value. So, that's good to know, kind of. It means I have to go back and insert "unsigned" column constraints on a bunch of columns if I want to be fussy... which I do.
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rbfree
    Yes, the error message told me I can't insert the negative value.
    srsly?

    not when i tested it (see above) --
    Code:
    foo
    9.37
    1.23
    0
    i'm on 4.1.20, what version gave you the error message?
    Attached Thumbnails Attached Thumbnails srsly.gif  
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2009
    Posts
    104

    yep

    5.1.32

    I really do like the image of the ... ostrich? Poignant. (Will you send me a jpg?)

    I've attached a screenshot.

    I supposed that means that we can enforce unsigned values in mysql (current).
    Attached Thumbnails Attached Thumbnails screenshot.jpg  
    In theory, there is no difference between theory and practice. In practice there is.
    Disputed Origins

    Ninety-three percent of all statistics are made-up on the spot.
    Ancient proverb.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •