1-- 2-- MONEY 3-- 4-- Note that we assume lc_monetary has been set to C. 5-- 6 7CREATE TABLE money_data (m money); 8 9INSERT INTO money_data VALUES ('123'); 10SELECT * FROM money_data; 11SELECT m + '123' FROM money_data; 12SELECT m + '123.45' FROM money_data; 13SELECT m - '123.45' FROM money_data; 14SELECT m / '2'::money FROM money_data; 15SELECT m * 2 FROM money_data; 16SELECT 2 * m FROM money_data; 17SELECT m / 2 FROM money_data; 18SELECT m * 2::int2 FROM money_data; 19SELECT 2::int2 * m FROM money_data; 20SELECT m / 2::int2 FROM money_data; 21SELECT m * 2::int8 FROM money_data; 22SELECT 2::int8 * m FROM money_data; 23SELECT m / 2::int8 FROM money_data; 24SELECT m * 2::float8 FROM money_data; 25SELECT 2::float8 * m FROM money_data; 26SELECT m / 2::float8 FROM money_data; 27SELECT m * 2::float4 FROM money_data; 28SELECT 2::float4 * m FROM money_data; 29SELECT m / 2::float4 FROM money_data; 30 31-- All true 32SELECT m = '$123.00' FROM money_data; 33SELECT m != '$124.00' FROM money_data; 34SELECT m <= '$123.00' FROM money_data; 35SELECT m >= '$123.00' FROM money_data; 36SELECT m < '$124.00' FROM money_data; 37SELECT m > '$122.00' FROM money_data; 38 39-- All false 40SELECT m = '$123.01' FROM money_data; 41SELECT m != '$123.00' FROM money_data; 42SELECT m <= '$122.99' FROM money_data; 43SELECT m >= '$123.01' FROM money_data; 44SELECT m > '$124.00' FROM money_data; 45SELECT m < '$122.00' FROM money_data; 46 47SELECT cashlarger(m, '$124.00') FROM money_data; 48SELECT cashsmaller(m, '$124.00') FROM money_data; 49SELECT cash_words(m) FROM money_data; 50SELECT cash_words(m + '1.23') FROM money_data; 51 52DELETE FROM money_data; 53INSERT INTO money_data VALUES ('$123.45'); 54SELECT * FROM money_data; 55 56DELETE FROM money_data; 57INSERT INTO money_data VALUES ('$123.451'); 58SELECT * FROM money_data; 59 60DELETE FROM money_data; 61INSERT INTO money_data VALUES ('$123.454'); 62SELECT * FROM money_data; 63 64DELETE FROM money_data; 65INSERT INTO money_data VALUES ('$123.455'); 66SELECT * FROM money_data; 67 68DELETE FROM money_data; 69INSERT INTO money_data VALUES ('$123.456'); 70SELECT * FROM money_data; 71 72DELETE FROM money_data; 73INSERT INTO money_data VALUES ('$123.459'); 74SELECT * FROM money_data; 75 76-- input checks 77SELECT '1234567890'::money; 78SELECT '12345678901234567'::money; 79SELECT '123456789012345678'::money; 80SELECT '9223372036854775807'::money; 81SELECT '-12345'::money; 82SELECT '-1234567890'::money; 83SELECT '-12345678901234567'::money; 84SELECT '-123456789012345678'::money; 85SELECT '-9223372036854775808'::money; 86 87-- special characters 88SELECT '(1)'::money; 89SELECT '($123,456.78)'::money; 90 91-- documented minimums and maximums 92SELECT '-92233720368547758.08'::money; 93SELECT '92233720368547758.07'::money; 94 95SELECT '-92233720368547758.09'::money; 96SELECT '92233720368547758.08'::money; 97 98-- rounding 99SELECT '-92233720368547758.085'::money; 100SELECT '92233720368547758.075'::money; 101 102-- rounding vs. truncation in division 103SELECT '878.08'::money / 11::float8; 104SELECT '878.08'::money / 11::float4; 105SELECT '878.08'::money / 11::bigint; 106SELECT '878.08'::money / 11::int; 107SELECT '878.08'::money / 11::smallint; 108 109-- check for precision loss in division 110SELECT '90000000000000099.00'::money / 10::bigint; 111SELECT '90000000000000099.00'::money / 10::int; 112SELECT '90000000000000099.00'::money / 10::smallint; 113 114-- Cast int4/int8/numeric to money 115SELECT 1234567890::money; 116SELECT 12345678901234567::money; 117SELECT (-12345)::money; 118SELECT (-1234567890)::money; 119SELECT (-12345678901234567)::money; 120SELECT 1234567890::int4::money; 121SELECT 12345678901234567::int8::money; 122SELECT 12345678901234567::numeric::money; 123SELECT (-1234567890)::int4::money; 124SELECT (-12345678901234567)::int8::money; 125SELECT (-12345678901234567)::numeric::money; 126 127-- Cast from money to numeric 128SELECT '12345678901234567'::money::numeric; 129SELECT '-12345678901234567'::money::numeric; 130SELECT '92233720368547758.07'::money::numeric; 131SELECT '-92233720368547758.08'::money::numeric; 132